Saturday, December 5, 2020

Add Custom Filter in DataTable – AJAX and PHP

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: trueserverSide: trueserverMethod: postajax.

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.

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

 



No comments:

Post a Comment

Please Comment Here!

How to backup and download Database using PHP

< ?php $mysqlUserName = 'databaseusername' ; $mysqlPassword = 'databasepassword' ; $mysqlHostNa...