November 04, 2022

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.

jQuery Datatables with AJAX in PHP
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 the following files for this post:

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

Add jQuery DataTables with Server-Side Processing in PHP

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

  1. Sorting: Provides a sorting feature easy to implement on the client side.
  2. Search: Provides the searching functionality to search for specific data in a database table.
  3. Pagination: One other cool feature is to paginate a large number of records in chunks and display them as an HTML table in an easy way.
  4. AJAX: It is possible to load table rows via AJAX with server-side processing.
  5. 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 employee's table in the database and display employees in tabular format using server-side processing. Steps to add an AJAX datatable in PHP are:

 

Step 1: Add Employees Table to Database

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

employees.sql

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

INSERT INTO `employees` (`id`, `full_name`, `email`, `gender`, `designation`) VALUES
(1, 'Dr. Humberto Stracke', 'murazik.ericka@example.com', 'Male', 'Social Sciences Teacher'),
(2, 'Jaren Emard I', 'aurelie55@example.net', 'Male', 'Set and Exhibit Designer'),
(3, 'Furman Marvin', 'aryanna.barrows@example.org', 'Male', 'Mapping Technician'),
(4, 'Miss Shana Wiegand', 'rosamond11@example.net', 'Female', 'Dietetic Technician'),
(5, 'Gregorio Hayes', 'kariane42@example.com', 'Male', 'Railroad Switch Operator'),
(6, 'Caesar Jenkins', 'gislason.opal@example.com', 'Male', 'Painting Machine Operator'),
(7, 'Graciela Schaefer', 'kraig61@example.org', 'Female', 'Medical Assistant'),
(8, 'Johnpaul Kuvalis', 'bgreenfelder@example.org', 'Male', 'House Cleaner'),
(9, 'Amos Thiel', 'jessica06@example.com', 'Male', 'General Farmworker'),
(10, 'Nona Kovacek', 'marjolaine.kessler@example.org', 'Female', 'Precision Mold and Pattern Caster');
 

Step 2: Define Database Constants

We will need to connect to the database and retrieve database records. So we define the following constants for the 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 users can see the initial database records in tabular format and can filter/search records. The table must have a unique ID selector to initialize the 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 to handle the initial request to display records from the database table and also handle searching and filtering. Create a file datatable-ajax.php for server-side processing that fetches the records from the database based on provided parameters and returns a JSON format response with data. Steps to follow for server-side datatable processing are:

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

  • Add a serverSide parameter with a true value for server-side processing.
  • Add parameter processing for the indicator when the request is in progress.
  • Add an ajax object with URL to server-side script, type of request and data.
  • Add columnDefs for column definitions to disable sorting on the first and last column.
  • Add the columns object parameter to define column names used in the datatable.
  • Add a responsive parameter with breakpoints for a 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 the 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 an AJAX datatable in PHP using jQuery's DataTable library. This server-side processing of the datatable is encouraged to be used for a large number of database records. This is how the datatable will look like:

jQuery Datatables with AJAX in PHP
Home