title: Datatables + PHP: Server-Side Processing on Multiple Tables
body: <p><strong>How can I get Datatables <em>Server-Side Processing</em> script to work with a custom query? I need to select columns from multiple tables and have Datatables render them.</strong></p>
  
  <p>Datatables.net's Server-Side Processing (SSP) with PHP is summarized here: <a href="https://datatables.net/examples/server_side/simple.html" rel="noreferrer">https://datatables.net/examples/server_side/simple.html</a></p>
  
  <p>I found this <a href="https://stackoverflow.com/questions/9870154/datatables-server-side-processing-inner-join-or-multiple-tables">SO question</a>, but the original poster never provided his solution. I don't have sufficient reputation to ask him to provide more detail.</p>
  
  <p>Here is my raw SQL without using Datatable's SSP</p>
  
  <pre><code>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
  */
  </code></pre>
  
  <p>How can I get Datatables to run queries off the above using SSP?</p>
  
  <p>It appears <a href="https://github.com/DataTables/DataTables/blob/master/examples/server_side/scripts/server_processing.php#L22" rel="noreferrer">server_processing.php</a> only accepts 1 table and no custom filtering (i.e., <code>WHERE</code> clauses).</p>
  
  <pre><code>// 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 )
  );
  </code></pre>
  
  <p>However, <a href="https://github.com/DataTables/DataTables/blob/master/examples/server_side/scripts/ssp.class.php#L199" rel="noreferrer">ssp.class.php</a> <strong>does</strong> support filtering using <code>WHERE</code>. I'm thinking I need to modify <code>ssp.class.php</code> to force in my <code>WHERE</code> clause</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Found a solution. Will post when I have free time.</p>