DataTables & Custom SQL
DataTables & Custom SQL
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?
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?
This discussion has been closed.
Replies
[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
I assume I run my server_processing.php and cut and paste results into jsonlint.com.
Allan
Any other way to debug?
Allan
Allan
Allan
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.
[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
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
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
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. :(
[code]
echo json_encode($your_array);
[/code]
Its better than building the json in that crazy way.
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?
btw your sql syntax is giving error. can you give the results of "echo $sOutput; "
Allan
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
> 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
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?
..
..
..
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.
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
I'd say search issue of mine is bSearchable. I'll try modifying Individual column filtering $sWhere .= " AND ";
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
so.. lets try this and lets see what 'll happen next ^^
[code]
..
..
$sWhere
GROUP BY ponumber
$sOrder
$sLimit
";
[/code]