Admin

Software Developer

22nd March, 2024

170

Mastering DataTables: Implementing Column Filtering and Server-Side Processing with PHP

DataTables revolutionizes HTML tables by adding advanced features and functionalities. Among its myriad capabilities, server-side processing stands out, enabling efficient handling of large datasets directly from the database. In this tutorial, we'll focus on integrating column filtering into DataTables with server-side processing using PHP. Let's dive in!

Understanding DataTables Column Filtering

Column filtering empowers users to locate specific records effortlessly within extensive datasets. With DataTables, integrating column filtering becomes a seamless process, particularly when combined with server-side processing.

Setting Up Your Environment

Before we delve into the implementation, let's review 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

Creating a Database Table

First, we need a database table to dynamically display records. Here's a sample SQL to create a members table:

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

Implementing DataTables with Column Filtering and Server-Side Processing

index.html

<!-- HTML Table Structure -->
<table id="memListTable" class="display" style="width:100%">
    <thead>
        <!-- Table Header -->
    </thead>
    <tfoot>
        <!-- Table Footer -->
    </tfoot>
</table>

<!-- jQuery Library -->
<script src="js/jquery.min.js"></script>

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

<!-- Script to Initialize DataTables with Column Filtering -->
<script>
$(document).ready(function(){
    // Clone the table header and append it
    $('#memListTable thead tr')
    .clone(true)
    .addClass('filters')
    .appendTo('#memListTable thead');

    // Initialize DataTables
    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) {
                var cell = $('.filters th').eq($(api.column(colIdx).header()).index());
                var title = $(cell).text();
                
                // Create input elements for filtering
                if(colIdx != 5 && colIdx != 6){
                    $(cell).html('<input type="text" class="dt-input" placeholder="' + title + '" />');
                }else{
                    $(cell).html('');
                }

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

fetchData.php

<?php 
// Database connection info 
$dbDetails = array( 
    'host' => 'localhost', 
    'user' => 'root', 
    'pass' => 'root', 
    'db'   => 'your_database_name' 
); 

// DB table to use 
$table = 'members'; 

// Table's primary key 
$primaryKey = 'id'; 

// Database columns 
$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 SSP class 
require 'ssp.class.php'; 

// Output data in JSON format 
echo json_encode(SSP::simple($_GET, $dbDetails, $table, $primaryKey, $columns));
?>

Conclusion

Incorporating column filtering into DataTables with server-side processing using PHP offers a streamlined approach to managing and presenting large datasets. With our tutorial, you're equipped to enrich your DataTables implementations with powerful filtering capabilities, enhancing user experience and data accessibility.Now, embark on your journey to unlock the full potential of DataTables in your web applications!

Admin

Related Tutorial

Download XAMPP with Multiple PHP Versions: Easy Installation Guide.

A Guide to Types of Arrays in PHP.

How to Use Session Variables in PHP.

How to unlink file in php without exception.

XAMPP version with PHP Version Details.

Popular Tools for Developers

Popular Tools for Project Manager

Popular Tools for Youtubers

Other Tools for Daily Life

About us

Find Online Tools

Quick links

Social links

Other