SQL syntax errors...

Julan

Hi, I searched and got very confused with the related threads.

Here's my situation:
I simply want to display a large table of about 50,000 rows and 8 columns (5 of which I want visible through the table).

Unfortunately, every time I load the page, I get this error:
[code]
DataTables warning: JSON data from server could not be parsed. This is caused by a JSON formatting error.
[/code]

So I run Firebug, which tells me that there is an SQL syntax error:
[code]
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc, code, price FROM products ORDER BY name asc LIMIT 0, 10' at line 1
[/code]

And I can't really get any further than that.

You would think this was quite fool-proof... but not today. =)

Julan

So I tried changing [code]$aColumns = array ( 'name', 'desc', 'code', 'price' );[/code] to [code]$aColumns = "'name', 'desc', 'code', 'price'";[/code], obviously MySQL didn't like it as an array, but now the script crashes jquery.

I also just looked at the support page... I guess I have to click the beer icon if I want to get this fixed? Anyone know of any working alternatives?

nmehal

I was into the same problem. but i have noticed that if your string contains any newline or carrage return, JSON formatting is not valid. "Since many byte values turn into illegal characters if you try String conversion,........ "

Solution:

1- Find the output generated. validate it at http://jsonlint.com/ .
2- If you get error. analyse the string generated by your script. remove carrage return/newline character from the string. and it will work fine.

i used
$sOutput = str_replace(array("\r\n", "\n", "\r"), ' ',$sOutput);

Regards

Julan

Thanks for trying, but I'm afraid that didn't help. I don't know how to find the output since I only get the SQL syntax error.

I tried your tip on adding str_replace to $sOutput, but the script doesn't seem to even use $sOutput... I can completely remove the entire block of code and it just runs through and stops with the syntax error again.

There's still a few other jQuery table scripts that I can try out, a couple of them work but unfortunately none are as feature-rich as this one.

nmehal

Please post your code here. Let me have a look. I am also newbee with this stuff..

Julan

Well, in my header I have this:
[code]



$(document).ready(function() {
$('#prod-tbl').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "server_processing.php"
} );
} );

[/code]

And for server_processing.php I have this:

[code]
<?php

$aColumns = array( 'name', 'desc', 'code', 'price' );
$sIndexColumn = "id";
$sTable = "products";

$gaSql['user'] = "foo";
$gaSql['password'] = "bar";
$gaSql['db'] = "db";
$gaSql['server'] = "localhost";

$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'] );

$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ".
mysql_real_escape_string( $_GET['iDisplayLength'] );
}


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

nmehal

Im pretty much sure..there is no problem with the query.
bring up firebug. in the firebug pan click "console". reload server_processing.php. you will see something like this with a plus sign.

http://test.example.com/user/server_process.php?_=1288862398166&sEcho=1&iColumns=5&sColumns=&iDisplayStart=0&iDisplayLength=10&sNames=%2C%2C%2C%2C&sSearch=&bRegex=false&sSearch_0=&bRegex_0=false&bSearchable_0=true&sSearch_1=&bRegex_1=false&bSearchable_1=true&sSearch_2=&bRegex_2=false&bSearchable_2=true&sSearch_3=&bRegex_3=false&bSearchable_3=true&sSearch_4=&bRegex_4=false&bSearchable_4=true

click the plus sign and you can see the output. 0r right click the long location text and click copy reponse body. validate the output in http://jsonlint.com/

i hope it helps.

regards

Julan

Heh. =P

I got that far before, but I just get an SQL syntax error, the response says:
[code]
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc, code, price
FROM products

ORDER BY name
asc
LIMIT 0, 10' at line 1
[/code]

I'm sure you're right about it having something to do with the line breaks in the JSON string though, but changing the $sOutput string in server_processing.php didn't seem to make any difference...

Julan

This is obviously something to do with the $aColumns array...

Trying different usage of quotes around the coulmn names, I was able to get the following JSON response:
[code]
{"sEcho": 1, "iTotalRecords": 65536, "iTotalDisplayRecords": 65536, "aaData": [ ["","","","",""],["","","","",""],["","","","",""],["","","","",""],["","","","",""],["","","","",""],["","","","",""],["","","","",""],["","","","",""],["","","","",""]] }
[/code]

But I've tried everything now, from single quotes encapsulating double quotes to two single quotes holding the array... nothing actually brings up the table data. =(

nmehal

JSON strings looks okay. as you can validate it. i believe you have data in the table from where you are trying to populate datatables grid.

Julan

There are 2000 records in the table I'm connecting to... but none of the rows are showing in the datatables grid.

I am also getting the following error with Firebug:

[code]nTds[i] is undefined
[Break on this error] if ( nTds[i].className.indexOf(sClass+"1") != -1 ) [/code]

This is on "jquery.dataTables.js" on line 4649.

Julan

Fixed it by using a different table.

I don't know what it was in the other table that the script didn't like, but whatever it was, it seems to work okay now.

Thanks @nmehal for your input. =)