Datatable custom filter dropdown enhances the user experience on any website. Such a filter type also helps in fetching out the requisite information in a short time. You can sort out the information on a website effortlessly using it. This in turn enhances your searchability and generates relevant search results.
So, how to enable this functionality on your PHP website? You can constructively list the data in an HTML table using DataTables server-side processing. It enhances the searchability, sorting, and pagination functionality on your website.
Further, you will learn how to add a custom search and filter with DataTables Server side-processing. It will be done through PHP and MySQL.
So, how to do it?
- Fetch the relevant data from the MySQL using Datatables Server-side processing
- Put in sort and pagination functionalities to the HTML tables with Datatables
- Put in custom search and filter inputs
Creating a basic database table
This is the first requirement while creating a Datatable custom filter dropdown. A basic table with basic fields is a requisite to save the member’s information in the database. So, the below SQL code is used to create such a table 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;Creating custom search and filter in database
Furthermore, you can fill in the data in HTML format with custom search and filter options. This can be made possible through the DataTables jQuery plugin.
Include the following code in 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>
<!-- Creating HTML table with Search and Filter input -->- Add search input field and thereby filter dropdown to sort out results as per gender
<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
After the above step, you need to initialize the DataTables API using DataTable() method and configure it.
- Set the searching option to false to disable the default format.
- Keep the server-side processing on by setting the ‘processing’ and ‘server side’ option to ‘true’.
- In ajax object, fill in the URL by specifying the server-side script (getData.php).
- You can use the extend() method to enable custom search and filter options.
- To begin, you need to mention the custom field name and enter the input value.
<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>
This is another step to enable the Datatable custom filter dropdown. The getData.php file is installed to enable search and filter. Also, we will use the SSP class ssp.class.php to build the SQL query.
- The simple() function enables to fetch members data as per the search and filter query. It can be executed using PHP and MySQL.
<?php // Database connection info $dbDetails = array( 'host' => 'localhost', 'user' => 'root', 'pass' => 'root', 'db' => 'phpsof' ); // 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 ) );
SSP Library
The SSP class contains helper functions that help in building the SQL queries. These functions are relevant to add custom search and filter to DataTables Server-side processing with PHP.
Conclusion
So, to conclude, this example script lets you add Datatable custom filter dropdown. You can facilitate the easy sorting and filter using the server-side processing DataTables. This is how you can customize the tabular information as per your needs.
Besides this, you can also refer our Popup contact form guide. This comprehensive guide tells every detail on building popup contact forms using jQuery and PHP.
