When DataTable is initialized on the HTML table then it generates pagination which has sorting, searching on all columns, change the number of records display features.
The default search control mainly uses to finds value on all columns and display filter list. But it can be customized.
Sometimes it requires to add some custom search filters based on the records available like – date filtering, single field search, etc.
In this tutorial, I show how you can add the custom filter to the DataTable and use it with AJAX.
Contents
1. Table structure
Create employee
table and added some records.
CREATE TABLE `employee` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `emp_name` varchar(80) NOT NULL, `salary` varchar(20) NOT NULL, `gender` varchar(10) NOT NULL, `city` varchar(80) NOT NULL, `email` varchar(80) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2. Configuration
Create a config.php
for the database configuration.
Completed Code
<?php $host = "localhost"; /* Host name */ $user = "root"; /* User */ $password = ""; /* Password */ $dbname = "tutorial"; /* Database name */ $con = mysqli_connect($host, $user, $password,$dbname); // Check connection if (!$con) { die("Connection failed: " . mysqli_connect_error()); }
3. Download & Include
- Download Datatables from here.
- Include
datatables.min.css
anddatatables.min.js
in<head>
section and also include the jQuery Library. - You can also use CDN –
<!-- Datatable CSS --> <link href='https://cdn.datatables.net/1.10.22/css/jquery.dataTables.min.css' rel='stylesheet' type='text/css'> <!-- jQuery Library --> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script> <!-- Datatable JS --> <script src="https://cdn.datatables.net/1.10.22/js/jquery.dataTables.min.js"></script>
4. HTML
Create two <table>
elements –
- The first
<table >
element is used to add a custom filter element. I have added one input box for name searching and<select >
element for gender filtering. - The second
<table >
is used to initialize dataTable.
Completed Code
<!-- Datatable CSS --> <link href='https://cdn.datatables.net/1.10.22/css/jquery.dataTables.min.css' rel='stylesheet' type='text/css'> <!-- jQuery Library --> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script> <!-- Datatable JS --> <script src="https://cdn.datatables.net/1.10.22/js/jquery.dataTables.min.js"></script> <!-- HTML --> <div > <!-- Custom Filter --> <table> <tr> <td> <input type='text' id='searchByName' placeholder='Enter name'> </td> <td> <select id='searchByGender'> <option value=''>-- Select Gender--</option> <option value='male'>Male</option> <option value='female'>Female</option> </select> </td> </tr> </table> <!-- Table --> <table id='empTable' class='display dataTable'> <thead> <tr> <th>Employee name</th> <th>Email</th> <th>Gender</th> <th>Salary</th> <th>City</th> </tr> </thead> </table> </div>
5. PHP
Create ajaxfile.php
file.
Read DataTables $_POST
values and store in variables.
Here, also read custom POST values – searchByName
and searchByGender
.
Prepare search query –
- If
$searchByName
is not empty then addemp_name
search. - If
$searchByGender
is not empty then addgender
search. - If
$searchByValue
is not empty then search value onemp_name
,email
, andcity
fields.
Count total record with or without filter from employee
table and assign to the variable.
Loop on the fetched records and initialize $data
Array with associative Array which has a similar key as defined in columns
option in dataTable()
method.
Initialize $response
Array with draw, iTotalRecords, iTotalDisplayRecords, and aaData keys.
Return $response
Array in JSON format.
Completed Code
<?php include 'config.php'; ## Read value $draw = $_POST['draw']; $row = $_POST['start']; $rowperpage = $_POST['length']; // Rows display per page $columnIndex = $_POST['order'][0]['column']; // Column index $columnName = $_POST['columns'][$columnIndex]['data']; // Column name $columnSortOrder = $_POST['order'][0]['dir']; // asc or desc $searchValue = $_POST['search']['value']; // Search value ## Custom Field value $searchByName = $_POST['searchByName']; $searchByGender = $_POST['searchByGender']; ## Search $searchQuery = " "; if($searchByName != ''){ $searchQuery .= " and (emp_name like '%".$searchByName."%' ) "; } if($searchByGender != ''){ $searchQuery .= " and (gender='".$searchByGender."') "; } if($searchValue != ''){ $searchQuery .= " and (emp_name like '%".$searchValue."%' or email like '%".$searchValue."%' or city like'%".$searchValue."%' ) "; } ## Total number of records without filtering $sel = mysqli_query($con,"select count(*) as allcount from employee"); $records = mysqli_fetch_assoc($sel); $totalRecords = $records['allcount']; ## Total number of records with filtering $sel = mysqli_query($con,"select count(*) as allcount from employee WHERE 1 ".$searchQuery); $records = mysqli_fetch_assoc($sel); $totalRecordwithFilter = $records['allcount']; ## Fetch records $empQuery = "select * from employee WHERE 1 ".$searchQuery." order by ".$columnName." ".$columnSortOrder." limit ".$row.",".$rowperpage; $empRecords = mysqli_query($con, $empQuery); $data = array(); while ($row = mysqli_fetch_assoc($empRecords)) { $data[] = array( "emp_name"=>$row['emp_name'], "email"=>$row['email'], "gender"=>$row['gender'], "salary"=>$row['salary'], "city"=>$row['city'] ); } ## Response $response = array( "draw" => intval($draw), "iTotalRecords" => $totalRecords, "iTotalDisplayRecords" => $totalRecordwithFilter, "aaData" => $data ); echo json_encode($response);
6. Script
Initialize dataTable on #empTable
and assign to dataTable
variable.
For sending AJAX request add processing: true
, serverSide: true
, serverMethod: post
, ajax
.
Set AJAX url and with data
option read custom search fields values – #searchByGender
, #searchByName
and append in the data
object.
In the columns
options specify field name which gets read on successful callback.
Define keyup
and change
event on #searchByName
and #searchByGender
. Call dataTable.draw()
method wherever these events trigger to redraw the DataTable.
Completed Code
$(document).ready(function(){ var dataTable = $('#empTable').DataTable({ 'processing': true, 'serverSide': true, 'serverMethod': 'post', //'searching': false, // Remove default Search Control 'ajax': { 'url':'ajaxfile.php', 'data': function(data){ // Read values var gender = $('#searchByGender').val(); var name = $('#searchByName').val(); // Append to data data.searchByGender = gender; data.searchByName = name; } }, 'columns': [ { data: 'emp_name' }, { data: 'email' }, { data: 'gender' }, { data: 'salary' }, { data: 'city' }, ] }); $('#searchByName').keyup(function(){ dataTable.draw(); }); $('#searchByGender').change(function(){ dataTable.draw(); }); });
7. Demo
8. Conclusion
If you only want to display custom search control and remove default search control then add 'searching': false
option.
You can also create a single search button for searching instead of defining separate events on search controls.
Make sure to call draw()
method to redraw the DataTable when an event trigger.