DataTables Server-side Processing with PHP and MySQL

DataTables is a library of jQuery that displays the list records in an HTML table using an intuitive interface. It offers features such as search, pagination, and sort.

In an earlier article, we saw a custom code that allows search and pagination of the records. In addition, DataTables will enable us to limit the number of records displayed on a page.

DataTables extension supports both client-side as well as server-side processing. This article will show you the database results using DataTables server-side processing.

A PHP file calls domain class by sending the table, column, and configuration details to the function. The domain class executes the query and returns an array of results. The JSON format encodes the resultant array and will be sent as a response to the DataTables AJAX program.

Read Also: How to integrate jQuery Fullcalendar with PHP and MySQL

DataTable Defining Code

This code defines HTML code for DataTable. This code requires CSS and JavaScript libraries. The table HTML tag contains an id attribute used to define and assign the table as a DataTable.

<!-- DataTable CSS -->
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/datatables/1.10.21/css/jquery.dataTables.min.css" />

<!-- DataTable Script -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/datatables/1.10.21/js/jquery.dataTables.min.js" ></script>

Table Details:

<table id="table_list" class="dataTable" width="100%" cellspacing="0">
   <thead>
      <tr>
         <th>S.No.</th>
         <th>Country</th>
         <th>ISO Alpha-2 Code </th>
         <th>ISO Alpha-3 Code </th>
      </tr>
    </thead>
</table>

DataTable Initialize using jQuery script.

This code shows how to set the required DataTable property key features, and jQuery DataTable initialize. In addition, this code sets the serverSide property to true. The server-side file path for the ajax key is also specified. These keys are used for setting the server-side processing of the DataTable.

$(document).ready(function(e){
		$('#table_list').dataTable({
			"bProcessing": true,
         	"serverSide": true,
         	"ajax":{
	            url :"dataList.php",
	            type: "POST",
	            error: function(){
	              $("#error").show();
	            }
          	}
        });
	});

Server-Side Processing script

Below is the PHP code that will provide data set details in JSON format. This will retrieve the data from MySql using SELECT with ORDER AND LIMIT keys. In addition, it includes column names and their respective values in an array.

<?php 
// DB table to use 
$table = 'worldcountryList';

// Table's primary key 
$primaryKey = 'id'; 

// An array of columns from the database that should be read and returned to DataTables. 
// The 'db' parameter is the database column name, and the 'dt parameter the DataTables column ID. 
// In this example, object parameter names
$columns = array( array('db' => 'id', 'dt' => 0),
	array('db' => 'name', 'dt' => 1),
	array('db' => 'alpha_2',  'dt' => 2),
	array('db' => 'alpha_3',   'dt' => 3)
);
// SQL server connection information
$sql_details = array(
	'user' => 'root',
	'pass' => '******',
	'db'   => 'database',
	'host' => 'localhost'
);

// Helper functions for building a DataTables server-side processing SQL query
require('ssp.class.php');
echo json_encode(SSP::simple($_POST, $sql_details, $table, $primaryKey, $columns));

Database table file attached in demo download you can simply import it in your database and update your database credentials above and run script.

I hope you find it helpfull article update by comments.