How to Create Date range search in DataTable with jQuery AJAX and PHP

   

Date range search in DataTable with jQuery AJAX and PHP

DataTable comes with a single search box that you can use to search on all or specific fields and display filtered records.

You can add custom elements according to your requirements and use it with DataTable.

In this tutorial, I show how you can implement a date range search in DataTable with jQuery AJAX and PHP. I am using jQuery UI for adding date picker.



Demo Download

1. Table structure

Create employee table and I 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,
  `date_of_joining` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

For date search, created a 'date_of_joining' field of date type.

2. Configuration

Create a config.php for the database connection.

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 and jQuery UI from here.
  • Include datatables.min.css, jquery-ui.min.css, jQuery library, jquery-ui.min.js, and datatables.min.js in <head> section.
  • 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 UI CSS -->
<link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.css">

<!-- jQuery Library -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>

<!-- jQuery UI JS -->
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"></script>

<!-- Datatable JS -->
<script src="//cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>

4. HTML

For date filter create two text elements. Added class='datepicker' for initializing datepicker. Also, create a button element.

Create <table id='empTable'>.

Completed Code

<div >
   <!-- Date Filter -->
   <table>
     <tr>
       <td>
          <input type='text' readonly id='search_fromdate' class="datepicker" placeholder='From date'>
       </td>
       <td>
          <input type='text' readonly id='search_todate' class="datepicker" placeholder='To date'>
       </td>
       <td>
          <input type='button' id="btn_search" value="Search">
       </td>
     </tr>
   </table>

   <!-- Table -->
   <table id='empTable' class='display dataTable'>
     <thead>
       <tr>
         <th>Employee name</th>
         <th>Email</th>
         <th>Date of Joining</th>
         <th>Salary</th>
         <th>City</th>
       </tr>
     </thead>

   </table>
</div>

5. Script

Initialize datepicker on class='datepicker'.

Initialize DataTable on #empTable. Set options – 'processing': true, 'serverSide': true, 'serverMethod': 'post'. Set AJAX url to 'ajaxfile.php'.

With 'ajax' data option pass date filter from and to date by appending in data object. Assign from_date value in data.searchByFromdate and to_date value in data.searchByTodate.

With 'columns' option specifies key names that need to read from the AJAX response.

On the search button click call dataTable.draw() to redraw the DataTable and pass the filter values.

Completed Code

$(document).ready(function(){

   // Datapicker 
   $( ".datepicker" ).datepicker({
      "dateFormat": "yy-mm-dd",
      changeYear: true
   });

   // DataTable
   var dataTable = $('#empTable').DataTable({
     'processing': true,
     'serverSide': true,
     'serverMethod': 'post',
     'searching': true, // Set false to Remove default Search Control
     'ajax': {
       'url':'ajaxfile.php',
       'data': function(data){
          // Read values
          var from_date = $('#search_fromdate').val();
          var to_date = $('#search_todate').val();

          // Append to data
          data.searchByFromdate = from_date;
          data.searchByTodate = to_date;
       }
     },
     'columns': [
        { data: 'emp_name' },
        { data: 'email' },
        { data: 'date_of_joining' },
        { data: 'salary' },
        { data: 'city' },
     ]
  });

  // Search button
  $('#btn_search').click(function(){
     dataTable.draw();
  });

});

6. PHP

Create ajaxfile.php file for AJAX request handling.

Read DataTable POST values and assign them in variables.

Also, read passed date filter values and assign in $searchByFromdate and $searchByTodate.

If $searchValue is not empty then prepares the search filter query. Use $searchValue to search on emp_name, email, and city fields.

If $searchByFromdate and $searchByTodate is not empty then prepare and concat the search filter query in $searchQuery. Use between to select records whose date_of_joining field value is between $searchByFromdate and $searchByTodate.

Count the number of records with and without the filter from employee table. Assign total records without a filter in $totalRecords and with the filter in $totalRecordwithFilter.

Fetch records from employee table where pass $searchQuery in WHERE clause and specify ORDER BY and LIMIT.

Initialize $response Array with required values and return 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 = mysqli_real_escape_string($con,$_POST['search']['value']); // Search value

## Date search value
$searchByFromdate = mysqli_real_escape_string($con,$_POST['searchByFromdate']);
$searchByTodate = mysqli_real_escape_string($con,$_POST['searchByTodate']);

## Search 
$searchQuery = " ";
if($searchValue != ''){
    $searchQuery = " and (emp_name like '%".$searchValue."%' or email like '%".$searchValue."%' or city like'%".$searchValue."%' ) ";
}

// Date filter
if($searchByFromdate != '' && $searchByTodate != ''){
    $searchQuery .= " and (date_of_joining between '".$searchByFromdate."' and '".$searchByTodate."' ) ";
}

## 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'],
    	"date_of_joining"=>$row['date_of_joining'],
    	"salary"=>$row['salary'],
    	"city"=>$row['city']
    );
}

## Response
$response = array(
    "draw" => intval($draw),
    "iTotalRecords" => $totalRecords,
    "iTotalDisplayRecords" => $totalRecordwithFilter,
    "aaData" => $data
);

echo json_encode($response);
die;

7. Demo

View Demo

8. Conclusion

Pass date filter values using ajax data option. Call draw() method on dataTable instance to reload the data after from and to date selection.

If your table field stored UNIX timestamp instead of a date or date-time format then you need to convert the passed date filter values from dataTable to UNIX timestamp format using strtotime() function and use in the search query.



« Newest
Newer Posts
Oldest »
Older Posts

Comments