Help! Mysql Improved

orchid1

Arg! can't get the connection function working properly with MySQLi

anybody modify the script to work with MySQL improved i.e. mysqli_connect()
I have the DataTable working on localHost but can't get it to work on remote server
remote server uses MySQL improved while localHost uses good ol' MySQL
anyIdeas

allan

Hi orchid1,

I'm not aware of anyone having ported my example backend script to MySQLi, but I can't imagine it would be that much different from regular MySQL. Are you able to log into the database with your user/pass (i.e. might it be a permissions problem)? If it's a general connection issue, rather than something specific to DataTables, perhaps it would be best to ask in a MySQLi forum.

Allan

orchid1

thanx for the feedback

I'm pretty sure the it's the php

I'm gonna try the php forums
but maybe you can find something I cannot
anyways here's the code

[code]
<?php
/* MySQL connection */
$gaSql['user'] = "USER";
$gaSql['password'] = "PASSWORD";
$gaSql['db'] = "DATABASE";
$gaSql['server'] = "SERVERNAME i.e. localhost";
$gaSql['type'] = "mysql";

$gaSql['link'] = mysqli_connect( $gaSql['server'], $gaSql['user'], $gaSql['password'], $gaSql['db']) or
die( 'Could not open connection to server' );

mysqli_select_db($gaSql['link'], $gaSql['db']) or
die( 'Could not select database '. $gaSql['db'] );

//error
if (!$gaSql['link']) {
echo "Cannot connect to database";
exit;
}

/* Paging */
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) )
{
$sLimit = "LIMIT ".mysqli_real_escape_string( $_GET['iDisplayStart'] ).", ".
mysqli_real_escape_string( $_GET['iDisplayLength'] );
}

/* Ordering */
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i
[/code]

allan

Looks fairly sensible to me. Does it exit on this line: echo "Cannot connect to database";? If so I would guess that it's a 'grants' permissions problem on the server.

Allan

orchid1

I'm not getting any response from the server i.e."Cannot connect to database".

So i'm not totally sure what is going on

dataTeble is set up so it will return "Cannot connect to database" if that is the output right??
I'm assuming that the XHRequestObject return function will print anything returned from the server???

I'm working on this and I appreciate the input form you.
Any other ideas?
I will post back when i figure out the bug and post the code. I have a feeling I'm not the only one that has had this problem.
thanx

flies

You forget about one parameter to:
mysqli_real_escape_string ( mysqli $link , string $escapestr )

you give only $escapestr and not $link... About ten tiems...

i.e.
if ( mysqli_real_escape_string( $_GET['sSearch'] ) != "" )


Advice:

add:
error_reporting(E_ALL);
on top on your php script...

flies

mysqli_error()
mysqli_error($gaSql['link'])

mysqli_real_escape_string( $_GET['sSearch'] )
mysqli_real_escape_string($gaSql['link'], $_GET['sSearch'] )

allan

@orchid1 - Okay if you are getting 'Cannot connect to database', then there is something wrong with either the server, the connection details you have given or the grant permissions for your user. I'm afraid we probably won't be able to help you much in this forum.

Regarding the return - actually no DataTables will not parse this information and display it because it is not valid json. I'd recommend using Firebug or similar to see what the return from the server is.

@flies - the $link is optional is it not? http://uk.php.net/manual/en/function.mysql-real-escape-string.php

Regards,
Allan

flies

not in mysqli
http://us.php.net/manual/en/mysqli.real-escape-string.php

And he said he is not getting response because he has error reporting disable (probably). Therefore we can't see this error:

Warning: mysqli_real_escape_string() expects exactly 2 parameters, 1 given in /var/www/sql.php on line 43

orchid1

it works !!
I want to cry I really do partly cuz i'm sad I lost a whole night with my son and wife trying to find the bug and partly because I can move on with my app

Thank you so very much and as promised here is the code for whomever comes after me
[code]

<?php
/* MySQL connection */
$gaSql['user'] = "USER";
$gaSql['password'] = "PASSWORD";
$gaSql['db'] = "DATABASE";
$gaSql['server'] = "SERVERNAME i.e. localhost";
$gaSql['type'] = "mysql";

$gaSql['link'] = mysqli_connect( $gaSql['server'], $gaSql['user'], $gaSql['password'], $gaSql['db']) or
die( 'Could not open connection to server' );

mysqli_select_db($gaSql['link'], $gaSql['db']) or
die( 'Could not select database '. $gaSql['db'] );

//error
if (!$gaSql['link']) {
echo "Cannot connect to database";
exit;
}

/* Paging */
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) )
{
$sLimit = "LIMIT ".mysqli_real_escape_string($gaSql['link'], $_GET['iDisplayStart'] ).", ".
mysqli_real_escape_string($gaSql['link'], $_GET['iDisplayLength'] );
}

/* Ordering */
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i


[/code]

allan

Good to hear you got it sorted!

@flies: Nice catch!

Allan

flies

@allan: thx :) Btw. I got question... Any particular reason for creating manually json response (except that json_encode was introduced in php5)? I'm going to rewrite my scripts to server-side processing and that question just popped up...

allan

The only reason was that I figured it would be a touch faster than using json_encode, plus I think it's a little more flexible. Typically you would have the SQL array (which is associative in my example) which you want to output, and you would likely have to convert this into another PHP array with the formatting that is required. Then you encode that. So the method I took was to remove this middle step.

The other thing is that it was 5.2 which introduced json_encode, and that's not quite as widely adopted as perhaps we would like!

Regards,
Allan

flies

Yeah it might be faster...

But (to the second argument) you can make alterations in the sql statement (not all but most) and just get the selected columns in the desired order and formatting. But if you checked that it's faster I'm not gonna use it, cause processing power is most important variable in my case :)

allan

I didn't actually check it - I just suspect that this is the case. It might be that if you do as you say and tailor your SQL statement to exactly match what you want, and then add sEcho etc to that array, followed encode_json, then that might actually be a touch faster - it's probably fairly close either way. But that's the great thing about the server-side processing, you can do it any way you fancy :-)

Allan

Snorre

Having the rest of my classes written in mysqli I rewrote the server-side example script to mysqli to be able to push it up in the model. By all means, the rewriting is not a big deal. Basically just putting "i" after the mysql and switching the order of the connection. But being av beginner at this myself, I would love to have this posted before so I wouldn't have to spend time on this.

[code]
<?php
/*
* Script: DataTables server-side script for PHP and MySQL
* Copyright: 2010 - Allan Jardine
* License: GPL v2 or BSD (3-point)
*/

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/

/* Array of database columns which should be read and sent back to DataTables. Use a space where
* you want to insert a non-database field (for example a counter or static image)
*/
$aColumns = array( 'engine', 'browser', 'platform', 'version', 'grade' );

/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "id";

/* DB table to use */
$sTable = "ajax";

/* Database connection information */
$gaSql['user'] = "";
$gaSql['password'] = "";
$gaSql['db'] = "";
$gaSql['server'] = "localhost";


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/

/*
* MySQL connection
*/
$gaSql['link'] = mysqli_connect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) or
die( 'Could not open connection to server' );

mysqli_select_db( $gaSql['link'], $gaSql['db'] ) or
die( 'Could not select database '. $gaSql['db'] );


/*
* Paging
*/
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".mysqli_real_escape_string($gaSql['link'], $_GET['iDisplayStart'] ).", ".
mysqli_real_escape_string($gaSql['link'], $_GET['iDisplayLength'] );
}


/*
* Ordering
*/
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i $iFilteredTotal,
"aaData" => array()
);

while ( $aRow = mysqli_fetch_array( $rResult ) )
{
$row = array();
for ( $i=0 ; $i
[/code]

allan

Great stuff - thanks very much for posting that modified script. I'm sure it will prove to be very useful for other!

Regards,
Allan