How can I get Datatables Server-Side Processing script to work with a custom query? I need to select columns from multiple tables and have Datatables render them.
Datatables.net’s Server-Side Processing (SSP) with PHP is summarized here: https://datatables.net/examples/server_side/simple.html
I found this SO question, but the original poster never provided his solution. I don’t have sufficient reputation to ask him to provide more detail.
Here is my raw SQL without using Datatable’s SSP
SELECT tbl_houses.style, tbl_houses.roomCount, tbl_residents.firstName, tbl_residents.lastName FROM tbl_houses, tbl_residents WHERE tbl_houses.houseID = tbl_residents.residentID /* * # Equivalent query using JOIN suggested by @KumarRakesh * # Note: JOIN ... ON is a synonym for INNER JOIN ... ON * # Using JOIN conforms to syntax spec'd by ANSI-92 https://stackoverflow.com/a/894855/946957 * * SELECT tbl_houses.style, tbl_houses.roomCount, tbl_residents.firstName, tbl_residents.lastName * FROM tbl_houses * JOIN tbl_residents ON tbl_houses.houseID = tbl_residents.residentID */
How can I get Datatables to run queries off the above using SSP?
It appears server_processing.php only accepts 1 table and no custom filtering (i.e., WHERE
clauses).
// DB table to use $table = 'datatables_demo'; // Table's primary key $primaryKey = 'id'; /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * If you just want to use the basic configuration for DataTables with PHP * server-side, there is no need to edit below this line. */ require( 'ssp.class.php' ); echo json_encode( SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns ) );
However, ssp.class.php does support filtering using WHERE
. I’m thinking I need to modify ssp.class.php
to force in my WHERE
clause
UPDATE
Found a solution. Will post when I have free time.
Advertisement
Answer
TL;DR: I ended up using a modification of the original Datatables ssp.class.php
called ssp.php
implemented by Emran Ul Hadi: https://github.com/emran/ssp
His modification accepts JOIN, WHERE, GROUP BY and column aliases. Although the file hasn’t been updated in over a year, it still works with DataTables 1.12.x. I made some modifications to his version that increases its robustness and improves the documentation with clearer examples.
Will post my mods/updates here when I have a bit more time. Eventually I hope to put in a pull-request to get my updates into his repository.