DataTables AJAX Pagination with Search and Sort – PHP

DataTables is a jQuery plugin which makes 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


Contents

  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` (
  `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
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Configuration

Create a config.php for database connection.

Completed Code

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

  • Download Datatables from here.
  • Include datatables.min.css and datatables.min.js in <head> section and also include 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.3.1/jquery.min.js"></script>

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

4. HTML

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

Completed Code

<!-- 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.3.1/jquery.min.js"></script>

<!-- Datatable JS -->
<script src="//cdn.datatables.net/1.10.19/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. 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 which 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

Create a new ajaxfile.php.

Read the $_POST values and store in variables which 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 Array which has a draw, iTotalRecords, iTotalDisplayRecords, and aaData keys.

Return JSON response.

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 = $_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" => $totalRecordwithFilter,
  "iTotalDisplayRecords" => $totalRecords,
  "aaData" => $data
);

echo json_encode($response);

7. Demo

View in the new tab.


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.

You can also view PDO version here.

If you found this tutorial helpful then don't forget to share.
Spread the love
  • 1
  •  
  •  
  • 1
  •  

22 Comments

  1. Denis said:

    Thank you for the post! It’s the only way I could make AJAX pagination work in DataTables.

    October 25, 2018
    Reply
  2. zaib tabs said:

    Beautiful and handcrafted script. Yogesh wonderful man.

    December 24, 2018
    Reply
      • zaib tabs said:

        Can you teach online on weekends please?

        December 27, 2018
        Reply
  3. zaib tabs said:

    I am in process of complying to PDO, would please advise how i should be able to convert the Fetch records $data = array(); using bindparam or bindvalue for pdo statement.

    December 27, 2018
    Reply
    • Yogesh Singh said:

      Hi zaib,
      If you want return response from AJAX in Array format then simply loop on the fetch records and initialize $data Array with key and values.

      December 27, 2018
      Reply
  4. zaib tabs said:

    All is working,
    except this line, which i would like to replace with
    stmt = con->prepare(select * from employees WHERE 1 ? order by ? ? limit ?, ?);

    than bind the values.

    December 27, 2018
    Reply
    • Yogesh Singh said:

      You can do like this –

      $order = “name”;
      $sort = “asc”;
      $stmt = $pdo->prepare(“Select * from employees WHERE active=:active order by “.$order.” “.$sort.” limit :limit, :offset”);

      $stmt->bindValue(‘:active’, 1, PDO::PARAM_INT);
      $stmt->bindValue(‘:limit’, 0, PDO::PARAM_INT);
      $stmt->bindValue(‘:offset’, 10, PDO::PARAM_INT);

      $stmt->execute();

      December 27, 2018
      Reply
  5. zaib tabs said:

    Hi Yogesh,

    Would you be able to post your original query using PDO and email me please.

    if i try i am getting errors. and I am unable to post my query.

    The website is not letting us to type the php/pdo code in the reply section of this website

    December 27, 2018
    Reply
  6. zaib tabs said:

    and If you type ‘ single quote or double quote at the beginning, i am getting json error. Please can you advise what can we do to escape or do nothing when ‘ ” are typed.

    secondly, would you be able to convert your original server side using PDO please?

    December 27, 2018
    Reply
  7. zaib tabs said:

    Hi Yogesh,
    Email sent.
    Please have a look at advise.

    December 28, 2018
    Reply
  8. Gilnei Moraes said:

    I get an “invalid JSON response” when tested with +10,000 rows.
    With few records works fine.
    Any clue?

    March 29, 2019
    Reply
    • Yogesh Singh said:

      Hi Gilnei,
      If Datatable works fine with few records then there is some issue with your data which you are returning. You can debug it using browser console.

      March 29, 2019
      Reply
  9. Bhupendra Pratap Singh said:

    Very Nice Article , one of the best pagination code on internet,thanks

    March 29, 2019
    Reply
  10. hrishikesh said:

    hi sir ,
    Thanks this help me a lot in my project.
    but i am having a small problem with this whin i retrive large data i mean data above 5000+ it takes a lot of time to display in the web page . is their any solution on this,
    please help.
    And Thanks a lot for this Content.

    April 3, 2019
    Reply
    • Yogesh Singh said:

      If you implemented it properly then it will not take much time to load data. It may be because of your SQL query which used for fetching data.

      April 3, 2019
      Reply
  11. Ravi Kant said:

    Very Helpful Tutorial

    April 16, 2019
    Reply
  12. Nevy said:

    Hy Yogesh hell of script.. I Just want to ask that can we customise the pagination style in datatables???

    April 20, 2019
    Reply

Leave a Reply

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