Getting JSON Parsing Error & SQL Syntax Error

PriceGuideApp

First of all - great tool, and I've gotten it to work reading from an AJAX source - but I have over 20,000 data sets & adding more, so I need server side processing.

I'm not really sure why it doesn't work, below is my .php file code, I get a pop up box saying JSON can't parse. I see an error in the debugger that says: 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 'set, card_no, player_name, serial_no
FROM orders

ORDE' at line 1

URL: http://www.priceguideapp.com/test/main.html

[code] $aColumns = array( 'sport', 'set', 'card_no', 'player_name', 'serial_no', 'price' );

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

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

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

Here is my HTML:
[code]



Price Guide App Beta 1.1

@import "/media/css/demo_page.css";
@import "/media/css/demo_table.css";




$(document).ready(function() {
$('#example').dataTable( {
"aoColumns": [
{ "sWidth": "10%" },
null,
{ "sWidth": "7%" },
{ "sWidth": "35%" },
{ "sWidth": "7%" },
{ "sWidth": "10%" }
],
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "/scripts/server_processing.php"
} );
} );
[/code]


Any ideas? I'm sure it's something pretty easy, but I can't seem to get the data to load. Doesn't look like it's even returning the data. Thanks, I'll be donating next payday.

bandan88

On the JSON parsing error have you tried changing line 4

$sIndexColumn = "id";

to

$sIndexColumn = "sport"; ?

PriceGuideApp

Thanks bandan88 - I'm thinking it's actually my data. I uploaded the sample data - and the table works. I tried validating my json, looks like it breaks when some has the name = O'Neil .... I guess it's possible the ' sign in the guys name is messing with the return? I'm going to pull those results out and re-try.

Either way, the AJAX source works for now, and I'll be testing the server side. If that's not the problem - or I find a work around, I'll try to update this.

bandan88

PriceGuideApp,

Glad that the table works. To test your " ' " problem, I changed some of my data to include an ' in one of the cells in the mysql table and datatables rendered it with no problems.

PriceGuideApp

I just wanted to follow up on this just in case someone else has a similar problem.

I ended up running some tests and found out that one of my columns was named 'set' and for some reason that caused the above error.

Changed it to 'set_name' and it works ... lol

Simple fix, so if you have this problem - try re-naming some of your columns and maybe that will do the trick.