Datatable AJAX pagination with PHP and PDO

Last updated on November 15, 2022 by Yogesh Singh

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

Demo Download

Table structure
Configuration
Download & Include
HTML
Script – Initialize DataTables
PHP – Return DataTables Data
Demo
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 DataTables

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 – Return DataTables Data

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.

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

François Beerten

February 20, 2019 at 11:04 am

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
mofasa

July 27, 2019 at 8:13 pm

on your file ajaxfile.php

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

is $email should be $searchValue

??
Reply
François Beerten

July 28, 2019 at 8:59 am

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
François Beerten

July 29, 2019 at 7:37 am

Sorry, but can you tell me how to send an attachment?
Reply
Simon Hans

August 30, 2019 at 1:34 am

Thank You for this, I restructured it into OOP to fit in my code
Reply
Raj

August 31, 2019 at 1:18 pm

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
Afzal

October 18, 2019 at 1:58 pm

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
Harry

November 27, 2019 at 4:49 pm

Best method since it return JSON data. Thank you very much. Your technique is awesome.
Reply
Md sazib uddin

December 19, 2019 at 8:58 am

How I use the button (like edit, delete) inside the data table?
Reply
Luciano

February 19, 2020 at 10:51 am

How to create a link within the datatable?
Reply
Vimuth

February 28, 2020 at 7:51 am

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
Sazib

March 16, 2020 at 8:14 pm

Thanks for the great tuts. how can I make it order by desc ?
Reply
Aramis Serafim

April 7, 2020 at 8:27 am

gentlemen, if the mysql table is empty, the search will be processed. How to give the message that the search resulted in 0?
Reply
Ska

May 13, 2020 at 11:56 am

DataTables warning: table id=user_data – Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

Can you help me please ?
Reply
- Ska
  
  May 13, 2020 at 12:07 pm
  
  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
Rudi Kang

January 9, 2021 at 6:06 am

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
Yaz

August 29, 2021 at 6:23 am

Thankyou so so much!
Reply
David

October 8, 2021 at 1:37 pm

Thank you very much Yogesh.
Your tutorial is great.
Reply