DataTables Server-side Processing using PHP with MySQL
by Vincy. Last modified on July 9th, 2022.
DataTables is a jQuery library used to display the list of records in a HTML table with an intuitive interface. It includes features like search, pagination, sort, filter and more.
In a previous article, we have seen a custom code for search and pagination for the list of records. Using DataTables we can limit the number of records to be displayed per page.
DataTables extension provides both client-side and server-side processing. In this article, we are going to show the database results by using DataTables server-side processing.
A PHP file calls the domain class function by sending the database table, column and the configuration details. The domain class executes the query to read requested columns and returns array of results. This resultant array is encoded to the JSON format which will send the response of the DataTables AJAX script.
DataTable HTML Code
The following code shows the HTML code for the DataTable. This code contains required CSS and JavaScript library includes on the Head portion. I have added styles to customize the DataTable UI by overriding the library styles. The HTML table tag has an id attribute which is used to initialize the table as a DataTable.
<html>
<head>
<title>Datatables</title>
<link rel="stylesheet" href="vendor/DataTables/datatables.min.css">
<link rel="stylesheet" href="style.css">
<script src="vendor/jquery/jquery-1.11.2.min.js" type="text/javascript"></script>
<script src="vendor/DataTables/datatables.min.js" type="text/javascript"></script>
<style>
body {font-family: calibri;color:#4e7480;}
</style>
</head>
<body>
<div class="container">
<table id="contact-detail" class="display nowrap" cellspacing="0" width="100%">
<thead>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Address</th>
<th>Phone</th>
<th>DOB</th>
</tr>
</thead>
</table>
</div>
</body>
</html>
DataTable Initialization using jQuery
The following code shows the jQuery DataTable initialization by setting the required DataTable property. In this code, the serverSide property is set as true and the server side file path is specified for the ajax property. These two are used to enable the server side processing for the DataTable.
<script>
$(document).ready(function() {
$('#contact-detail').dataTable({
"scrollX": true,
"pagingType": "numbers",
"processing": true,
"serverSide": true,
"ajax": "server.php"
} );
} );
</script>
Server-Side Processing
The following PHP code is adapted from the example given in the DataTable official website. It declares the database configuration details, table name, columns and sends then to the domain class to process the SELECT query.
<?php
$table = 'tbl_contact';
$primaryKey = 'id';
$columns = array(
array( 'db' => 'first_name', 'dt' => 0 ),
array( 'db' => 'last_name', 'dt' => 1 ),
array( 'db' => 'address', 'dt' => 2 ),
array( 'db' => 'phone', 'dt' => 3,),
array( 'db' => 'date_of_birth','dt' => 4,
'formatter' => function( $d, $row ) {
return date( 'd-m-Y', strtotime($d));
}
)
);
$sql_details = array(
'user' => 'root',
'pass' => '',
'db' => 'blog_samples',
'host' => 'localhost'
);
require( 'vendor/datatables/ssp.class.php' );
echo json_encode(
SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);
?>
Hi, I’m Vincy. I help build websites, grow businesses, big and small. If that is what you want, contact me.
I’m currently available for freelance work.