DataTables & Custom SQL

drrock

I'm using server side processing with PHP 4.x so not JSON version for serverside processing.

I have SQL statement below working for non-DataTables application but can't figure out how to make my SQL work with DataTables and hoping someone can help.

I may not be using correct termonlogy but here goes...

I have one table (po_spots) that can have multiple date ranges (launchdate) grouped with a primary key value (ponumber). I need to find the earliest date within that group and eventually assign as 'mindates.' Then join with table po_gen_info.

Like I said, my SQL statement is working but not with Datatable. Working SQL is:

[code]
SELECT po_gen_info.client,po_gen_info.promotitle,po_gen_info.projectlead,po_gen_info.station_kamx,po_gen_info.station_kkmj,po_gen_info.station_kjce,mindates.*
FROM ( SELECT ponumber
, MIN(launchdate) AS earliestdate
FROM po_spots WHERE `produced` != 'Yes'
GROUP
BY ponumber ) AS mindates

INNER
JOIN po_spots
ON po_spots.ponumber = mindates.ponumber
AND po_spots.launchdate = mindates.earliestdate
INNER
JOIN po_gen_info ON po_spots.ponumber = po_gen_info.ponumber
GROUP
BY ponumber
ORDER
BY mindates.earliestdate
[/code]

Allan provides an example server_processing.php file that I successfully can use to grab my SQL data. But I don't know how to customize the Where clause within server_processing.php file.

Can someone please help with customizing the Where clause that also does not break the sorting functionality, please?

drrock

I tried inserting my SQL statement with table join within server_processing.php as follows:

[code]
/*
* SQL queries
* Get data to display
*/
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS po_gen_info.client,po_gen_info.promotitle,po_gen_info.projectlead,po_gen_info.station_kamx,po_gen_info.station_kkmj,po_gen_info.station_kjce,mindates.*
FROM ( SELECT ponumber
, MIN(launchdate) AS earliestdate
FROM po_spots WHERE produced != 'Yes'
GROUP
BY ponumber ) AS mindates

INNER
JOIN po_spots
ON po_spots.ponumber = mindates.ponumber
AND po_spots.launchdate = mindates.earliestdate
INNER
JOIN po_gen_info ON po_spots.ponumber = po_gen_info.ponumber
GROUP
BY ponumber
ORDER
BY mindates.earliestdate
$sWhere
$sOrder
$sLimit
";
[/code]

This does not work and I get the "JSON data from server could not be parsed..."

Can someone please help?

allan

If you are getting that error then I'd suggest running the returned JSON through http://jsonlint.com to see where the formatting problem is.

Allan

drrock

jsonlint.com says JSON is valid.

I assume I run my server_processing.php and cut and paste results into jsonlint.com.

allan

No - get the JSON from Firebug. DataTables will send parameters to the script that it needs to deal with.

Allan

drrock

Oh man... Firebug was crashing Firefox 4 so I had to uninstall it.

Any other way to debug?

allan

Heh - good old Firebug... Use Safari or Chrome :-) The inspector tools in Webkit are impressive.

Allan

drrock

I get an error "You have an error in your SQL syntax; check the manual that corresponds to your My SQL server version for the right syntax to use near "ORDER BY client asc LIMIT 0,10' at line 19" but that's not in my custom SQL query so must be coming from DataTables?

allan

Nope - DataTables has nothing to do with the SQL - that is all done on the server-side. It is generated and run by the information that DataTables sends to the server - so there is something wrong with the SQL that is being built. I'd suggest printing out the full SQL statement that is generated and having a look at that.

Allan

drrock

Hmmm... there is something else going on here because the exact SQL is working in a file not using DataTables but when I plug in the SQL in server_processing_....php I get the error. I can run server_processing_....php directly (i.e., http://.../server_processing_....php) and see the results so I have to assume the SQL is not the issue.

allan

The SQL that will run if you just access it directly will be different from the SQL run when DataTables calls it since it calls it with parameters. Using the inspector tools in Webkit you can see what these parameters are and call the script in exactly that same manner, which will be useful for debugging it.

Allan

drrock

Maybe I see what is going on here.

Will DataTables by default sort by first column listed first in the $aColumns array? So in the example below, will return results in 'client' column alphabetically?

$aColumns = array( 'client', 'promotitle', 'projectlead',... 'ponumber' );

Also, since I'm doing a table join, how do I configure $sTable variable? Currently I only have it set as $sTable = "po_gen_info"; but I also need to read table 'po_spots' since of course I'm doing a join.

drrock

Here is my server_processing.php file:

[code]
$aColumns = array( 'ponumber', 'datesubmitted', 'client', 'promotitle', 'projectlead', 'earliestdate' );

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

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

...

/*
* SQL queries
* Get data to display
*/
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS po_gen_info.datesubmitted,po_gen_info.client,po_gen_info.promotitle,po_gen_info.projectlead,po_gen_info.station_kamx,po_gen_info.station_kkmj,po_gen_info.station_kjce,mindates.*
FROM ( SELECT ponumber
, MIN(launchdate) AS earliestdate
FROM po_spots WHERE produced != 'Yes'
GROUP
BY ponumber ) AS mindates

INNER
JOIN po_spots
ON po_spots.ponumber = mindates.ponumber
AND po_spots.launchdate = mindates.earliestdate
INNER
JOIN po_gen_info ON po_spots.ponumber = po_gen_info.ponumber
GROUP
BY ponumber
ORDER
BY mindates.earliestdate
$sWhere
$sOrder
$sLimit
";
...

/*
* Output
*/
$sOutput = '{';
$sOutput .= '"sEcho": '.intval($_GET['sEcho']).', ';
$sOutput .= '"iTotalRecords": '.$iTotal.', ';
$sOutput .= '"iTotalDisplayRecords": '.$iFilteredTotal.', ';
$sOutput .= '"aaData": [ ';
while ( $aRow = mysql_fetch_array( $rResult ) )
{
$sOutput .= "[";
for ( $i=0 ; $i

drrock

And URL is http://entercomaustin.com/po/search_by_client_mindates.php

allan

Here is the URL DataTables is trying to load:

http://entercomaustin.com/po/DataTables_1_7_6/examples/examples_support/server_processing_poclient_mindates.php?_=1306531149254&sEcho=1&iColumns=6&sColumns=&iDisplayStart=0&iDisplayLength=10&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&sSearch_5=&bRegex_5=false&bSearchable_5=true&iSortingCols=1&iSortCol_0=0&sSortDir_0=asc&bSortable_0=true&bSortable_1=true&bSortable_2=true&bSortable_3=true&bSortable_4=true&bSortable_5=true

As you can see there is something in the script which isn't very happy about the parameters being sent. It might need some debugging of the script to figure out where it is going wrong.

Allan

drrock

And how would I debug script?

Within the server_processing_....php script, I've not changed any of your example sections under Ordering, Filtering or Individual Column Filtering. Are those affecting parameters being sent?

So now I'm stuck. :(

allan

Echo out the constructed SQL and paste it in here please.

Allan

drrock

Hi Allan,

Sorry but I don't understand your request. Are you saying give you the results of echo $sOutput? Otherwise, my SQL is shown above under $sQuery.

And sorry I'm such a hack. :(

numberone

just create an array with your values and
[code]
echo json_encode($your_array);
[/code]
Its better than building the json in that crazy way.

drrock

Hmmm... not sure that's possible.

Per my original post, "I'm using server side processing with PHP 4.x so not JSON version for serverside processing."

I thought I could not use json_encode if running PHP 4 as PHP 4 does not support JSON. Is that not correct?

numberone

oops. yes mybad. didnt read that sorry. You are correct, 5.2+ needed for that.

btw your sql syntax is giving error. can you give the results of "echo $sOutput; "

allan

The error is that the SQL that is built has an error it in. Therefore I'd suggest printing out $sQuery as there will be an error in it somewhere.

Allan

drrock

The SQL itself is valid. I'm using it in a non-DataTables application without issue and can successfully run in it phpMyAdmin. But I got to thinking and as I previously suspected, the issue must lie in how DataTables uses the $sWhere, $sOrder and or $sLimit variables. I was originally including those in $sQuery so instead I simply removed $sWhere, $sOrder and $sLimit variables from $sQuery and finally got it to work. YES! Small steps to final solution.

But now as you might expect (and forgive me as I do not totally understand how DataTables works), sorting or searching the results does not work presumably as a result of removing $sWhere, $sOrder and $sLimit variables from $sQuery

So, how do I get sorting and searching to work? Do I edit $sWhere and $sOrder to include my custom SQL? As I wrote previously, I have not edited those sections in the modified example server_processing....php file. If that is the solution, how do I configure $sWhere, $sOrder, etc?

I can post sections of code here but there is a character limit by this forum. So around line 119 in example server_processing.php we have my modified $sQuery with $sWhere, $sOrder and $sLimit variables removed:

[code]
$sQuery = "SELECT SQL_CALC_FOUND_ROWS po_gen_info.datesubmitted,po_gen_info.client,po_gen_info.promotitle,po_gen_info.projectlead,po_gen_info.station_kamx,po_gen_info.station_kkmj,po_gen_info.station_kjce,mindates.*
FROM ( SELECT ponumber, MIN(launchdate) AS earliestdate
FROM po_spots WHERE produced != 'Yes'
GROUP BY ponumber ) AS mindates

INNER JOIN po_spots
ON po_spots.ponumber = mindates.ponumber
AND po_spots.launchdate = mindates.earliestdate
INNER JOIN po_gen_info ON po_spots.ponumber = po_gen_info.ponumber
GROUP BY ponumber
ORDER BY mindates.earliestdate";
[/code]

And around line 59 Ordering section is :
[code]
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i

allan

You say:

> The SQL itself is valid

However - the error states that there is an error in the SQL that is run against the SQL server:

>> "You have an error in your SQL syntax; check the manual that corresponds to your My SQL server version for the right syntax to use near "ORDER BY client asc LIMIT 0,10' at line 19"

So there the SQL is invalid. One thing to bare in mind here is that DataTables is a Javascript library - it runs on the client-side. The server_processing.php script is an example script showing how the requirements for a server-side processing data source can be created and passed back to DataTables on the client-side. The server_processing.php script works independently of DataTables - although obviously it expects and accepts input for DataTables.

So as I suggested, I would print out $sQuery and see what the SQL that is being generated by the script is, as it is obviously not working as expected when you include the limit etc (you don't need to include that if you don't want to - it's just an example of how it can be done - obviously you are doing a join so it will need to be modified). There is documentation of the inputs and outputs for server-side processing that DataTables gives and expects here: http://datatables.net/usage/server-side

Allan

drrock

Sorry Allan. I'm not understanding the issue with SQL.

I am getting results: http://entercomaustin.com/po/search_by_client_mindates.php

That is the context in which I am saying the SQL is valid.

So I'm getting results but you are saying the reason I cannot sort, search or filter the results is because there is something wrong with my SQL?

numberone

[code]
..
..
..
GROUP
BY ponumber
ORDER
BY mindates.earliestdate
$sWhere
$sOrder
$sLimit
";
[/code]

i think the problem is here. It seems you are using ' ORDER BY ' twice.

Remove the first one..

[code]
..
..
..
GROUP
BY ponumber
$sWhere
$sOrder
$sLimit
";
[/code]

And it should work.

Regards.

drrock

Kool! That fixed the sorting issue. Thank you numberone!

Now the only remedy needed is to fix the Search form field issue. I assume if $sWhere is the issue, I'd need to modify:
[code]
/*
* Filtering
* NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here, but concerned about efficiency
* on very large tables, and MySQL's regex functionality is very limited
*/
$sWhere = "";
if ( $_GET['sSearch'] != "" )
{
$sWhere = "WHERE (";
for ( $i=0 ; $i

drrock

Allan: thanks for the server-side doc link. Interesting what one can find if they would only read the manual. ;)

I'd say search issue of mine is bSearchable. I'll try modifying Individual column filtering $sWhere .= " AND ";

drrock

Numberone,

Thanks again for your help so far.

I think you helped someone with a similar issue but when I looked at that forum post it was deleted.

Any ideas how to fix Search issue? I tried modifying /* Individual column filtering */ section above for $sWhere but no luck.

Cheers,

http://entercomaustin.com/po/search_by_client_mindates.php

numberone

well i can say that 'Where' statement must be before the 'GROUP BY' statement. Usually 'HAVING' statement is being used after 'GROUP BY'.

so.. lets try this and lets see what 'll happen next ^^

[code]
..
..
$sWhere
GROUP BY ponumber
$sOrder
$sLimit
";
[/code]

drrock

Sorry but that did not work.