two frustratingly simple problems

toonces

hi all. i have two seemingly simple errors in my data tables that i can't troubleshoot and i'm hoping someone can point me in the right direction..

problem 1. my data table works fine to load (using a server_processing.php file similar to the example one provided by the author) and no worries. However, when i type something into the search box, i get a JSON error saying JSON data could not be processed. Why would the data for loading the table be fine but search wouldn't be??

problem 2. I have 692 total records in my database. When the datatable is first loaded however, the status message at the bottom says "Showing 1 to 10 of 685 entries (filtered from 692 total entries)". The problem is, there is no filtering on the query! Is there a maximum number of records or something that limits it to 685? I've checked the server processing script and there's nothing in the filtering at all.

allan

1. Sounds like an SQL error. What is being returned by the server from the Ajax request?

2. iTotalRecords and iTotalDisplayRecords aren't being used correctly in that case. From: http://datatables.net/usage/server-side

> iTotalRecords - Total records, before filtering (i.e. the total number of records in the database)

> iTotalDisplayRecords - Total records, after filtering (i.e. the total number of records after filtering has been applied - not just the number of records being returned in this result set)

Allan

toonces

update... problem 2 is solved, it was a sql issue and not a datatables issue. I've figured out what issue #1 is caused by, but can't figure out how to get around it. As you said though Allan, it is definitely a SQL error created because of the way the server_processing script creates the mysql query for filtered sets.

For this particular table, I needed a complex query (i could get into why but it's probably not relevant here) that uses a JOIN statement. In the standard template for a server_processing.php script that comes with the datatables package, the code looks like this :

[code]$sQuery = "
SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
FROM $sTable
$sWhere
$sOrder
$sLimit
";[/code]

but my modified one has this :

[code]
$sQuery = "SELECT SQL_CALC_FOUND_ROWS c.ref as cref, c.serialnumber, c.customer, c.location, c.user, c.model, c.CPU, FCS_customers.ref
FROM FCS_machinesData as c JOIN FCS_customers on c.customer = FCS_customers.customername
$sWhere
$sOrder
$sLimit
";
[/code]

which works fine for the initial load, but as soon as you type anything into the search box (ie any "WHERE" clause is appended) it returns a JSON error. I'm pretty certain it must append the WHERE clause in a way that makes the SQL syntax no longer valid, but unsure on how to fix.

rewen

toonces,

Can you get the final value of $sQuery so that we can see the actual query being sent to your server and figure out why it's invalid?

Just do something like echo $sQuery; and check for the query within the body of the ajax request (using Chrome Devtools or Firebug, etc).

toonces

ok i've loaded chrome. stupid question : where in dev tools will the body of the ajax request show up?

allan

Chrome Dev tools (which are superb) are built into all versions of Chrome now. Safari also has them (they are really 'Webkit tools') and Firebug will work just fine on Macs as well.

toonces

ok, some progress at least in finding out what's broken. I did as you suggested and captured the sql coming out of the ajax request, and it's obvious why it doesn't work. Question is, how to fix it.

my join statement uses aliases (c.ref as cref for example) and so when all the LIKE and ORDER BY clauses refer to names of fields different from the assigned ones, havoc ensues. see below : (this is what happens if the string "searchstring" is entered in the search textbox, which causes a json error)

[code]
SELECT SQL_CALC_FOUND_ROWS c.ref as cref, c.serialnumber, c.customer, c.location, c.user, c.model, c.CPU, FCS_customers.ref FROM FCS_machinesData as c JOIN FCS_customers on c.customer = FCS_customers.customername WHERE (cref LIKE '%searchstring%' OR serialnumber LIKE '%searchstring%' OR customer LIKE '%searchstring%' OR location LIKE '%searchstring%' OR user LIKE '%searchstring%' OR model LIKE '%searchstring%' OR CPU LIKE '%searchstring%' OR ref LIKE '%searchstring%' )
ORDER BY cref asc LIMIT 0, 10

[/code]

toonces

no ideas fellows? Still at a loss...

toonces

bump......

allan

I guess you need to namespace the SQL columns? Not sure to be honest - what happens if you runt he query in something like phpMyAdmin or on the command line. Do you get an sql error?

Allan

toonces

solved it, but i consider it less a solution than simply giving up and doing it a different way. For the record, this was the problem : the routine in the stock server_processing script has a "for" loop that creates WHERE clauses based on the column names listed at the beginning of the script. BUT, if lower in the script you have any SQL (like I did) that uses different names (ie an alias, or a suffix when two tables have a column of the same name) for the columns, the auto generated WHERE clauses use the wrong column names. But if you change the column names at the top, it won't work either because the aliases hadn't been defined yet. so you get a sort of mexican standoff, catch 22, whatever you want to call it. The solution i settled on was to ditch the loop and hard wire the WHERE clauses, ie several lines where the only moving part was the $_GET['sSearch'] variable, but the column names were hand coded. That was tedious but it fixed it. The main drawback there of course is you don't have reusable code as it's hard wired for one table only.