DataTables server query debugging

jimv

I'd like to see the exact query that the server is executing.
Is there anyway to get the query passed back in teh reply from the server?
If not, any suggestions on the best way to see what the query was?
(PHP/MySQL server side, html page front end)
Thanks
Jim

allan

Hi Jim,

What I tend to do is use the PHP logging functions that PHP provides (i.e. error_log() ) and monitor the error log for the server. Another option is to add the query string to the returned object, and then just view the XHR return using Firebug or something like that.

Regards,
Allan

jimv

Allan,
Using the example code you provide (Shown below, thanks for providing it!!), I tried adding a parmater to the sOutput object returned
$sOutput .= '"SQLQuery": TEST ';

But when ever I do that, it appears to break the returned object.
Am I misunderstanding?

Thanks
Jim



$sOutput = '{';
$sOutput .= '"sEcho": '.$_GET['sEcho'].', ';
$sOutput .= '"iTotalRecords": '.$iTotal.', ';
$sOutput .= '"iTotalDisplayRecords": '.$iFilteredTotal.', ';
$sOutput .= '"aaData": [ ';
while ( $aRow = mysql_fetch_array( $rResult ) )
{
$sOutput .= "[";
$sOutput .= '"'.addslashes($aRow['ZIP']).'",';
$sOutput .= '"'.addslashes($aRow['LAST']).'",';
$sOutput .= '"'.addslashes($aRow['FIRST']).", ".addslashes($aRow['MIDDLE']).'",';
$sOutput .= '"'.addslashes($aRow['CITY']).'",';
$sOutput .= '"'.addslashes($aRow['STATE']).'",';
$sOutput .= '"'.addslashes($aRow['COLLEGE']).'"';
$sOutput .= "],";
}
$sOutput .= '"SQLQuery": TEST ';
$sOutput = substr_replace( $sOutput, "", -1 );
$sOutput .= '] }';

stepf

you can output your query to a text file for reading

http://datatables.net/forums/comments.php?DiscussionID=675&page=1#Item_5

jimv

Stepf,
Thanks, the file output worked well for the query, got the info I needed!!

I am still curious how to add parameters to the returned object, might be usefull for other purposes.

Cheers,
Jim

allan

Hi Jim,

I think the problem is that the line:

$sOutput .= '"SQLQuery": TEST ';

is creating non valid JSON. You need to do something like this (note I've added it at the top since it's a touch easier):

[code]
$sOutput = '{';
$sOutput .= '"SQLQuery": "'.addslashes ($sQuery).'", ';
$sOutput .= '"sEcho": '.$_GET['sEcho'].', ';
$sOutput .= '"iTotalRecords": '.$iTotal.', ';
$sOutput .= '"iTotalDisplayRecords": '.$iFilteredTotal.', ';
$sOutput .= '"aaData": [ ';
while ( $aRow = mysql_fetch_array( $rResult ) )
{
$sOutput .= "[";
$sOutput .= '"'.addslashes($aRow['ZIP']).'",';
$sOutput .= '"'.addslashes($aRow['LAST']).'",';
$sOutput .= '"'.addslashes($aRow['FIRST']).", ".addslashes($aRow['MIDDLE']).'",';
$sOutput .= '"'.addslashes($aRow['CITY']).'",';
$sOutput .= '"'.addslashes($aRow['STATE']).'",';
$sOutput .= '"'.addslashes($aRow['COLLEGE']).'"';
$sOutput .= "],";
}
$sOutput = substr_replace( $sOutput, "", -1 );
$sOutput .= '] }';
[/code]
Regards,
Allan

jimv

Allan,
I had originally tried something like that, but whenever I add anything to the sOutput, then Datatables no longer parses the result and displays it in the table.
I used "dataTables-1.5/examples/data_sources/server_side.html" and "/dataTables-1.5/examples/examples_support/server_processing.php" as a test.
If I modify sOuput as you suggest above, the table in server_side.html is not populated with data.

For now, I've used the suggestion someone had to dump to a file, and figured out what was wrong with my sql query and so I'm all set for now. But it would be interesting to be able to add additonal data to the sOutput return.

Thanks for all the great work!
Jim

allan

Hi Jim,

Sorry about that, I've actually got a small mistake in my code above - I hadn't put the sQuery output in quotes, which is of course needed for a string in json...

[code]
$sOutput .= '"SQLQuery": "'.addslashes ($sQuery).'", ';
[/code]
I've corrected the code above as well now, to avoid future confusion.

Best way to debug why json might not be working is with http://www.jsonlint.com - which would show this error up right away.

Regards,
Allan