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.

View Demo

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.

datatables server-side with php

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 )
);
?>

View DemoDownload

Leave a Reply

Your email address will not be published. Required fields are marked *

↑ Back to Top

Share this page