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 and datatables.min.js in <head> section and also include the jQuery Library. - You can also use CDN.
<!-- Datatable CSS -->
<link href='//cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css' rel='stylesheet' type='text/css'>
<!-- jQuery Library -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<!-- Datatable JS -->
<script src="//cdn.datatables.net/1.10.19/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='//cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css' rel='stylesheet' type='text/css'>
<!-- jQuery Library -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<!-- Datatable JS -->
<script src="//cdn.datatables.net/1.10.19/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 a new 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 add emp_name search. - If
$searchByGender is not empty then add gender search. - If
$searchByValue is not empty then search value on emp_name, email, and city fields.
Count total record with or without filter from employee table.
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 in 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();
});
});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.
