Web Development: Add Custom Filter in DataTable

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.

Web Development

Saturday, December 5, 2020

Add Custom Filter in DataTable – AJAX and PHP

1. Table structure

2. Configuration

3. Download & Include

4. HTML

5. PHP

6. Script

8. Conclusion

Credit & Source: https://makitweb.com/how-to-add-custom-filter-in-datatable-ajax-and-php/

No comments:

Post a Comment

How to backup and download Database using PHP

Followers