two frustratingly simple problems
two frustratingly simple problems
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.
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.
This discussion has been closed.
Replies
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
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.
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).
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]
Allan