jQuery Datatables Ajax PHP and MySQL using PDO Example

Table of contents

In this post, I will share an example of how to implement jquery Datatables ajax in PHP and MySQL using PDO. If you have hundreds of thousands of records or even millions of records you don't want to load it at once to your HTML as we do in our previous example because it could slow your server performance. But using ajax you don't need to load all the records to your Datatable because it is paginated and you only show what we need.

Step 1: Create Table with MySQL

We will use the employee's table as an example with basic fields. See below table structure:

CREATE TABLE `employees` (
  `id` int(10) NOT NULL,
  `email` varchar(100) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `address` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `employees`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `employees`
  MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
COMMIT;

Step 2: PHP & MySQL Database Connection

Once your database and table are configured let's create a connection. See below:

<?php

$host     = 'localhost';
$db       = 'demos';
$user     = 'root';
$password = '';

$dsn = "mysql:host=$host;dbname=$db;charset=UTF8";

try {
    $conn = new PDO($dsn, $user, $password, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

} catch (PDOException $e) {
     echo $e->getMessage();
}

As you can see our connection is coded with PDO.

Step 3: Index with Datatables

Now let's configure our HTML with Datatable and Ajax script. See below code:

<!DOCTYPE html>
<html>
<head>
	<meta charset="utf-8">
	<title>JQuery Datatable Example</title>

	<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.3/css/jquery.dataTables.min.css">

	<script type="text/javascript" src="https://code.jquery.com/jquery-3.5.1.js"></script>
	<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
	<script type="text/javascript" src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script>

	<script type="text/javascript">
		$(document).ready(function() {
		    $('#jquery-datatable-ajax-php').DataTable({
		      	'processing': true,
		      	'serverSide': true,
		      	'serverMethod': 'post',
		      	'ajax': {
		          	'url':'datatable.php'
		      	},
		      	'columns': [
		         	{ data: 'email' },
		         	{ data: 'first_name' },
		         	{ data: 'last_name' },
		         	{ data: 'address' }
		      	]
		   });

		} );
	</script>
</head>
<body>

	<div class="container mt-5">
		<h2 style="margin-bottom: 30px;">jQuery Datatable Ajax PHP Example</h2>
		<table id="jquery-datatable-ajax-php" class="display" style="width:100%">
	        <thead>
	            <tr>
	                <th>Email</th>
	                <th>Firstname</th>
	                <th>Lastname</th>
	                <th>Address</th>
	            </tr>
	        </thead>
	    </table>
	</div>
	

</body>
</html>

Step 4: Our Datatables PHP & MySQL Code with PDO

Next, our code for our Datatables Ajax PHP and MySQL code using PDO.

<?php
   // Database Connection
   include 'connection.php';

   // Reading 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 (email LIKE :email OR 
           first_name LIKE :first_name OR
           last_name LIKE :last_name OR 
           address LIKE :address ) ";
      $searchArray = array( 
           'email'=>"%$searchValue%",
           'first_name'=>"%$searchValue%",
           'last_name'=>"%$searchValue%",
           'address'=>"%$searchValue%"
      );
   }

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

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

   // Fetch records
   $stmt = $conn->prepare("SELECT * FROM employees 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(
         "email"=>$row['email'],
         "first_name"=>$row['first_name'],
         "last_name"=>$row['last_name'],
         "address"=>$row['address']
      );
   }

   // Response
   $response = array(
      "draw" => intval($draw),
      "iTotalRecords" => $totalRecords,
      "iTotalDisplayRecords" => $totalRecordwithFilter,
      "aaData" => $data
   );

   echo json_encode($response);

Result:

datatable ajax php using pdo

Download

That's it I hope it helps. Thank you for reading :)