Who has an idea how to setup the server-side datatables using ssp.class.php please?
Who has an idea how to setup the server-side datatables using ssp.class.php please?
Not working search bar, sorting, pagination and showing 0 to 0 of 0 entries but showing data from database
Can someone help me please?
Ajax Result:
{"draw":0,"recordsTotal":19,"recordsFiltered":19,"data":[["Adrian Sellote","9260354100","1234","Philippines","6329","6th Aug 22"],["Joselle Salpocial","9260354100","1234","Philippines","6329","6th Aug 22"],["Robin B. Sellote","9260354100","1234","Philippines","6239","6th Aug 22"],["asdfa","8498465464","1032132","Philippines","6465546","6th Aug 22"],["asdf","5498464654","131313","Philippines","65646","6th Aug 22"],["asdf","3213123123","asdf1321321","Philippines","31313","6th Aug 22"],["asdfasdf","6846464654","13213213","Philippines","3132131","6th Aug 22"],["asdf","5465465465","121233","Philippines","3213","6th Aug 22"],["asdf","5454621321","asdfasdf1","Philippines","51312","6th Aug 22"],["asdf","3351312321","asdfasd32","Philippines","23123","6th Aug 22"],["asdf","3132231321","23113","Philippines","211321","6th Aug 22"],["asdf","5465465465","13213","Philippines","312321","6th Aug 22"],["asdf","1313213212","13213213","Philippines","121213","6th Aug 22"],["asdfasfd","6546454654","6464646","Philippines","145121321","6th Aug 22"],["asdf","5465654654","321321321","Philippines","31321","6th Aug 22"],["asdfasdf","5464645465","13212313","Philippines","321321","6th Aug 22"],["asdfasfd","3213132132","1321321321","Philippines","2133213","6th Aug 22"],["asdfasdasf","3132132132","32132131","Philippines","321321231","6th Aug 22"],["asdfasdasdfasdf","2121312132","1321321","Philippines","21321","6th Aug 22"]]}
HTML
<link rel="stylesheet" href="https://cdn.datatables.net/1.12.1/css/dataTables.bootstrap5.min.css">
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script src="https://cdn.datatables.net/1.12.1/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.12.1/js/dataTables.bootstrap5.min.js"></script>
<table class="table table-striped table-bordered responsive display no-wrap" id="example" style="width: 100%">
<thead>
<tr>
<th>Customer Name</th>
<th>Contact Number</th>
<th>Address</th>
<th>Country</th>
<th>Postal Code</th>
<th>Date Created</th>
</tr>
</thead>
<tbody></tbody>
<tfoot>
<tr>
<th>Customer Name</th>
<th>Contact Number</th>
<th>Address</th>
<th>Country</th>
<th>Postal Code</th>
<th>Date Created</th>
</tr>
</tfoot>
</table>
JQuery
$(document).ready(function() {
$('#example').DataTable({
processing: true,
serverSide: true,
ajax: '/pages/collections/datatables-server-side/index-action.php',
paging: true,
ordering: true,
info: true,
order: [
[0, 'asc']
],
"columnDefs": [
{ "visible": false, "targets": [] }
],
bDestroy: true,
});
table.buttons().container().appendTo( '#example_wrapper .col-md-6:eq(0)' );
$('#example tbody').on('click', 'tr', function () {
if ($(this).hasClass('selected')) {
$(this).removeClass('selected');
} else {
table.$('tr.selected').removeClass('selected');
$(this).addClass('selected');
}
});
});
Server-side Processing Script (index-action.php)
ajax: '/pages/collections/datatables-server-side/index-action.php'
<?php
$table = 'customer_tbl';
$primaryKey = 'id';
$columns = array(
array( 'db' => '_customer_name', 'dt' => 0 ),
array( 'db' => '_contact_number', 'dt' => 1 ),
array( 'db' => '_address', 'dt' => 2 ),
array( 'db' => '_country', 'dt' => 3 ),
array( 'db' => '_postal_code', 'dt' => 4 ),
array(
'db' => '_created_at',
'dt' => 5,
'formatter' => function( $d, $row ) {
return date( 'jS M y', strtotime($d));
}
)
);
$sql_details = array(
'user' => 'root',
'pass' => '',
'db' => 'php_db',
'host' => 'localhost'
);
require( 'ssp.class.php' );
echo json_encode(
SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);
ssp.class.php
https://github.com/DataTables/DataTablesSrc/blob/master/examples/server_side/scripts/ssp.class.php
```
static function simple ( $request, $conn, $table, $primaryKey, $columns )
{
$bindings = array();
$db = self::db( $conn );
// Build the SQL query string from the request
$limit = self::limit( $request, $columns );
$order = self::order( $request, $columns );
$where = self::filter( $request, $columns, $bindings );
// Main query to actually get the data
$data = self::sql_exec( $db, $bindings,
"SELECT `".implode("`, `", self::pluck($columns, 'db'))."`
FROM `$table`
$where
$order
$limit"
);
// Data set length after filtering
$resFilterLength = self::sql_exec( $db, $bindings,
"SELECT COUNT(`{$primaryKey}`)
FROM `$table`
$where"
);
$recordsFiltered = $resFilterLength[0][0];
// Total data set length
$resTotalLength = self::sql_exec( $db,
"SELECT COUNT(`{$primaryKey}`)
FROM `$table`"
);
$recordsTotal = $resTotalLength[0][0];
/*
* Output
*/
return array(
"draw" => isset ( $request['draw'] ) ?
intval( $request['draw'] ) :
0,
"recordsTotal" => intval( $recordsTotal ),
"recordsFiltered" => intval( $recordsFiltered ),
"data" => self::data_output( $columns, $data )
);
}