Date range filters in server-side jQuery dataTables using PHP and AJAX

Date range filters in server side jQuery dataTables using PHP and AJAX

DataTables is a very popular and useful jQuery library for creating powerful tables. In this article, we will see how to create Date range filters in server-side jQuery dataTables using PHP and AJAX.

The result looks like the below:

Date range filters in server-side jQuery dataTables using PHP and AJAX

In this article, we have also covered:

  1. Add custom filters in the server-side dataTable
  2. Display all records in server-side dataTable
  3. Add the Copy, Print, and export to CSV, Excel, and PDF buttons in the server-side dataTables
  4. Save the state of filters and entries in server-side dataTable
  5. Convert the timestamp() date into the friendly date

So, this is a bonus article!

Implementation for Date range filters in server-side jQuery dataTables

Now, Let’s start with our main topic for Date range filters in server-side jQuery dataTables.

Folder structure

You are not bound to follow the same folder structure. However, to run this example you need to follow it first and then you can change it as per your programming best practices or project structure. The file and folder structure for this example is:

application-hierarchy

In this guide, we will explain and write code for each of the files.

Create database table

The first step for implementing Date range filters in server-side jQuery dataTables is to create a table that will hold the data for the server-side dataTable. Let’s create a table of users that we will use for the server-side dataTable:

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `email` varchar(75) NOT NULL,
  `gender` enum('Male','Female') NOT NULL,
  `date_of_birth` date NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

Now, populate the users data by adding some records.

INSERT INTO `users` (`id`, `first_name`, `last_name`, `email`, `gender`, `date_of_birth`, `created_at`) VALUES
(1, 'Allen', 'Carlson', 'allen@example.com', 'Male', '1992-12-06', '2021-02-01 07:14:39'),
(2, 'Evette', 'Parker', 'evette@example.com', 'Female', '1973-10-14', '2021-02-05 07:14:39'),
(3, 'Savanna', 'Johnson', 'savanna@example.com', 'Female', '1982-03-19', '2021-02-01 07:14:39'),
(4, 'Gloria', 'Rosalez', 'gloria@example.com', 'Female', '1992-06-12', '2021-02-01 07:14:39'),
(5, 'Paul', 'Fredrickson', 'paul@example.com', 'Male', '1990-06-07', '2021-02-02 07:14:39'),
(6, 'Charlotte', 'Winston', 'charlotte@example.com', 'Female', '1962-12-06', '2021-02-02 07:14:39'),
(7, 'Ivan', 'Payne', 'ivan@example.com', 'Male', '1959-01-17', '2021-02-02 07:14:39'),
(8, 'Philip', 'Andrade', 'philip@example.com', 'Male', '1964-03-24', '2021-02-03 07:14:39'),
(9, 'Rick', 'Davenport', 'rick@example.com', 'Male', '1955-03-18', '2021-02-03 07:14:39'),
(10, 'Pearl', 'Demmer', 'pearl@example.com', 'Female', '1988-09-09', '2021-02-04 07:14:39'),
(11, 'Michael', 'Brooks', 'michael@example.com', 'Male', '1993-09-21', '2021-02-05 07:14:39'),
(12, 'Vivian', 'Zoller', 'vivian@example.com', 'Female', '1986-12-09', '2021-02-04 07:14:39'),
(13, 'Jamie', 'Garcia', 'jamie@example.com', 'Male', '2001-03-12', '2021-02-05 07:14:39'),
(14, 'Natalie', 'Ray', 'natalie@example.com', 'Female', '1981-01-13', '2021-02-05 07:14:39'),
(15, 'Charlie', 'Harpster', 'charlie@example.com', 'Male', '1990-09-05', '2021-02-06 07:14:39'),
(16, 'Mae', 'Heaton', 'mae@example.com', 'Female', '1974-03-24', '2021-02-06 07:14:39');

HTML Markup

Create a file index.php start adding the following HTML code.

<!-- CSS -->

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.20/css/dataTables.bootstrap.min.css">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.6.5/css/buttons.dataTables.min.css">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.6.4/css/bootstrap-datepicker.css" />


<!-- JavaScript -->

<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
<script src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.6.5/js/dataTables.buttons.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>
<script src="https://cdn.datatables.net/buttons/1.6.5/js/buttons.html5.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.6.5/js/buttons.print.min.js"></script>
<script src="https://cdn.datatables.net/1.10.20/js/dataTables.bootstrap.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.6.4/js/bootstrap-datepicker.js"></script>

We have used CDN links for Date range filters in server-side jQuery dataTables, for adding CSS and JS libraries.

Let’s write the HTML to build the main structure and this will also go inside index.php file:

<div class="container">
  <div class="row">
    <div class="col-sm-12">

      <div class="well">
        <h2 class="text-center">Date range filters in server side jQuery datatables using PHP and AJAX</h2>
      </div>
      <!-- #Add custom filters in the server-side dataTable -->
      <div class="row well input-daterange">
        <div class="col-sm-4">
          <label class="control-label">Gender</label>
          <select class="form-control" name="gender" id="gender" style="height: 40px;">
            <option value="">- Please select -</option>
            <option value="male">Male</option>
            <option value="female">Female</option>
          </select>
        </div>

        <div class="col-sm-3">
          <label class="control-label">Start date</label>
          <input class="form-control datepicker" type="text" name="initial_date" id="initial_date" placeholder="yyyy-mm-dd" style="height: 40px;"/>
        </div>

        <div class="col-sm-3">
          <label class="control-label">End date</label>
          <input class="form-control datepicker" type="text" name="final_date" id="final_date" placeholder="yyyy-mm-dd" style="height: 40px;"/>
        </div>

        <div class="col-sm-2">
          <button class="btn btn-success btn-block" type="submit" name="filter" id="filter" style="margin-top: 30px">
            <i class="fa fa-filter"></i> Filter
          </button>
        </div>

        <div class="col-sm-12 text-danger" id="error_log"></div>
      </div>

      <br/><br/>

      <table id="fetch_users" class="table table-hover table-striped table-bordered" cellspacing="0" width="100%">
        <thead>
          <tr>
            <th>#</th>
            <th>First name</th>
            <th>Last name</th>
            <th>Email</th>
            <th>Gender</th>
            <th>Date of birth</th>
            <th>Created at</th>
          </tr>
        </thead>
      </table>

    </div>
  </div>
</div>

In the above code, we have also added date range fields to apply filters in server-side jQuery dataTables.

JavaScript logic

JavaScript plays a crucial role in creating AJAX requests. These AJAX requests are used in the Date range filters in server-side jQuery dataTables.

Write the following code after initializing the JavaScript libraries that go inside index.php file:

<script type="text/javascript">

  load_data(); // first load

  function load_data(initial_date, final_date, gender){
    var ajax_url = "jquery-ajax.php";

    $('#fetch_users').DataTable({
      "order": [[ 0, "desc" ]],
      dom: 'Blfrtip', // Add the Copy, Print and export to CSV, Excel and PDF buttons
      buttons: [
        'copy', 'csv', 'excel', 'pdf', 'print'
      ],
      "processing": true,
      "serverSide": true,
      "stateSave": true, // #Save the state of filters and entries
      "lengthMenu": [ [10, 25, 50, 100, -1], [10, 25, 50, 100, "All"] ], // #Display all records in server-side dataTable
      "ajax" : {
        "url" : ajax_url,
        "dataType": "json",
        "type": "POST",
        "data" : { 
          "action" : "fetch_users", 
          "initial_date" : initial_date, 
          "final_date" : final_date, 
          "gender" : gender
        },
        "dataSrc": "records"
      },
      "columns": [
        { "data" : "counter" },
        { "data" : "first_name" },
        { "data" : "last_name" },
        { "data" : "email" },
        { "data" : "gender" },
        { "data" : "date_of_birth" },
        { "data" : "created_at" }
      ]
    }); 
  }  

  $("#filter").click(function(){
    var initial_date = $("#initial_date").val();
    var final_date = $("#final_date").val();
    var gender = $("#gender").val();

    if(initial_date == '' && final_date == ''){
      $('#fetch_users').DataTable().destroy();
      load_data("", "", gender); // filter immortalize only
    }else{
      var date1 = new Date(initial_date);
      var date2 = new Date(final_date);
      var diffTime = Math.abs(date2 - date1);
      var diffDays = Math.ceil(diffTime / (1000 * 60 * 60 * 24)); 

      if(initial_date == '' || final_date == ''){
          $("#error_log").html("Warning: You must select both (start and end) date.</span>");
      }else{
        if(date1 > date2){
            $("#error_log").html("Warning: End date should be greater then start date.");
        }else{
           $("#error_log").html(""); 
           $('#fetch_users').DataTable().destroy();
           load_data(initial_date, final_date, gender);
        }
      }
    }
  });

  $('.input-daterange').datepicker({
    todayBtn:'linked',
    format: "yyyy-mm-dd",
    autoclose: true
  });

</script>

PHP – Backend processing

Let’s create a database connection. Create a folder config and add a file db-config.php in it, then add the following code:

<?php 
  error_reporting(1);
  
  const DB_HOST = 'localhost';
  const DB_USER = 'root';
  const DB_PASS = '';
  const DB_NAME = 'serverside_dt';

  $connection = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);

  if(!$connection) {
    die("Connection failed: " . mysqli_connect_error());
  }
?>

Please don’t forget to change the database credentials as per yours.

Now create a file jquery-ajax.php and add the following code, this file contains all the server-side logic to populate Date range filters in server-side jQuery dataTables:

<?php
include 'config/db-config.php';
global $connection;

// Date range filters in server-side jQuery dataTables
if($_REQUEST['action'] == 'fetch_users'){

    $requestData = $_REQUEST;
    $start = $_REQUEST['start'];

    $initial_date = $_REQUEST['initial_date'];
    $final_date = $_REQUEST['final_date'];
    $gender = $_REQUEST['gender'];

    if(!empty($initial_date) && !empty($final_date)){
        $date_range = " AND created_at BETWEEN '".$initial_date."' AND '".$final_date."' ";
    }else{
        $date_range = "";
    }

    if($gender != ''){
        $gender = " AND gender = '$gender' ";
    }

    $columns = ' id, first_name, last_name, email, gender, date_of_birth, created_at ';
    $table = ' users ';
    $where = " WHERE first_name!='' ".$date_range.$gender;

    $columns_order = array(
        0 => 'id',
        1 => 'first_name',
        2 => 'last_name',
        3 => 'email',
        4 => 'gender',
        5 => 'date_of_birth',
        6 => 'created_at'
    );

    $sql = "SELECT ".$columns." FROM ".$table." ".$where;

    $result = mysqli_query($connection, $sql);
    $totalData = mysqli_num_rows($result);
    $totalFiltered = $totalData;

    if( !empty($requestData['search']['value']) ) {
        $sql.=" AND ( first_name LIKE '%".$requestData['search']['value']."%' ";
        $sql.=" OR last_name LIKE '%".$requestData['search']['value']."%'";
        $sql.=" OR email LIKE '%".$requestData['search']['value']."%'";
        $sql.=" OR gender LIKE '".$requestData['search']['value']."'";
        $sql.=" OR date_of_birth LIKE '%".$requestData['search']['value']."%'";
        $sql.=" OR created_at LIKE '%".$requestData['search']['value']."%' )";
    }

    $result = mysqli_query($connection, $sql);
    $totalData = mysqli_num_rows($result);
    $totalFiltered = $totalData;

    $sql .= " ORDER BY ". $columns_order[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir'];
    
    // #if all records are set display, remove the LIMIT from SQL clause
    if($requestData['length'] != "-1"){
        $sql .= " LIMIT ".$requestData['start']." ,".$requestData['length'];
    }

    $result = mysqli_query($connection, $sql);
    $data = array();
    $counter = $start;

    $count = $start;
    while($row = mysqli_fetch_array($result)){
        $count++;
        $nestedData = array();

        $nestedData['counter'] = $count;

        $nestedData['last_name'] = $row["last_name"];
        $nestedData['first_name'] = $row["first_name"];

        $nestedData['email'] = '<a href="mailto:'.strtolower($row["email"]).'">'.strtolower($row["email"]).'</a>';

        $nestedData['date_of_birth'] = $row["date_of_birth"];
        $nestedData['gender'] = $row["gender"];
    
        // #convert the timestamp() date into friendly date
        $time = strtotime($row["created_at"]);
        $nestedData['created_at'] = date('h:i:s A - d M, Y', $time);

        $data[] = $nestedData;
    }

    $json_data = array(
        "draw"            => intval( $requestData['draw'] ),
        "recordsTotal"    => intval( $totalData),
        "recordsFiltered" => intval( $totalFiltered ),
        "records"         => $data
    );

    echo json_encode($json_data);
}
?>

The complete code

The complete code for Date range filters in server-side jQuery dataTables is:

<?php 
  include "config/db-config.php";
?>
<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Date range filters in server-side jQuery dataTables</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
    
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.20/css/dataTables.bootstrap.min.css">
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.6.5/css/buttons.dataTables.min.css">

    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.6.4/css/bootstrap-datepicker.css" />

    <!-- HTML5 shim and Respond.js for IE8 support of HTML5 elements and media queries -->
        <!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
        <!--[if lt IE 9]>
          <script src="https://oss.maxcdn.com/html5shiv/3.7.3/html5shiv.min.js"></script>
          <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
        <![endif]-->  
  </head>
  <body>

    <div class="container">
      <div class="row">
        <div class="col-sm-12">

          <div class="well">
            <h2 class="text-center">Date range filters in server side jQuery datatables using PHP and AJAX</h2>
          </div>

          <div class="row well input-daterange">
            <div class="col-sm-4">
              <label class="control-label">Gender</label>
              <select class="form-control" name="gender" id="gender" style="height: 40px;">
                <option value="">- Please select -</option>
                <option value="male">Male</option>
                <option value="female">Female</option>
              </select>
            </div>

            <div class="col-sm-3">
              <label class="control-label">Start date</label>
              <input class="form-control datepicker" type="text" name="initial_date" id="initial_date" placeholder="yyyy-mm-dd" style="height: 40px;"/>
            </div>

            <div class="col-sm-3">
              <label class="control-label">End date</label>
              <input class="form-control datepicker" type="text" name="final_date" id="final_date" placeholder="yyyy-mm-dd" style="height: 40px;"/>
            </div>

            <div class="col-sm-2">
              <button class="btn btn-success btn-block" type="submit" name="filter" id="filter" style="margin-top: 30px">
                <i class="fa fa-filter"></i> Filter
              </button>
            </div>

            <div class="col-sm-12 text-danger" id="error_log"></div>
          </div>

          <br/><br/>

          <table id="fetch_users" class="table table-hover table-striped table-bordered" cellspacing="0" width="100%">
            <thead>
              <tr>
                <th>#</th>
                <th>First name</th>
                <th>Last name</th>
                <th>Email</th>
                <th>Gender</th>
                <th>Date of birth</th>
                <th>Created at</th>
              </tr>
            </thead>
          </table>

        </div>
      </div>
    </div>

    <script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>

    <script src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.6.5/js/dataTables.buttons.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.6.5/js/buttons.html5.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.6.5/js/buttons.print.min.js"></script>
    <script src="https://cdn.datatables.net/1.10.20/js/dataTables.bootstrap.min.js"></script>

    <script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.6.4/js/bootstrap-datepicker.js"></script>

    <script type="text/javascript">

      load_data(); // first load

      function load_data(initial_date, final_date, gender){
        var ajax_url = "jquery-ajax.php";

        $('#fetch_users').DataTable({
          "order": [[ 0, "desc" ]],
          dom: 'Blfrtip',
          buttons: [
            'copy', 'csv', 'excel', 'pdf', 'print'
          ],
          "processing": true,
          "serverSide": true,
          "stateSave": true,
          "lengthMenu": [ [10, 25, 50, 100, -1], [10, 25, 50, 100, "All"] ],
          "ajax" : {
            "url" : ajax_url,
            "dataType": "json",
            "type": "POST",
            "data" : { 
              "action" : "fetch_users", 
              "initial_date" : initial_date, 
              "final_date" : final_date, 
              "gender" : gender
            },
            "dataSrc": "records"
          },
          "columns": [
            { "data" : "counter" },
            { "data" : "first_name" },
            { "data" : "last_name" },
            { "data" : "email" },
            { "data" : "gender" },
            { "data" : "date_of_birth" },
            { "data" : "created_at" }
          ]
        }); 
      }  

      $("#filter").click(function(){
        var initial_date = $("#initial_date").val();
        var final_date = $("#final_date").val();
        var gender = $("#gender").val();

        if(initial_date == '' && final_date == ''){
          $('#fetch_users').DataTable().destroy();
          load_data("", "", gender); // filter immortalize only
        }else{
          var date1 = new Date(initial_date);
          var date2 = new Date(final_date);
          var diffTime = Math.abs(date2 - date1);
          var diffDays = Math.ceil(diffTime / (1000 * 60 * 60 * 24)); 

          if(initial_date == '' || final_date == ''){
              $("#error_log").html("Warning: You must select both (start and end) date.</span>");
          }else{
            if(date1 > date2){
                $("#error_log").html("Warning: End date should be greater then start date.");
            }else{
               $("#error_log").html(""); 
               $('#fetch_users').DataTable().destroy();
               load_data(initial_date, final_date, gender);
            }
          }
        }
      });

      $('.input-daterange').datepicker({
        todayBtn:'linked',
        format: "yyyy-mm-dd",
        autoclose: true
      });

    </script>
  </body>
</html>
ht

Download Script

You can download the complete script for Date range filters in server-side jQuery dataTables by clicking here.

Conclusion

With our simple guide on the Date range filters in server-side jQuery dataTables, you can not only integrate the data ranges in your dataTables but can also add user interactivity with this feature.

Related Posts