The DataTables jQuery plugin makes it simple to show a data list in a tabular manner on a web page. DataTables makes it simple to add search, filter, and pagination functions to an HTML table. You may dynamically download data from a database and list it in an HTML table with search, sorting, and pagination capability using DataTables server-side processing.
The default search and filter tools that come with DataTables are typically used. The DataTables API, on the other hand, allows you to apply custom search and filter input. In this article, we’ll teach you how to use PHP and MySQL to create a custom search and filter to DataTables Server-side Processing.
The members data will be retrieved from the database and listed in DataTables with custom search and filter inputs.
Using Datatables Server-side Processing, fetch and list data from a MySQL database.
With Datatables, you can add sorting and pagination to your HTML table.
Datatables can now have specific search and filter inputs.
Make a database table.
A table in the database is necessary to store the member’s information. In the MySQL database, the following SQL creates a members table with some basic fields.
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 with Custom Search and Filter can be attached to an HTML table (index.html)
Using the DataTables jQuery plugin, the dynamic data will be shown in an HTML table with specific search and filter options.
Include the jQuery and DataTables library files in the DataTables JS and CSS library.
<!-- 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>Table in HTML with Search and Filter Create an HTML table and add a selector (#memListTable) to this element to attach DataTables.
To sort records by gender, add a search input box and a filter dropdown.
<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>DataTables can be attached to an HTML table in the following way:
Using the DataTable() method, initialise the DataTables API class and customise the table object.
Set the searching option to false to disable the default search.
Set the processing option to true to enable server-side processing.
True is the value for the serverSide option.
In the url option of the ajax object, specify the URL of the server-side script (getData.php).
Use the extend() method to provide custom search and filtering.
Choose a name for the custom field and a value for the input.
<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>Script that runs on the server (getData.php)
The server-side processing with search and filter is done in the getData.php file. The SSP class will be used to ease the process of creating SQL queries easier (ssp.class.php).
The SSP class’s basic() function assists in retrieving members’ data from the database using PHP and MySQL, based on a search and filter query.
<?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 )
);Library of SSP
The SSP class is in charge of database operations. It includes various assistance functions for creating SQL queries for DataTables server-side processing, including search and filtering. The SSP library’s source code may be found here.
You do not need to download this library or any of the other files individually because they are included in the source code.
Note :