Using Mysqli instead of Mysql

rubenvisscher

Hello,

I want to use de server-side processing option from the Datatable. I've changed the php code to this :

[code]
<?php
/* MySQL connection */
include('config.php');
include('Database.php');

$db = new Database(DB_USER, DB_HOST, DB_PASSWORD, DB_NAME);

/* Paging */
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
/* $sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ".
mysql_real_escape_string( $_GET['iDisplayLength'] );*/
$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 ; $igetQuery($sQuery);
$aResultFilterTotal = $rResultFilterTotal->fetch_array();
$iFilteredTotal = $aResultFilterTotal[0];

$sQuery = "
SELECT COUNT(id)
FROM ajax
";
//$rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
$rResultTotal = $db->getQuery($sQuery);
//$aResultTotal = mysql_fetch_array($rResultTotal);
$aResultTotal = $rResultTotal->fetch_array();
$iTotal = $aResultTotal[0];

$sOutput = '{';
$sOutput .= '"sEcho": '.intval($_GET['sEcho']).', ';
$sOutput .= '"iTotalRecords": '.$iTotal.', ';
$sOutput .= '"iTotalDisplayRecords": '.$iFilteredTotal.', ';
$sOutput .= '"aaData": [ ';
//while ( $aRow = mysql_fetch_array( $rResult ) )
while ( $aRow = $rResult->fetch_array() )
{
$sOutput .= "[";
$sOutput .= '"'.addslashes($aRow['engine']).'",';
$sOutput .= '"'.addslashes($aRow['browser']).'",';
$sOutput .= '"'.addslashes($aRow['platform']).'",';
if ( $aRow['version'] == "0" )
$sOutput .= '"-",';
else
$sOutput .= '"'.addslashes($aRow['version']).'",';
$sOutput .= '"'.addslashes($aRow['grade']).'"';
$sOutput .= "],";
}
$sOutput = substr_replace( $sOutput, "", -1 );
$sOutput .= '] }';

echo $sOutput;


function fnColumnToField( $i )
{
if ( $i == 0 )
return "engine";
else if ( $i == 1 )
return "browser";
else if ( $i == 2 )
return "platform";
else if ( $i == 3 )
return "version";
else if ( $i == 4 )
return "grade";
}
?>
[/code]

If I load the page, "server_processing.php" , in the browser I get the right JSON Code but he don't load the data in the table on the page , "server_side.html" .
Does someone know what goes wrong ?

allan

What happens when you run the returned json through http://jsonlint.com ?

Allan

rubenvisscher

[code]
Valid JSON
[/code]

I have tried that but the JSON is valid

allan

Okay - that's good news at least. But it doesn't answer the question as to why it wouldn't be working. Can you post a link showing the problem? Or a sample of the JSON that is sent back?

Allan

archedimes

I know this is an old thread, but I have a similar problem. I want to use mysqli so I can use stored procedures, and it works great except... it doesn't work. Specifically, it throws the generic "DataTables warning: JSON data from server failed to load or be parsed. This is most likely to be caused by a JSON formatting error." The only hitch is that I know for a fact the data and JSON are fine. In fact, both the php page using the standard mysql method and the one using mysqli are ultimately just returning a string (see below), so the only difference is the connection method, not the data returned. I have tried stepping through using firebug and I've gotten as far as it is failing somewhere inside the function() call on the "error" line. thoughts?

just returns a string:
[code]
$sOutput = '{"sEcho": 1, "iTotalRecords": 3, "iTotalDisplayRecords": 3, "aaData": [ ["Contact","email"],["Get A Quote","email"],["Online Certificate","email"]] }';
echo $sOutput;
[/code]


declaration and call - maybe it is a settings/options issue?
[code]
$j('#quotetable').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "/wordpress/wp-content/themes/mytheme/get-quotes.php"
} );
[/code]


it fails inside the function() call
[code]this.fnServerData = function ( url, data, callback ) {
1254 $.ajax( {
1255 "url": url,
1256 "data": data,
1257 "success": callback,
1258 "dataType": "json",
1259 "cache": false,
1260 "error": function () {
1261 alert( "DataTables warning: JSON data from server failed to load or be parsed. "+
1262 "This is most likely to be caused by a JSON formatting error." );
1263 }
1264 } ); [/code]

archedimes

in case it wasn't clear, it makes it to the end of the php page, returns the string, and fails after that, during the second sort

archedimes

i got it to work, but still not sure why this works:
leave the mysql_pconnect alone and just add a mysqli connection in a new addition to the array, and just use the mysqli connection. So the result is as follows:

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

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

$gaSql['splink'] = new mysqli( $gaSql['server'], $gaSql['user'], $gaSql['password'], $gaSql['db'] ) or
die( 'Could not open connection to server' );
.
.
.
$res = $gaSql['splink']->multi_query($query);[/code]