complex SQL in server processing script?

DataHound

hello all. I have a nicely working datatable, but i want to make the dataset that is used more precise, and to do that i need some fancy SQL. However, the standard serverProcessing.php that comes as an example with datatables seems hardwired to expect certain things like WHERE statements. Only problem with that is that I need to use some fancier sql that doesn't use a WHERE statement. spceifically, I want to use a statement with an inner join, a simplified version of which would be something like :

SELECT c.ref AS cref, sometable.*
FROM someothertable AS c
JOIN sometable ON c.customername = sometable.customer

I just did that off the top of my head, so if the sql isn't quite perfect cut me some slack - the SQL isn't the point anyhow :-) What i can't quite figure out is how to end up with a complex dataset like the above when the server processing php expects :

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

allan

This is an example script which has been modified to do a JOIN: http://datatables.net/dev/server_processing.txt . - however, it might or might not be the right kind of JOIN for you! In theory the server-side processing script can be of any complexity you need, as long as the script responds with the format DataTables expects, it doesn't matter what the script is or what it looks like. My example one should taken as a starting point, rather than an "ultimate" script!

Allan

PeteB

Here's an example I use for joining 3 tables:

[code] /* DB table to use */
$sTable1 = "games";
$sTable2 = "gameplayers";
$sTable3 = "players";

$sTable = $sTable1 . ' LEFT JOIN ' . $sTable2 . ' ON (' . $sTable2 . '.gpgameID = ' . $sTable1 . '.gameID)';
$sTable = $sTable . ' LEFT JOIN ' . $sTable3 . ' ON (' . $sTable3 . '.playerID = ' . $sTable2 . '.gpplayerID)';
$sTable = $sTable . ' WHERE ' . $sTable1 . '.gameID = ' . $gameno . '';
[/code]
HTH

Pete.