I am trying to use datatables.net to build out a table on my website. I have a basic table generating OK using their example and 1 table but I have a complex query with joins, where clause, subquery, etc. and I can't figure out how to create a table with the results.
Their basic example looks like this:
// DB table to use
$table = 'TableNameToQuery';
// Table's primary key
$primaryKey = 'pk';
With this at the end:
echo json_encode(
SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
The MySQL query looks something along the lines of:
select t1.col1, col2, col3, col4 from table1 t1 left join table 2 t2 on t1.col1 = t2.col1 where t2.col5 = 'complete'
Server-side processing PHP script that is included in DataTables distribution (examples/server_side/scripts/ssp.class.php) is very simplistic and doesn't support joins and sub-queries right out of the box.
Emran ul Hadi released modified SSP class that supports what you need, please visit his repository at github.com/emran/ssp.
Also in the original SSP class in addition to SSP::simple there is a SSP::complex method that is defined as follows:
static function complex ( $request, $conn, $table, $primaryKey, $columns, $whereResult=null, $whereAll=null )
where $whereResult is WHERE condition to apply to the result set and $whereAll is WHERE condition to apply to all queries. You may use conditions with SSP::complex in $whereResult and/or $whereAll parameters.