jQuery Datatables with AJAX in PHP

jQuery DataTables is a jQuery library that extends the HTML tables and adds some cool features like search and filters. Learn how to use jQuery DataTables in PHP with AJAX requests to fetch paginated data.

Demo Download

jQuery DataTables is a jQuery library that enhances the layout and functionality of HTML tables by providing useful built-in features to display large datasets in an organized manner. This post demonstrates how to integrate jQuery DataTables with server-side processing in PHP via AJAX. We are going to create following files for this post:

constants.php: The file containing constants for database connection.
employees.sql: The database table of employee records to display in table.
index.html: The HTML page where the datatable will be displayed.
datatable-ajax.php: The server-side script to prepare paginated data and send a response in JSON format.
javascript.js: The javascript file which will send AJAX request to server-side script and render the table on client side.
style.css: The CSS stylesheet for HTML page.

Add jQuery DataTables with Server-Side Processing in PHP

jQuery DataTables provides easy to implement features to sort, filter and paginate large number of database records. Following are the features that compel the usage jQuery DataTables:

Sorting: Provides sorting feature easy to implement on client side.
Search: Provides the searching functionality to search for specific data in database table.
Pagination: One other cool feature to paginate large number of records in chunks and display them as HTML table in easy way.
AJAX: It is possible load table rows via AJAX with server-side processing.
Responsiveness: Supports responsive datatables and adapts table layout automatically for different screen sizes.

In this post we are going to integrate responsive jQuery DataTables with server-side processing using AJAX requests. We will use an employees table in database and display employees in tabular format using server-side processing. Steps to add AJAX datatable in PHP are:

Step 1: Add Employees Table to Database

We need some records in database in order to display them as paginated record set. Run the following query to import dummy employee data for datatable.

employees.sql

DROP TABLE IF EXISTS `employees`;
CREATE TABLE IF NOT EXISTS `employees` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `full_name` varchar(255) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `gender` tinytext DEFAULT NULL,
  `designation` varchar(100)  DEFAULT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `employees` (`id`, `full_name`, `email`, `gender`, `designation`) VALUES
(1, 'Dr. Humberto Stracke', '[email protected]', 'Male', 'Social Sciences Teacher'),
(2, 'Jaren Emard I', '[email protected]', 'Male', 'Set and Exhibit Designer'),
(3, 'Furman Marvin', '[email protected]', 'Male', 'Mapping Technician'),
(4, 'Miss Shana Wiegand', '[email protected]', 'Female', 'Dietetic Technician'),
(5, 'Gregorio Hayes', '[email protected]', 'Male', 'Railroad Switch Operator'),
(6, 'Caesar Jenkins', '[email protected]', 'Male', 'Painting Machine Operator'),
(7, 'Graciela Schaefer', '[email protected]', 'Female', 'Medical Assistant'),
(8, 'Johnpaul Kuvalis', '[email protected]', 'Male', 'House Cleaner'),
(9, 'Amos Thiel', '[email protected]', 'Male', 'General Farmworker'),
(10, 'Nona Kovacek', '[email protected]', 'Female', 'Precision Mold and Pattern Caster');

Step 2: Define Database Constants

We will need to connect to database and retrieve database records. So we define following constants for database connection used in server-side processing.

constants.php

<?php
define('DB_HOST', 'DATABASE_HOST');  // Your database host
define('DB_NAME', 'DATABASE_NAME');  // Your database name
define('DB_USER', 'DATABASE_USERNAME');  // Username for database
define('DB_PASSWORD', 'DATABASE_PASSWORD'); // Password for database

Step 3: Add an HTML Table

We also need an interface where user can see the initial database records in tabular format and can filter/search records. The table must have unique id selector to initialize datatable in JavaScript.

index.html

<!DOCTYPE html>
<html>
    <head>
        <title>jQuery DataTables with AJAX in PHP - Demo</title>
        <meta content="text/html; charset=UTF-8" http-equiv="Content-Type"/>
        <meta content="width=device-width, initial-scale=1, maximum-scale=1" name="viewport" />
        <link rel="stylesheet" href="bootstrap/bootstrap.min.css" />
        <link rel="stylesheet" href="datatables/datatables.min.css" />
        <link rel="stylesheet" href="css/style.css" />
        <script src="js/jquery-3.1.1.min.js"></script>
    </head>
    <body>
        <section class="section py-4">
            <div class="container">
                <table id="employees-table" class="employees-table row-border compact table bg-white">
                    <thead>
                        <tr>
                            <th>#</th>
                            <th>Full Name</th>
                            <th>Email</th>
                            <th>Gender</th>
                            <th>Designation</th>
                            <th>Action</th>
                        </tr>
                    </thead>
                </table>
            </div>
        </section>
        <footer>
            <script src="datatables/datatables.min.js"></script>
            <script src="js/javascript.js"></script>
        </footer>
    </body>
</html>

Step 4: Server-Side DataTable Processing

Now on the server-side we need a script handle the initial request to display records from database table and also handle searching and filtering. Create a file datatable-ajax.php for server-side processing that fetches the records from database based on provided parameters and return JSON format response with data. Steps to follow for server side datatable processing are:

Define initial query for employees table and fetch all records.
Add a WHERE clause and check if user passed anything to search fields.
Append WHERE clause for each column in database table that should be searched for given value.
Check if order/sort field was passed in request. Add graceful handle of order direction using ternary operator in case user passed anything other than ASC/DESC order.
Check if length parameter is passed in request for pagination. Paginate the database records with LIMIT and OFFSET clause in query.
Establish a database connection.
Get total number of rows in table without any filter and limit clause.
Get total number of rows with filter but without limit clause. This is the filtered records returned in response.
Define an empty array variable $rows for rows.
Loop through each employee fetched from database and add data to indexed array inside loop. You can modify the data as per your need.
Declare a new empty array variable $records and add data rows we prepared, the number of filtered records and number of total records.
Finally return the $records array as JSON response.

datatable-ajax.php

<?php
include 'constants.php';

$sql = 'SELECT * FROM employees';

$post = filter_input_array(INPUT_POST);

$where = '1=1';

$param_types = '';

$param_values = [];

if (!empty($post['search']['value'])) {
    $where .= ' AND (full_name LIKE ? OR email LIKE ? OR gender LIKE ? OR designation LIKE ?)';

    $param_types .= str_repeat('s', 4);

    $param_values = array_merge($param_values, array_fill(0, 4, '%' . $post['search']['value'] . '%'));
}

$sql .= sprintf(' WHERE %s', $where);

$allowed_columns = ['id', 'full_name', 'email', 'gender', 'designation'];

if (!empty($post['order'][0]['name'])
    && in_array($post['order'][0]['name'], $allowed_columns, true)) {
    $sql .= sprintf(' ORDER BY %s %s',
        $post['order'][0]['name'],
        $post['order'][0]['dir'] === 'desc' ? 'DESC' : 'ASC');
} else {
    $sql .= ' ORDER BY id DESC';
}

if ($post['length'] != -1) {
    $sql .= ' LIMIT ?, ?';

    $param_types .= 'ii';

    $param_values[] = intval($post['start']);
    $param_values[] = intval($post['length']);
}

$db_connection = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) or die(mysqli_connect_error());

$stmt = mysqli_prepare($db_connection, $sql);
mysqli_stmt_bind_param($stmt, $param_types, ...$param_values);
mysqli_stmt_execute($stmt);

$result = mysqli_stmt_get_result($stmt);
mysqli_stmt_close($stmt);

$stmt = mysqli_prepare($db_connection, sprintf('SELECT COUNT(*) AS num_rows FROM employees WHERE %s', $where));

if (count($param_values) > 4) {
    mysqli_stmt_bind_param($stmt, substr($param_types, 0, 4), ...array_slice($param_values, 0, 4));
}

mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $filtered_rows);
mysqli_stmt_fetch($stmt);
mysqli_stmt_close($stmt);

$total_rows = mysqli_fetch_object(mysqli_query($db_connection, 'SELECT COUNT(*) AS num_rows FROM employees'))->num_rows;

$rows = [];

while ($employee = mysqli_fetch_assoc($result)) {
    $row = [];

    $row[] = $employee['id'];
    $row[] = $employee['full_name'];
    $row[] = $employee['email'];
    $row[] = $employee['gender'];
    $row[] = $employee['designation'];
    $row[] = '<a href="#">View</a>';

    $rows[] = $row;
}

$records = [];
$records['data'] = $rows;
$records['recordsTotal'] = intval($total_rows);
$records['recordsFiltered'] = intval($filtered_rows);

echo json_encode($records, JSON_PRETTY_PRINT);

Step 5: Initialize DataTable in JavaScript

We also need to initialize the jQuery DataTable on client-side using JavaScript when the document is in ready state.

Add a serverSide parameter with true value for server side processing.
Add parameter processing for indicator when request is in progress.
Add ajax object with URL to server-side script, type of request and data.
Add columnDefs for column definitions to disable sorting on first and last column.
Add columns object parameter to define column names used in datatable.
Add responsive parameter with breakpoints for responsive layout on different screen sizes.

javascript.js

$(document).ready(function () {
    $("#employees-table").dataTable({
        serverSide: true,
        processing: true,
        order: [[0, 'desc']],
        ajax: {
            url: "datatable-ajax.php",
            type: "POST",
            dataType: "json"
        },
        columnDefs: [
            {
                targets: [0, -1],
                orderable: false,
            },
        ],
        columns: [
            {name: "id"},
            {name: "full_name"},
            {name: "email"},
            {name: "gender"},
            {name: "designation"},
            {name: "action"}
        ],
        responsive: {
            breakpoints: [
                {name: 'xxl', width: Infinity},
                {name: 'xl', width: 1200},
                {name: 'lg', width: 992},
                {name: 'md', width: 768},
                {name: 'sm', width: 576},
            ]
        }
    })
});

Step 6: Add Datatable CSS Styles

Next add some CSS styles for datatable to make a neat and clean user interface.

style.css

*{
    box-sizing: border-box;
}
html,body{
    margin: 0;
    padding: 0;
}
body{
    background-color: #f6f6f6;
    font-family: "Segoe UI", "Roboto", "Helvetica", sans-serif;
    font-size: 15px;
    font-weight: normal;
    font-style: normal;
}
a{
    text-decoration: none;
    color: #3778cd;
}
.container{
    width: 100%;
    max-width: 1140px;
    margin-right: auto;
    margin-left: auto;
    padding-right: 15px;
    padding-left: 15px;
}

We just implemented AJAX datatable in PHP using jQuery's DataTable library. This server-side processing of datatable is encouraged to be used for extremely large number of database records. This is how the datatable will look like: