Menu
Index.php HTML
$(document).ready(function() {
$('#users').DataTable({
"processing": true,
"serverSide": true,
"ajax": "server_processing.php"
});
});<head>
<title>Server Side DataTable</title>
<link href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css" rel="stylesheet" type="text/css">
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" type="text/css">
<link href="https://cdn.datatables.net/1.10.16/css/dataTables.bootstrap.min.css" rel="stylesheet" type="text/css">
</head>
<body>
<table id="users" class="table table-striped table-bordered" cellspacing="0" width="100%">
<thead>
<tr>
<th>Id</th>
<th>Fist Name</th>
<th>Last Name</th>
<th>Phone</th>
<th>Location</th>
</tr>
</thead>
</table>
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.16/js/dataTables.bootstrap.min.js"></script>Server Script
$table = 'users';
$primaryKey = 'id';
$columns = array(
array( 'db' => 'id', 'dt' => 0 ),
array( 'db' => 'first_name', 'dt' => 1 ),
array( 'db' => 'last_name', 'dt' => 2 ),
array( 'db' => 'phone', 'dt' => 3 ),
array( 'db' => 'site', 'dt' => 4 ),
);
// SQL server connection information
$sql_details = array(
'user' => 'root',
'pass' => '',
'db' => 'datatables',
'host' => 'localhost'
);
require( 'ssp.class.php' );
echo json_encode(
SSP::simple($_GET, $sql_details, $table, $primaryKey, $columns)
);
Database Tables
users
id| first_name| last_name| phone| site|
--- ---------- ---------- ------ ------
1 Barry Roux 0454554 1
2 Sylvester Gus 012124 2
sites
id| name
--- ----------
1 London
2 Edinburgh
Desired Output
ids| name| surname | number| location
--- ------ ---- ------- ------
1 Barry Roux 0454554 London
2 Sylvester Gus 012124 Edinburgh
The Above Code Shows a basic example of how to get records from One single Table (users) using server side datatable.
Now I would like to Join and show records from 2 Tables. using something like this Query :
SELECT u.id as ids, u.first_name as name , u.last_name as surname, u.phone as number , s.name as location
FROM users u
LEFT JOIN sites s ON s.id = u.site.
Any idea how I can do that ?
Please Help
354
asked Oct 17 '25 17:10
You've probably solved it by now... But (like me) others may come across this question, and I found a solution!
Here it is: https://www.gyrocode.com/articles/jquery-datatables-using-where-join-and-group-by-with-ssp-class-php/
You will need to edit the ssp.class.php. Find and replace all the and replace FROM `$table' with FROM $table (removing the back-ticks).
<?php
$table = <<<EOT
(
SELECT
a.id,
a.name,
b.position
FROM table a
LEFT JOIN positions b ON a.position_id = b.id
) temp
EOT;
$primaryKey = 'id';
$columns = array(
array( 'db' => 'id', 'dt' => 0 ),
array( 'db' => 'name', 'dt' => 1 ),
array( 'db' => 'position', 'dt' => 2 )
);
$sql_details = array(
'user' => '',
'pass' => '',
'db' => '',
'host' => ''
);
require( 'ssp.class.php' );
echo json_encode(
SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);
All credit goes to Michael Ryvkin.
141
Consider using https://github.com/emran/ssp as an alternative to ssp.class.php. I personally found it hard and impossible to work with ssp.class.php in joining tables.
43
answered Oct 20 '25 08:10
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
