DataTables AJAX Pagination with Search and Sort - PHP

DataTables is a powerful jQuery plugin that offers advanced features for tabular data display on a web page. It has pagination, sorting, and filtering features that make managing large amounts of data simple.

It can help you present your data in a way that is both informative and accessible, whether you’re working with simple spreadsheets or complex datasets.

DataTable will automatically adjust data and create pagination with a search and sort feature after you add a data list to the HTML table and initialize it.

There are options available to implement AJAX pagination.

In this tutorial, I show how you can use DataTables with AJAX to implement pagination with search and sort using PHP.

Demo Download

Table of Content

Create a Table
Database Configuration
Download & Include DataTables Library
HTML Layout for DataTables
jQuery – Configuring DataTables with AJAX
PHP – Fetch DataTables Data
Demo
Conclusion

1. Create a Table

Create employee table.

CREATE TABLE `employee` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `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. Database Configuration

Create a config.php for the database connection.

<?php

$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 DataTables Library

Download Datatables from Official website.
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='//cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css' rel='stylesheet' type='text/css'>

<!-- jQuery Library -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>

<!-- Datatable JS -->
<script src="//cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>

4. HTML Layout for DataTables

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

Completed Code

<!-- Datatable CSS -->
<link href='https://cdn.datatables.net/1.11.5/css/jquery.dataTables.min.css' rel='stylesheet' type='text/css'>

<!-- jQuery Library -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>

<!-- Datatable JS -->
<script src="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js"></script>

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

   <thead>
       <tr>
           <th>Employee name</th>
           <th>Email</th>
           <th>Gender</th>
           <th>Salary</th>
           <th>City</th>
       </tr>
   </thead>

</table>

5. jQuery – Configuring DataTables with AJAX

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

$(document).ready(function(){
   $('#empTable').DataTable({
      'processing': true,
      'serverSide': true,
      'serverMethod': 'post',
      'ajax': {
          'url':'ajaxfile.php'
      },
      'columns': [
         { data: 'emp_name' },
         { data: 'email' },
         { data: 'gender' },
         { data: 'salary' },
         { data: 'city' },
      ]
   });
});

6. PHP – Fetch DataTables Data

Create a 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

<?php
## 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( 
      "emp_name"=>$row['emp_name'],
      "email"=>$row['email'],
      "gender"=>$row['gender'],
      "salary"=>$row['salary'],
      "city"=>$row['city']
   );
}

## 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 of this tutorial.

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 makitweb@gmail.com so I can check it.

If you found this tutorial helpful then don't forget to share.