close
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.
FROM `$table` with FROM $table to remove backticks.$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.
this query will never work since you are giving a query when you are waiting for a table name:
{“error”:”An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1103 Incorrect table name ‘\r\n ( \r\n SELECT \r\n M.id, \r\n M.name, \r\n M.email, \r\n L.country \r\n FROM member'”}