DataTables Column Filtering with Server-side Processing using PHP

DataTables is a JavaScript library used to enhance the functionality and add advanced features to HTML tables. DataTables plugin converts a basic HTML table to an advanced feature-rich table instantly. There are various advanced features are available with DataTables, and server-side processing is one of them. DataTables server-side processing enables you to load a large number of data sets from the database. By setting the server-side option the search, pagination, and column sorting operations will be handled with the server-side script and database.

Column filtering is a very useful option to help the user find specific records in a large number of data. DataTables library provides an easy option to add search and filtering features to each column of the data table. You can apply the column filtering functionality in DataTables with Server-side Processing. In this tutorial, we will show you how to integrate column filtering in DataTables Server-side Processing with PHP.

In the example script, we will list dynamic data in DataTables with Server-side processing and add column filtering using PHP.

• Fetch and list data from the MySQL database using DataTables Server-side Processing.
• Place the column filter option at the header of the table.
• Add input to each column for search and filter records by column value.

Before getting started to integrate individual column filters in DataTables with PHP, take a look at the file structure.

datatables_column_filtering_with_php/
├── index.html
├── fetchData.php
├── ssp.class.php
├── DataTables/
│   ├── datatables.min.css
│   └── datatables.min.js
└── js/
    └── jquery.min.js

Create Database Table

A table is required in the database to display records in the DataTables dynamically. The following SQL creates a members table with some basic fields in the MySQL database.

CREATE TABLE `members` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `first_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
 `last_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
 `email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `gender` enum('Male','Female') COLLATE utf8_unicode_ci NOT NULL,
 `country` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `created` datetime NOT NULL,
 `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1=Active | 0=Inactive',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DataTables Column Filtering with Server-side Processing (index.html)

On page load, the DataTables class is initialized, and dynamic data is fetched from the server side and listed in an HTML table with search input in each column.

Create HTML Table:
Define the HTML table element and specify an ID (memListTable) to attach DataTables.

<table id="memListTable" class="display" style="width:100%">
    <thead>
        <tr>
            <th>First name</th>
            <th>Last name</th>
            <th>Email</th>
            <th>Gender</th>
            <th>Country</th>
            <th>Created</th>
            <th>Status</th>
        </tr>
    </thead>
    <tfoot>
        <tr>
            <th>First name</th>
            <th>Last name</th>
            <th>Email</th>
            <th>Gender</th>
            <th>Country</th>
            <th>Created</th>
            <th>Status</th>
        </tr>
    </tfoot>
</table>

jQuery Library:
Include the jQuery library that helps to initialize DataTables with column filter inputs.

<script src="js/jquery.min.js"></script>

DataTables Library:
Include the DataTables CSS and JS library files.

<link rel="stylesheet" type="text/css" href="DataTables/datatables.min.css"/>
<script type="text/javascript" src="DataTables/datatables.min.js"></script>

Attach DataTables Plugin to HTML Table:

• Create a clone of the thead tr and append it to the thead section of the table.
• Initialize the DataTables API class using the DataTable() method and configure the table object.
• Enable the server-side processing by doing the following settings.
  • Set the processing option to true.
  • Set the serverSide option to true.
  • Specify the server-side script URL (fetchData.php) in the ajax option.
• Use the initComplete() method to define the column filtering operations.
  • Set the header cell to contain the input elements for the search and filter column.
  • Disable the search option for the Created (index count 5) and Status (index count 6) columns.
  • Post the keyword to the server-side script along with the specific column reference on every search input keypress.
  • Render filtered table data based on the search value using the draw() method of the DataTables object.

<script>
$(document).ready(function(){
    $('#memListTable thead tr')
    .clone(true)
    .addClass('filters')
    .appendTo('#memListTable thead');

    // Initialize DataTables API object and configure table
    var table = $('#memListTable').DataTable({
        orderCellsTop: true,
        fixedHeader: true,
        processing: true,
        serverSide: true,
        ajax: "fetchData.php",
        initComplete: function () {
            var api = this.api();

            // For each column
            api
            .columns()
            .eq(0)
            .each(function (colIdx) {
                // Set the header cell to contain the input element
                var cell = $('.filters th').eq(
                    $(api.column(colIdx).header()).index()
                );
                var title = $(cell).text();
                
                if(colIdx != 5 && colIdx != 6){
                    $(cell).html('<input type="text" class="dt-input" placeholder="' + title + '" />');
                }else{
                    $(cell).html('');
                }

                // On every keypress in this input
                $(
                    'input',
                    $('.filters th').eq($(api.column(colIdx).header()).index())
                )
                .on('keyup change', function (e) {
                    api
                    .column(colIdx)
                    .search(
                        this.value
                    )
                    .draw();
                });
            });
        }
    });
});    
</script>

By default, the data is listed in ascending order of the first column (first_name). You can change the column to set the listing order.

• The below example code sets the Created column (index count 5) as listing order and lists in descending order. (This means the newest records will come first)

order: {
    idx: 5,
    dir: 'desc'
}

Server-side Script to Fetch DataTables Data (fetchData.php)

The fetchData.php file performs server-side processing with column filter operations.

• Define database credentials (host, username, password, database name) in the $dbDetails variable.
• Define DB table name in $table variable and PRIMARY KEY in $primaryKey variable.
• To make the SQL query-building process more accessible, we will use the SSP class (ssp.class.php).
• The simple() function of the SSP class helps to fetch the member’s data from the database based on the mentioned columns using PHP and MySQL.
• The formatter parameter is used to modify the default format of the data returns for the column.

<?php 
 
// Database connection info 
$dbDetails = array( 
    'host' => 'localhost', 
    'user' => 'root', 
    'pass' => 'root', 
    'db'   => 'codexworld_db' 
); 
 
// DB table to use 
$table = 'members'; 
 
// Table's primary key 
$primaryKey = 'id'; 
 
// Array of database columns which should be read and sent back to DataTables. 
// The `db` parameter represents the column name in the database.  
// The `dt` parameter represents the DataTables column identifier. 
$columns = array( 
    array( 'db' => 'first_name', 'dt' => 0 ), 
    array( 'db' => 'last_name',  'dt' => 1 ), 
    array( 'db' => 'email',      'dt' => 2 ), 
    array( 'db' => 'gender',     'dt' => 3 ), 
    array( 'db' => 'country',    'dt' => 4 ), 
    array( 
        'db'        => 'created', 
        'dt'        => 5, 
        'formatter' => function( $d, $row ) { 
            return date( 'jS M Y', strtotime($d)); 
        } 
    ), 
    array( 
        'db'        => 'status', 
        'dt'        => 6, 
        'formatter' => function( $d, $row ) { 
            return ($d == 1)?'Active':'Inactive'; 
        } 
    ), 
); 
 
// Include SQL query processing class 
require 'ssp.class.php'; 
 
// Output data as json format 
echo json_encode( 
    SSP::simple( $_GET, $dbDetails, $table, $primaryKey, $columns ) 
); 
 
?>

SSP Library

The SSP class handles the database-related operations. It contains some helper functions to build SQL queries for DataTables server-side processing with custom links or buttons. You can see the code of the SSP library here.

Note that: This library and all the required files are included in the source code, you don’t need to download them separately.

Add Custom Search and Filter to DataTables Server-side Processing with PHP

Conclusion

Our example code helps to display records in tabular format with advanced features using the DataTables library. Column search and filter functionality take an important role in enhancing table with DataTables. DataTables server-side processing feature can be used to add search options in the table columns and filter records from the database using PHP and MySQL. You can easily enhance the DataTables column filtering functionality with server-side processing.