Date range search in DataTable with jQuery AJAX and PHP

Last updated on by

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 them 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.
Date range search in DataTable with jQuery AJAX and PHP

Demo  Download

Contents

  1. Table structure
  2. Configuration
  3. Download & Include
  4. HTML
  5. Script
  6. PHP
  7. Demo
  8. Conclusion

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
);

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.6.0/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 to variables. Also, read passed date filter values and assign to $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 Query
$searchQuery = array();
if($searchValue != ''){
     $searchQuery[] = "(emp_name like '%".$searchValue."%' or email like '%".$searchValue."%' or city like '%".$searchValue."%')";
}

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

$WHERE = "";
if(count($searchQuery) > 0){
     $WHERE = " WHERE ".implode(' and ',$searchQuery);
}

## 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);
$records = mysqli_fetch_assoc($sel);
$totalRecordwithFilter = $records['allcount'];

## Fetch records
$empQuery = "select * from employee ".$WHERE." 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.

If you found this tutorial helpful then don't forget to share.

17 thoughts on “Date range search in DataTable with jQuery AJAX and PHP”

  1. Sir! my table contains images then, it shows only image name but not image, and i want to add hyperlink to the fetching record. please help i am new

    Reply

  2. Hi
    This ” Date range search in DataTable with jQuery AJAX and PHP ” Download link not working pls update correctly.

    Thank you.

    Reply

  3. Hello Sir , thank you for the script however Im getting this error
    Notice: Undefined index: draw in C:\xampp\htdocs\date\ajaxfile.php on line 5

    Notice: Undefined index: start in C:\xampp\htdocs\date\ajaxfile.php on line 6

    Notice: Undefined index: length in C:\xampp\htdocs\date\ajaxfile.php on line 7

    Notice: Undefined index: order in C:\xampp\htdocs\date\ajaxfile.php on line 8

    Notice: Undefined index: columns in C:\xampp\htdocs\date\ajaxfile.php on line 9

    Notice: Undefined index: order in C:\xampp\htdocs\date\ajaxfile.php on line 10

    Notice: Undefined index: search in C:\xampp\htdocs\date\ajaxfile.php on line 11

    Notice: Undefined index: searchByFromdate in C:\xampp\htdocs\date\ajaxfile.php on line 14

    Notice: Undefined index: searchByTodate in C:\xampp\htdocs\date\ajaxfile.php on line 15

    Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\date\ajaxfile.php on line 30

    Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\date\ajaxfile.php on line 35

    Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\date\ajaxfile.php on line 43
    {“draw”:0,”iTotalRecords”:null,”iTotalDisplayRecords”:null,”aaData”:[]}

    Please help

    Reply

  4. my datatable does each and everything in the tutorial..thanks about that .
    my problem comes when i want to include print,pdf,excel buttons..i have tried using v1.10.22 JS and CSS and 1.6.4 button my table doesnt even display until i remove them….please help me out mostly on the print button..remember this me iam using v1.10.19 like for this tutorial

    Reply

  7. Hi Yogesh,

    Can tell me please how can i increase (or add) entry number? i want to show all my entries i have i database, here we can show max 100 entries per page. Thankyou

    Reply

  8. Sir before dates should not be highlighted.
    (Ex)FromDate: 2019-08-05, ToDate: 2019-07-05. Here ToDate is before than FromDate.
    This is my query sir. Can you modify the code.

    Reply

  9. HI, thank you a lot for this tutorial , i would like to change sort column but I don’t know where, from which page the below post are sent ?

    $columnIndex = $_POST[‘order’][0][‘column’]; // Column index
    $columnSortOrder = $_POST[‘order’][0][‘dir’]; // asc or desc

    Reply

Leave a Comment

© 2016-2023 Makitweb, All rights reserved