Christian Christian - 4 months ago 87
MySQL Question

Datatables + PHP: Server-Side Processing on Multiple Tables

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.'s Server-Side Processing (SSP) with PHP is summarized here:

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.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
* SELECT, 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.,

// 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
. I'm thinking I need to modify
to force in my


Found a solution. Will post when I have free time.


TL;DR: I ended up using a modification of the original Datatables ssp.class.php implemented by Emran Ul Hadi:

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. Eventually I hope to put in a pull-request to get these updates into his repository.