Articles Add Custom Search and Filter to DataTables Server-side Processing with PHP by "unknow"

[emailx45]

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

Спойлер: Content

[SHOWTOGROUPS=4,20]

DataTables jQuery plugin provides a quick and easy way to display data list in tabular format on the web page. The search, filter, and pagination functionality can be easily added to the HTML table with DataTables. Using the DataTables server-side processing, you can fetch the data dynamically from the database and list them in an HTML table with search, sorting, and pagination functionality.

Generally, the default search and filter options are used that come with DataTables. But, you can use custom search and filter input with the DataTables API. In this tutorial, we will show you how to add a custom search and filter with DataTables Server-side Processing using PHP and MySQL.

In the example code, we will fetch the members data from the database and list them with custom search and filter inputs in DataTables.

• Fetch and list data from the MySQL database using Datatables Server-side Processing.
• Add sorting and pagination features to the HTML table with Datatables.
• Add custom search and filter inputs to 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.

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;

Attach DataTables to HTML Table with Custom Search and Filter (index.html)
In this web page, the dynamic data will be listed in an HTML table with custom search and filter 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="js/jquery.min.js"></script>

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

HTML Table with Search and Filter Input:
Create an HTML table and add a selector (#memListTable) to attach DataTables to this element.

• Add search input field and filter dropdown to sort records by gender.

Спойлер: Code

<div class="post-search-panel">
    <input type="text" id="searchInput" placeholder="Type keywords..." />
    <select id="sortBy">
        <option value="">Sort by</option>
        <option value="Male">Male</option>
        <option value="Female">Female</option>
    </select>
</div>
<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>

Attach DataTables to HTML Table:
Initialize DataTables API class using DataTable() method and configure the table object.

• To disable the default search, set the searching option to false.
• To enable server-side processing,
  • Set the processing option to true.
  • Set the serverSide option to true.
  • Specify the server-side script (getData.php) URL in url option of the ajax object.
• To enable custom search and filter use extend() method.
  • Specify the custom field name and select the input value.

Спойлер: Code

<script>
// Initialize DataTables API object and configure table
var table = $('#memListTable').DataTable({
"searching": false,
"processing": true,
"serverSide": true,
"ajax": {
"url": "getData.php",
"data": function ( d ) {
return $.extend( {}, d, {
"search_keywords": $("#searchInput").val().toLowerCase(),
"filter_option": $("#sortBy").val().toLowerCase()
} );
}
}
});

$(document).ready(function(){
// Redraw the table
table.draw();

// Redraw the table based on the custom input
$('#searchInput,#sortBy').bind("keyup change", function(){
table.draw();
});
});
</script>

Server-side Script (getData.php)
The getData.php file is used to perform the server-side processing with search and filter. To make the SQL query building process easier, we will use the SSP class (ssp.class.php).

• The simple() function of the SSP class helps to fetch the members data from the database based on the search and filter query using PHP and MySQL.

Спойлер: 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';
}
)
);

$searchFilter = array();
if(!empty($_GET['search_keywords'])){
$searchFilter['search'] = array(
'first_name' => $_GET['search_keywords'],
'last_name' => $_GET['search_keywords'],
'email' => $_GET['search_keywords'],
'country' => $_GET['search_keywords']
);
}
if(!empty($_GET['filter_option'])){
$searchFilter['filter'] = array(
'gender' => $_GET['filter_option']
);
}

// Include SQL query processing class
require 'ssp.class.php';

// Output data as json format
echo json_encode(
SSP::simple( $_GET, $dbDetails, $table, $primaryKey, $columns, $searchFilter )
);
[/code]

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 search and filter. You can see the code of the SSP library from here.

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

Server Side Filtering using jQuery Ajax PHP and MySQL

Conclusion
This example script helps you to add the custom search and filter options to the DataTables plugin easily. You can enable the Server-side processing DataTables and add custom input to the search, filter, sort records. Also, the HTML table and data list can be customized easily as per your needs. DataTables API provides various configuration options to enhance the table with server-side data from the database.

[/SHOWTOGROUPS]