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

The end result looks like the below:

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

In this article we have also covered:

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

So, this is a bonus article!

Now, Let’s start with our main topic.

Folder structure

You are not bound to follow the exact 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. The file and folder structure for this example is:

1. Create SQL

The first step is to create a table that will hold the data for the server-side dataTable. Let’s create a table of users which 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');

2. Write HTML

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

CDN links to use for stylings and scripting:

<!-- 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>

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.

3. Write JavaScript

Write the following code after initializing the JavaScript libraries, goes 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>

4. Write PHP

Let’s create a database connection. Create a folder config and add a file db-config.php in it, then add the following piece of 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 dataTable:

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

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

Complete index.php code:

<?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 using PHP and AJAX</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

You can download the complete script here.

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

Folder structure

1. Create SQL

2. Write HTML

3. Write JavaScript

4. Write PHP

Proper Handling of MySQLi Queries in PHP: Opening, Closing, and Troubleshooting

How to display available payment gateways for your WooCommerce website

How to optimize a WordPress website

How to Secure a WordPress website

How to enable mouse swipe or drag on the bootstrap carousel

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

Folder structure

1. Create SQL

2. Write HTML

3. Write JavaScript

4. Write PHP

Related Posts

Proper Handling of MySQLi Queries in PHP: Opening, Closing, and Troubleshooting

How to display available payment gateways for your WooCommerce website