DataTables Server-side Processing using PHP with MySQL
by Vincy. Last modified on July 5th, 2023.
DataTables is a jQuery library that displays the list of records in an HTML table with an intuitive interface. It includes search, pagination, sort, filter, and more features.
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 displayed per page.
DataTables extension provides both client-side and server-side processing. In this article, we will 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 configuration details. The domain class executes the query to read requested columns and returns an 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 the required CSS and JavaScript library in 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 that initializes 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. This code sets the serverSide property as ‘true,’ and the server-side file path is specified for the Ajax property. These two are used to enable server-side processing for 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 on the DataTable official website. It declares the database configuration details, table name, and columns and sends them 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.