Issue with Server-Side Data (json error depending on content of sSearch)

ArcNovus

Hello

i'm using datatables within a selfwritten crm with "large" amounts of data ( 30'000+ rows per table, 80+ tables ). Everything works very fine so far, but one small bug i noticed.

As i use the search filter ( Search: ) to filter the results, it works correctly for queries "j" and " jn2" but not for "jn" .. but its not about sSearch = 2, because e.G "li" is working or "jp" too .. also queries with umlauts are working e.G "

ArcNovus

To support: I could also provide a Teamviewer / VNC Session to show you the issue live ! it's important to get this bug fixed ..because so the customer is not able to find all newly created articles

ArcNovus

in meantime .. i changed ajax type to post

"fnServerData": function ( sSource, aoData, fnCallback ) {
$.ajax( {
"dataType": 'json',
"type": "POST",
"url": sSource,
"data": aoData,
"success": fnCallback
} );
}

and so in qry file $_GET replaced with $_POST .. did not change

ArcNovus

DB queries are actually working ..when i copy sQuery to phpMyAdmin, i actually get a result

ArcNovus

if helps ..check these screenshots

not working
http://78.46.124.50/not_working.png
http://78.46.124.50/not_working2.png

working
http://78.46.124.50/working.png

ArcNovus

changing the output (custom queries disabled) does not change behavior of error

while ( $aRow = mysql_fetch_array( $rResult ) )
{
$sOutput .= "[";
//$sOutput .= '"'.addslashes($aRow['article_nr']).'",';
$sOutput .= '"'.addslashes(''.$aRow['article_nr'].'').'",';
$sOutput .= '"'.addslashes($aRow['supplier_article_nr']).'",';
$sOutput .= '"'.addslashes($aRow['name']).'",';
//if($aRow['tag_brand'] != 0) {
// $tagData = $companyDB->getRowAssoc('tag_tags','`ID` = '.$aRow['tag_brand']);
// $sOutput .= '"'.addslashes($tagData['label']).'",';
//} else {
$sOutput .= '"-",';
//}
//$sOutput .= '"'.addslashes($aRow['tag_brand']).'",';
if($userHasEditPermission){
$sOutput .= '"'.addslashes('').'"';
}else{
$sOutput .= '"'.addslashes(' ').'"';
}
$sOutput .= "],";
}

allan

Hi ArcNovus,

Thanks for all the information - very useful.

> Error is "DataTables warning: JSON data from server could not be parsed. This is caused by a JSON formatting error.

This error message means more or less way it says - the return string isn't strictly a JSON object. If you grab the return from the server and pass it through http://jsonlint.com you'll be able to see where the parsing error is.

My guess is that you've got a newline character somewhere, which just so happens to match the 'jn' filter (if you were to page through the whole table you'd come across it as well). So somewhere in the DB there is a row which doesn't convert to nice JSON when using addslashes and a new line character is my guess...

If you have PHP 5.2+ on your server I'd suggest using json_encode() as this makes dealing with JSON much easier in PHP - like my example here: http://www.datatables.net/development/server-side/php_mysql .

Regards,
Allan

ArcNovus

Hi Allan

thanks for your answer. Your Tip with jsonlint.com helped me and brought me to the soliution.

Removed addslashes() function from sOutput lines. The error was a slashed \' ..now as ' its working.

Thanks a lot for your help, i'm honestly greatful for such good work as datatables, so much time
i can spend in other parts of my app =)

allan

Hi ArcNovus,

Good to hear you got it sorted - and my pleasure :-). Great to hear that DataTables is proving to be useful!

Regards,
Allan

tdmohr

Hello,

I have the same problem after upgrading my jQuery version to the latest version.

The \' was causing the datatable to crash. JsonLint also showed the problem with having a forward slash.

Unfortunately however removing addslashes has not resolved my issue. Even more confusing is that JsonLint says the json with a single quote ' is valid. However datatables is still crashing.

I tried upgrading to the latest version of datatables but still no luck.

I would greatly appreciate some other ideas?

Thanks,

Tim

tdmohr

PS: This post on StackOverFlow indicates that single quotes do not need to be escaped in a JSON output. Does this mean this could be a datatables issue?

http://stackoverflow.com/questions/2275359/jquery-single-quote-in-json-response

allan

Can you show us a sample of your output please? Yes single quotes are perfectly valid in JSON - the issue previously in this thread was that there were two levels of interpretation and the escaping was needed for the second.

Allan

Dhurga

Redraw is not working.....
After insert and delete the datagrid is not working can you give any idea..
oTable.fnDraw();

Dhurga

Redraw is not working.....
After insert and delete the datagrid is not working can you give any idea..
oTable.fnDraw();

allan

[quote]Dhurga said: Redraw is not working.....[/quote]

How does this relate to the original thread? You are going to have to give a lot more information than "Redraw is not working"...

inf1h

FIXED. Thx ^^