Datatable AJAX pagination with PHP and PDO

With Datatable you can easily implement pagination on the webpage in less time.

It gives all the necessary features which require in pagination like – searching, sorting, change the number of records display.

You only need to manage the AJAX file.

In this tutorial, I show how you can add Datatable pagination with PHP and PDO.

Datatable AJAX pagination with PHP and PDO


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 a database connection.

Completed Code

<?php
$server = "localhost";
$username = "root";
$password = "";
$dbname = "tutorial";

// Create connection
try{
   $conn = new PDO("mysql:host=$server;dbname=$dbname","$username","$password");
   $conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e){
   die('Unable to connect with the database');
}

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='//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.6.0/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.5.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'>.

Enable serverside processing and set the POST method using options. Send AJAX 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

Create ajaxfile.php file.

Read Datatable POST values.

Set up a search query if $searchValue is not empty.

Count total records with and without a search filter.

Fetch all records from employee table and initialize $data Array with values.

Return $response Array in JSON format.

Completed Code

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

$searchArray = array();

## Search 
$searchQuery = " ";
if($searchValue != ''){
   $searchQuery = " AND (emp_name LIKE :emp_name or 
        email LIKE :email OR 
        city LIKE :city ) ";
   $searchArray = array( 
        'emp_name'=>"%$searchValue%", 
        'email'=>"%$searchValue%",
        'city'=>"%$searchValue%"
   );
}

## Total number of records without filtering
$stmt = $conn->prepare("SELECT COUNT(*) AS allcount FROM employee ");
$stmt->execute();
$records = $stmt->fetch();
$totalRecords = $records['allcount'];

## Total number of records with filtering
$stmt = $conn->prepare("SELECT COUNT(*) AS allcount FROM employee WHERE 1 ".$searchQuery);
$stmt->execute($searchArray);
$records = $stmt->fetch();
$totalRecordwithFilter = $records['allcount'];

## Fetch records
$stmt = $conn->prepare("SELECT * FROM employee WHERE 1 ".$searchQuery." ORDER BY ".$columnName." ".$columnSortOrder." LIMIT :limit,:offset");

// Bind values
foreach($searchArray as $key=>$search){
   $stmt->bindValue(':'.$key, $search,PDO::PARAM_STR);
}

$stmt->bindValue(':limit', (int)$row, PDO::PARAM_INT);
$stmt->bindValue(':offset', (int)$rowperpage, PDO::PARAM_INT);
$stmt->execute();
$empRecords = $stmt->fetchAll();

$data = array();

foreach($empRecords as $row){
   $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

Return Array data key name must be the same as specified in columns option while Datatable initializing.

You can also view without PDO version here.

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.

29 thoughts on “Datatable AJAX pagination with PHP and PDO”

  1. Have adjusted your example to my data file with 750 records.
    however, if I want to go to the next page via ‘next’, I sometimes get an error message on some pages! I can see page 1,4,5,6,7,8,10, but on page 2,3,9 I get the error message below.
    ——
    DataTables warning: table id=empTable – Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1
    ———
    any idea why? greetings

    Translated with http://www.DeepL.com/Translator

    Reply
    • Hi François,
      If it is working on some pages but not on some page that means the datatable is working fine but because of some fetched records JSON is not properly generating by Datatable. To check the problem – Inspect the page in the browser and navigate to Network and click on the AJAX file name which you are using. The filename will be in red color if response properly not generated and click on it. This will display the issue.

      Reply
  2. on your file ajaxfile.php

    $searchArray = array(
    ’emp_name’=>”%$searchValue%”,
    ’email’=>’%$email%’,
    ‘city’=>’%$searchValue%’
    );

    is $email should be $searchValue

    ??

    Reply
  3. Hi Yogesh,

    The error occurs when I add a record using special characters, such as: ç , ö, ë, ü etc.
    Tried to view the error message, as you suggested, but don’t see what’s wrong.
    Can I send you a screenprint of the error message as well as of a record with special characters?

    Reply
  4. Hi, I may have missed something, but where are the fields ‘draw’, ‘start’, ‘length’, etc. defined? My script is complaining that these variables are undefined.

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

    Thank You,

    Raj

    Reply
    • Hi Raj,
      Check AJAX request in browser network tab. Here, you see what method datatable is using ‘GET’ or ‘POST’ and what data is passing. It helps you to find the problem.

      Reply
  5. Hi sir,
    i want to used above code with two tables with joins but i am not complete i have several errors please suggest me how to do..
    erro is “>Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[23000]: Integrity constraint violation: 1052 Column ‘lid’ in where clause is ambiguous’ in C:\xampp\htdocs\AMS_Myntra\storemanager\ajaxInventry.php:54

    $draw = $_POST[‘draw’];
    $row = $_POST[‘start’];
    $rowperpage = $_POST[‘length’];
    $columnIndex = $_POST[‘order’][0][‘column’];
    $columnName = $_POST[‘columns’][$columnIndex][‘data’];
    $columnSortOrder = $_POST[‘order’][0][‘dir’];
    $searchValue = $_POST[‘search’][‘value’];
    ##custom field
    $searchByDepartment = $_POST[‘searchByDepartment’];
    $searchByCategory = $_POST[‘searchByCategory’];
    $searchByType = $_POST[‘searchByType’];
    $searchArray = array();
    ## Search
    $searchQuery = ” “;
    if ($searchByDepartment != ”) {
    $searchQuery .= ” and (did='” . $searchByDepartment . “‘) “;
    }
    if ($searchByCategory != ”) {
    $searchQuery .= ” and (acId='” . $searchByCategory . “‘) “;
    }
    if ($searchByType != ”) {
    $searchQuery .= ” and (acId='” . $searchByType . “‘) “;
    }
    if ($searchValue != ”) {
    $searchQuery = ” AND (oid LIKE :oid or
    acId LIKE :acId OR
    atId LIKE :atId OR
    assDesc LIKE :assDesc OR
    obQty LIKE :obQty OR
    creation_date LIKE :creation_date ) “;
    $searchArray = array(
    ‘oid’ => “%$searchValue%”,
    ‘acId’ => “%$searchValue%”,
    ‘atId’ => “%$searchValue%”,
    ‘assDesc’ => “%$searchValue%”,
    ‘obQty’ => “%$searchValue%”,
    ‘creation_date’ => “%$searchValue%”
    );
    }
    ## Total number of records without filtering
    $stmt = $conn->prepare(“SELECT COUNT(*) AS allcount FROM tbl_assetonboarding as `ob` join tbl_assetfulfillment as `fl` on `ob`.`oid`=`fl`.`oid` WHERE `ob`.`Inwards`=1 and `ob`.`lid`='” . $_SESSION[‘user’][‘location’] . “‘ “);
    $stmt->execute();
    $records = $stmt->fetch();
    $totalRecords = $records[‘allcount’];
    ## Total number of records with filtering
    $stmt = $conn->prepare(“SELECT COUNT(*) AS allcount FROM tbl_assetonboarding as `ob` join tbl_assetfulfillment as `fl` on `ob`.`oid`=`fl`.`oid`WHERE `ob`.`Inwards`=1 and `ob`.`lid`='” . $_SESSION[‘user’][‘location’] . “‘ ” . $searchQuery);
    $stmt->execute($searchArray);
    $records = $stmt->fetch();
    $totalRecordwithFilter = $records[‘allcount’];
    ## Fetch records
    $stmt = $conn->prepare(“SELECT `ob`.`oid`,`ob`.`did`,`ob`.`acId`,`ob`.`atId`,`ob`.`assDesc`,`ob`.`obQty`,`ob`.`poPath`,`ob`.`sImgPath`,`ob`.`creation_date` FROM tbl_assetonboarding as `ob` join tbl_assetfulfillment as `fl` on `ob`.`oid`=`fl`.`oid` WHERE `ob`.`Inwards`=1 and `ob`.`lid`='” . $_SESSION[‘user’][‘location’] . “‘ ” . $searchQuery . ” ORDER BY ” . $columnName . ” ” . $columnSortOrder . ” LIMIT:limit,:offset”);
    // Bind values
    foreach ($searchArray as $key => $search) {
    $stmt->bindValue(‘:’ . $key, $search, PDO::PARAM_STR);
    }
    $stmt->bindValue(‘:limit’, (int)$row, PDO::PARAM_INT);
    $stmt->bindValue(‘:offset’, (int)$rowperpage, PDO::PARAM_INT);
    $stmt->execute();
    $empRecords = $stmt->fetchAll();
    $data = array();
    $i = 1;
    $catList = ”;
    $tpList = ”;
    $dpList = ”;
    foreach ($empRecords as $row) {
    foreach ($categoryList as $key => $value) {
    if ($value[‘cid’] == $row[‘acId’]) {
    $catList[$row[‘acId’]] = $value[‘cName’];
    }
    }
    foreach ($typeList as $key => $value) {
    if ($value[‘atId’] == $row[‘atId’]) {
    $tpList[$row[‘atId’]] = $value[‘assName’];
    }
    }
    foreach ($deptList as $key => $value) {
    if ($value[‘did’] == $row[‘did’]) {
    $dpList[$row[‘did’]] = $value[‘deptName’];
    }
    }
    $data[] = array(
    “sno” => $i,
    “did” => $dpList[$row[‘did’]],
    “acId” => $catList[$row[‘acId’]],
    “atId” => $tpList[$row[‘atId’]],
    “assDesc” => $row[‘assDesc’],
    “obQty” => $row[‘obQty’],
    “photos” => ‘‘,
    “creation_date” => $row[‘creation_date’],
    “action” => ‘Asset Transfer‘,
    “bcNo”=>’dd’,
    );
    $i++;
    }
    ## Response
    $response = array(
    “draw” => intval($draw) ,
    “iTotalRecords” => $totalRecords,
    “iTotalDisplayRecords” => $totalRecordwithFilter,
    “aaData” => $data
    );
    echo json_encode($response);

    Reply
  6. This is a wonderful tutorial but I think LIMIT and OFFSSET are wrong.

    $stmt->bindValue(‘:limit’, (int)$row, PDO::PARAM_INT);
    $stmt->bindValue(‘:offset’, (int)$rowperpage, PDO::PARAM_INT);

    should be,

    $stmt->bindValue(‘:limit’, (int)$rowperpage, PDO::PARAM_INT);
    $stmt->bindValue(‘:offset’, (int)$row, PDO::PARAM_INT);

    Really sorry if I’m wrong and thanks again for the wonderful tutorial

    Reply
    • Hi Sazib,
      If you set default ordering to ‘desc’ then you can use ‘order’: [[2, ‘desc’]], while datatable initialization. Here, 2 is an index position of the column that needs to sort. If you want to stop the user from sort the column then use columnDefs to disable sorting on the columns-

      "columnDefs": [
            { "targets": [0,1,2,3], "orderable": false }
        ]
      Reply
  7. gentlemen, if the mysql table is empty, the search will be processed. How to give the message that the search resulted in 0?

    Reply
    • Hi Aramis,
      If you want to edit the message that displays when search value not found then use “oLanguage” option while datatable initialization.
      “oLanguage”: {
      “sZeroRecords”: “No records available”,
      }

      Edit the value of “sZeroRecords”.

      Reply
    • Notice: Undefined variable: conn in C:\Users\Skander\Desktop\xampp\htdocs\Burgercode\admin\ajaxfile.php on line 30

      Fatal error: Uncaught Error: Call to a member function prepare() on null in C:\Users\Skander\Desktop\xampp\htdocs\Burgercode\admin\ajaxfile.php:30
      Stack trace:
      #0 {main}
      thrown in C:\Users\Skander\Desktop\xampp\htdocs\Burgercode\admin\ajaxfile.php on line 30

      Reply
  8. Hi Yogesh.

    How do you add a edit or delete function in this Datatable AJAX pagination with PHP and PDO ? so, the table data can be edited or erase.
    I mean that so the table data can be edited or deleted each line.
    is it possible to do that..

    Reply

Leave a Comment