DEV Community

Cover image for jQuery Datatables Ajax PHP and MySQL using PDO Example
Code And Deploy
Code And Deploy

Posted on

jQuery Datatables Ajax PHP and MySQL using PDO Example

Originally posted @ https://codeanddeploy.com visit and download the sample code: https://codeanddeploy.com/blog/php/jquery-datatables-ajax-php-and-mysql-using-pdo-example

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;


Enter fullscreen mode Exit fullscreen mode

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();
}


Enter fullscreen mode Exit fullscreen mode

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>


Enter fullscreen mode Exit fullscreen mode

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);

Enter fullscreen mode Exit fullscreen mode




Result:

datatable

I hope this tutorial can help you. Kindly visit here https://codeanddeploy.com/blog/php/jquery-datatables-ajax-php-and-mysql-using-pdo-example if you want to download this code.

Happy coding :)

Top comments (0)