Datatable AJAX pagination with PHP and PDO

Last updated on by

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

Contents

  1. Table structure
  2. Configuration
  3. Download & Include
  4. HTML
  5. Script – Initialize DataTables
  6. PHP – Return DataTables Data
  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 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.

© 2016-2024 Makitweb, All rights reserved