Articles DataTables Server-side Processing with PHP and MySQL by "unknow"

[emailx45]

Add Custom Search and Filter to DataTables Server-side Processing with PHP
"unknow" - 27/May/2020

Спойлер: Content

[SHOWTOGROUPS=4,20]
DataTables is a jQuery plug-in that provides a quick and easy way to list data in a tabular format on the web page. You can add the sorting, filtering, and pagination functionality to the HTML tables with minimal effort. Besides the client-side data, DataTables allow to fetch the server-side data and list them in the HTML table with search and pagination features.

When you are working with a large database that handles a large amount of data, you must consider using the server-side option in DataTables. With server-side processing, the searching, ordering, and paging operations are handled on the server. So, the Database engine can perform various actions on a large data set. If DataTables server-side processing is enabled, all records will not be retrieved from the database at once. Instead, only the specific records and required data are fetched from the database. In this tutorial, we will show you how to enable server-side processing in DataTables with PHP and MySQL.

In the example script, we will fetch the members data from the database and list them with DataTables. The following functionality will be implemented in Datatables Server-side Processing with PHP and MySQL.

• List data from the MySQL database using Datatables Server-side Processing.
• Add search, filter, and pagination features to the HTML table with Datatables.

Create Database Table
To store the member’s information a table is required in the database. The following SQL creates a members table with some basic fields in the MySQL database. The data will be fetched from this table by DataTables server-side processing.

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=utf8mb4 COLLATE=utf8_unicode_ci;

Expand HTML Table with DataTables (index.html)
In this web page, the member’s data will be listed in an HTML table with search, filter, and pagination options using the DataTables jQuery plugin.

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

<!-- DataTables CSS library -->
<link rel="stylesheet" type="text/css" href="DataTables/datatables.min.css"/>

<!-- jQuery library -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>

<!-- DataTables JS library -->
<script type="text/javascript" src="DataTables/datatables.min.js"></script>

JavaScript Code:
Use the DataTable() method to initialize the Datatables class. You need to specify the selector (#memListTable) of the HTML table where the DataTables will be attached.

To enable the server-side processing do the following:

• Set the processing option to true.
• Set the serverSide option to true.
• Specify the URL of the server-side script (getData.php) in the ajax option.

<script>
$(document).ready(function(){
    $('#memListTable').DataTable({
"processing": true,
"serverSide": true,
"ajax": "getData.php"
});
});
</script>

HTML Code:
Create an HTML table and add a selector (memListTable) to enable DataTables to this element.

Спойлер: Code

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

Server-side Script (getData.php)
The getData.php file is used to perform the server-side processing with PHP and MySQL. To make the SQL query building process easier, we will use the SSP class (ssp.class.php). It helps to fetch the member’s data from the MySQL database using PHP.

Спойлер: Code

<?php 
// Database connection info 
$dbDetails = array( 
    'host' => 'localhost', 
    'user' => 'root', 
    'pass' => 'root', 
    'db'   => 'codexworld' 
); 
 
// 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. See the code of the SSP library from Для просмотра ссылки Войди или Зарегистрируйся.

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

PHP CRUD Operations without Page Refresh using jQuery, Ajax, and MySQL

Conclusion
Our example code will help you to add the DataTables plugin with Server-side processing on the web page. You can easily load data from the database using PHP & MySQL and list them in an HTML table with some useful features (search, filtering, sorting, and paging). Apart from these features, the DataTables has various options to enhance the HTML tables with server-side data.

[/SHOWTOGROUPS]