DataTables AJAX Pagination with Search and Sort – PHP

DataTables is a jQuery plugin that makes it easier to add pagination on the webpage.

Just need to add records list then it will auto-adjust data and create pagination with search and sort feature.

There are options available to implement AJAX pagination.

In this tutorial, I show how you can implement AJAX pagination in DataTables with PHP.

DataTables AJAX Pagination with Search and Sort - PHP


  1. Table structure
  2. Configuration
  3. Download & Include
  4. HTML
  5. Script
  6. PHP
  7. Demo
  8. Conclusion

1. Table structure

Create employee table.

CREATE TABLE `employee` (
  `emp_name` varchar(80) NOT NULL, 
  `salary` varchar(20) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `city` varchar(80) NOT NULL,
  `email` varchar(80) NOT NULL

2. Configuration

Create a config.php for the database connection.

Completed Code


$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */

$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
  die("Connection failed: " . mysqli_connect_error());

3. Download & Include

  • Download Datatables from here.
  • Include datatables.min.css and datatables.min.js in <head> section and also include the jQuery Library.
  • You can also use CDN.
<!-- Datatable CSS -->
<link href='//' rel='stylesheet' type='text/css'>

<!-- jQuery Library -->
<script src=""></script>

<!-- Datatable JS -->
<script src="//"></script>


Create a <table id='empTable' class='display dataTable'> and add column name in <thead>.

Completed Code

<!-- Datatable CSS -->
<link href='' rel='stylesheet' type='text/css'>

<!-- jQuery Library -->
<script src=""></script>

<!-- Datatable JS -->
<script src=""></script>

<!-- Table -->
<table id='empTable' class='display dataTable'>

      <th>Employee name</th>


5. Script

Initialize DataTable on <table id='empTable'>.

Within the method pass options to enable server-side processing and send AJAX post request to ajaxfile.php.

In the columns option pass field names that get read when AJAX successfully callback.

Completed Code

      'processing': true,
      'serverSide': true,
      'serverMethod': 'post',
      'ajax': {
      'columns': [
         { data: 'emp_name' },
         { data: 'email' },
         { data: 'gender' },
         { data: 'salary' },
         { data: 'city' },

6. PHP

Create a new ajaxfile.php.

Read the $_POST values and store in variables that are passed by DataTable during AJAX request – draw, start, length, order,columnIndex, column name, order, and search.

Prepare search query if $searchValue is not empty.

Count the total number of records in the employee table.

Count the total number of records with the Search filter from the employee table.

Both the count returns same value on the first time. The difference is when the search value from DataTable then the record count with filter will use to show – number of filtered record from total records in DataTable.

Fetch records from employee table.

Loop on the records and initialize $data Array with an associative array. In the Array, the key will the same as defined in columns option during DataTable initialization.

Prepare $response An array that has a draw, iTotalRecords, iTotalDisplayRecords, and aaData keys.

Return $response JSON format.

Completed Code

## Database configuration
include 'config.php';

## Read value
$draw = $_POST['draw'];
$row = $_POST['start'];
$rowperpage = $_POST['length']; // Rows display per page
$columnIndex = $_POST['order'][0]['column']; // Column index
$columnName = $_POST['columns'][$columnIndex]['data']; // Column name
$columnSortOrder = $_POST['order'][0]['dir']; // asc or desc
$searchValue = mysqli_real_escape_string($con,$_POST['search']['value']); // Search value

## Search 
$searchQuery = " ";
if($searchValue != ''){
   $searchQuery = " and (emp_name like '%".$searchValue."%' or 
        email like '%".$searchValue."%' or 
        city like'%".$searchValue."%' ) ";

## Total number of records without filtering
$sel = mysqli_query($con,"select count(*) as allcount from employee");
$records = mysqli_fetch_assoc($sel);
$totalRecords = $records['allcount'];

## Total number of record with filtering
$sel = mysqli_query($con,"select count(*) as allcount from employee WHERE 1 ".$searchQuery);
$records = mysqli_fetch_assoc($sel);
$totalRecordwithFilter = $records['allcount'];

## Fetch records
$empQuery = "select * from employee WHERE 1 ".$searchQuery." order by ".$columnName." ".$columnSortOrder." limit ".$row.",".$rowperpage;
$empRecords = mysqli_query($con, $empQuery);
$data = array();

while ($row = mysqli_fetch_assoc($empRecords)) {
   $data[] = array( 

## Response
$response = array(
  "draw" => intval($draw),
  "iTotalRecords" => $totalRecords,
  "iTotalDisplayRecords" => $totalRecordwithFilter,
  "aaData" => $data

echo json_encode($response);

7. Demo

View Demo

8. Conclusion

Make sure that field names should be the same in AJAX response data as defined in columns data during DataTable initialization otherwise field value not be read.

Remove serverMethod option if you want to send GET type AJAX request then you also need to update the AJAX file.

You can also view the PDO version here.

NOTE – If data is not loading and Invalid JSON response message showing then use the browser network tab to debug. Check SQL queries again and return response. If still issue is not resolved then you either comment or mail me your code with SQL file at [email protected] so I can check it.

If you found this tutorial helpful then don't forget to share.
Are you want to get implementation help, or modify or extend the functionality of this script? Submit paid service request.