How to JOIN Multiple Tables in DataTables Server-side Processing using PHP

DataTables server-side processing is used to fetch data from the database and list them in a tabular view. In most cases, the single database table is used in DataTables with server-side processing. The SSP class (ssp.class.php) gives you the possibility to JOIN multiple tables with DataTables server-side processing. You can apply the LEFT or INNER JOIN clause to the SQL query in server-side processing. In this tutorial, we will show you how to JOIN multiple tables with server-side processing in DataTables using PHP.

In the following example code snippet, we will add LEFT JOIN query to join 2 database tables in DataTables server-side processing using PHP.

1. Edit the ssp.class.php file and replace all instances of FROM `$table` with FROM $table to remove backticks.
2. Define sub-query with JOIN or any other clauses in the $table variable.

<?php  
 
// Database connection info  
$dbDetails = array(  
    'host' => 'localhost',  
    'user' => 'root',  
    'pass' => 'root',  
    'db'   => 'codexworld_db'  
);  
  
$table = <<<EOT 
 ( 
    SELECT 
      M.id, 
      M.name, 
      M.email, 
      L.country 
    FROM members M 
    LEFT JOIN locations L ON L.member_id = M.id 
 ) temp 
EOT; 
 
$primaryKey = 'id';  
  
$columns = array(  
    array( 'db' => 'id',    'dt' => 0 ),  
    array( 'db' => 'name',  'dt' => 1 ),  
    array( 'db' => 'email', 'dt' => 2 ),  
    array( 'db' => 'country','dt' => 3 ) 
);  
  
// Include SQL query processing class  
require 'ssp.class.php';  
  
// Output data as json format  
echo json_encode(  
    SSP::simple( $_GET, $dbDetails, $table, $primaryKey, $columns )  
); 
 
?>

Here we have used LEFT JOIN to join 2 tables, you can use query for multiple tables including any complex SQL query.