Creating a Custom Date Range Filter in DataTables using JavaScript Tutorial

Submitted by oretnom23 on Friday, March 17, 2023 - 15:19.

In this tutorial, you can learn how to create a Custom Date Range Filter in DataTables using JavaScript and DataTables Library's APIs. The tutorial aims to provide students and beginners with a reference for learning some techniques of using DataTables Library. Here, I will be providing a simple web page script that demonstrates the implementation of a custom date range in DataTables.

What is DataTables?

The DataTables is a jQuery JS Library Plugin for HTML Table. It is a very adaptable tool that adds all of these cutting-edge features to any HTML table and is based on the principles of progressive improvement.

What is a Date Range?

Date Range is a website or web application input field that is composed of dates from and to. These fields are often used for filtering the data to be shown on the user interface which has date data between the given 2 dates.

How to Create a Custom Date Range Filter in DataTable?

The Custom Date Range can be added to the DataTables container wrapper in many ways. We can even simply add the date input elements on the HTML script. Using the DataTables built-in APIs and event listener we can join the date range input values to the queries for getting the data using the Server-Side processing and Ajax API. We can simply add an event listener to the date range input field to update the dataTables data each time the date input value is updated. Check out the sample web application source code script that I created for this tutorial which demonstrates the creation of a custom date range for DataTables.

Sample Web Application

The script that will be provided below results in a simple web page that contains a table that is initiated with the DataTables plug-in/library. To test the source code on your local machine, kindly download and install a virtual web server with Apache and MySQL such as XAMPP/WAMP.

Database Schema

The sample web application I created using the following Database Schema and sample data. Open your PHPMyAdmin in your preferred browser and create a new database named sample_db. Then execute the following MySQL script on the created database.

  1. -- phpMyAdmin SQL Dump
  2. -- version 5.1.3
  3. -- https://www.phpmyadmin.net/
  4. --
  5. -- Host: 127.0.0.1
  6. -- Generation Time: Mar 17, 2023 at 06:20 AM
  7. -- Server version: 10.4.24-MariaDB
  8. -- PHP Version: 8.1.5
  9.  
  10. SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
  11. START TRANSACTION;
  12. SET time_zone = "+00:00";
  13.  
  14. --
  15. -- Database: `sample_db`
  16. --
  17.  
  18. -- --------------------------------------------------------
  19.  
  20. --
  21. -- Table structure for table `members`
  22. --
  23.  
  24. CREATE TABLE `members` (
  25.     `id` mediumint(8) UNSIGNED NOT NULL,
  26.     `name` varchar(255) DEFAULT NULL,
  27.     `phone` varchar(100) DEFAULT NULL,
  28.     `email` varchar(255) DEFAULT NULL,
  29.     `date` varchar(255) DEFAULT NULL
  30. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  31.  
  32. --
  33. -- Dumping data for table `members`
  34. --
  35.  
  36. INSERT INTO `members` (`id`, `name`, `phone`, `email`, `date`) VALUES
  37. (1, 'Baker Heath', '1-784-933-4683', '[email protected]', '2017-01-02'),
  38. (2, 'Kibo Stephens', '1-340-240-6121', '[email protected]', '2021-10-09'),
  39. (3, 'Nicholas Fernandez', '(186) 434-2555', '[email protected]', '2022-03-14'),
  40. (4, 'Sylvester Slater', '(619) 460-3443', '[email protected]', '2022-11-11'),
  41. (5, 'Jaquelyn Barrett', '(352) 396-1466', '[email protected]', '2018-10-21'),
  42. (6, 'Phoebe Randall', '1-826-955-4477', '[email protected]', '2019-11-11'),
  43. (7, 'Iris Macias', '1-943-873-1528', '[email protected]', '2018-03-09'),
  44. (8, 'Rinah Harvey', '1-551-577-4076', '[email protected]', '2022-07-20'),
  45. (9, 'Courtney Cherry', '(295) 206-2921', '[email protected]', '2016-03-06'),
  46. (10, 'Kareem Kirby', '1-503-719-8248', '[email protected]', '2015-08-18'),
  47. (11, 'Martena Mosley', '1-627-463-2797', '[email protected]', '2017-07-09'),
  48. (12, 'Sandra Huff', '(648) 366-2417', '[email protected]', '2021-09-15'),
  49. (13, 'Lacota Jarvis', '1-296-566-7370', '[email protected]', '2019-08-05'),
  50. (14, 'Troy Roberts', '1-582-842-4361', '[email protected]', '2021-12-17'),
  51. (15, 'Wyoming Stout', '1-632-575-0298', '[email protected]', '2016-02-24'),
  52. (16, 'Duncan England', '(384) 653-8133', '[email protected]', '2022-01-21'),
  53. (17, 'Paloma Levy', '(164) 335-6535', '[email protected]', '2021-04-23'),
  54. (18, 'Sean Bernard', '1-851-870-3602', '[email protected]', '2022-04-17'),
  55. (19, 'Flynn Baxter', '(655) 301-5569', '[email protected]', '2017-12-29'),
  56. (20, 'Leila Carson', '1-236-562-4622', '[email protected]', '2021-08-20'),
  57. (21, 'Alden Delacruz', '(588) 786-2310', '[email protected]', '2021-04-12'),
  58. (22, 'Allen Sosa', '(333) 766-5334', '[email protected]', '2018-09-21'),
  59. (23, 'Drake Pierce', '(972) 388-3405', '[email protected]', '2016-08-27'),
  60. (24, 'Judith England', '1-868-529-3508', '[email protected]', '2016-03-15'),
  61. (25, 'Giacomo Sparks', '(648) 696-8659', '[email protected]', '2018-02-09'),
  62. (26, 'Barry Roberson', '1-249-272-0470', '[email protected]', '2018-02-16'),
  63. (27, 'Yael Lara', '(100) 545-4384', '[email protected]', '2015-11-09'),
  64. (28, 'Tasha Baird', '(472) 642-3907', '[email protected]', '2022-03-04'),
  65. (29, 'Jesse Mccormick', '1-167-456-6862', '[email protected]', '2023-01-31'),
  66. (30, 'Travis Alston', '(172) 703-1864', '[email protected]', '2020-08-27'),
  67. (31, 'Rina Shepherd', '1-845-244-1380', '[email protected]', '2016-08-19'),
  68. (32, 'Nicole Malone', '(767) 394-8231', '[email protected]', '2018-03-31'),
  69. (33, 'Allistair Hughes', '1-887-888-4564', '[email protected]', '2019-02-28'),
  70. (34, 'Phelan Barrett', '1-691-835-5229', '[email protected]', '2016-11-21'),
  71. (35, 'Raven Gilmore', '1-556-961-3217', '[email protected]', '2019-02-15'),
  72. (36, 'Kasimir Woodward', '1-311-213-7102', '[email protected]', '2020-07-09'),
  73. (37, 'Kerry Oneal', '(435) 548-5392', '[email protected]', '2021-01-22'),
  74. (38, 'Jessamine Bradley', '(138) 769-7616', '[email protected]', '2018-11-06'),
  75. (39, 'Zachery Cherry', '1-428-713-7784', '[email protected]', '2020-01-02'),
  76. (40, 'Xavier Bush', '1-503-760-7665', '[email protected]', '2022-05-24'),
  77. (41, 'Karina Richards', '(733) 381-9364', '[email protected]', '2018-08-31'),
  78. (42, 'Troy Lancaster', '1-877-683-3143', '[email protected]', '2018-03-04'),
  79. (43, 'Logan Valencia', '1-479-653-6698', '[email protected]', '2019-05-07'),
  80. (44, 'Courtney Henson', '(726) 534-2276', '[email protected]', '2016-07-14'),
  81. (45, 'Zenaida Mckenzie', '1-418-655-5252', '[email protected]', '2021-10-03'),
  82. (46, 'Maxine Huber', '1-801-978-8488', '[email protected]', '2016-03-06'),
  83. (47, 'Lester Harrington', '(405) 638-9236', '[email protected]', '2022-11-14'),
  84. (48, 'Maya Gilliam', '(681) 813-8849', '[email protected]', '2016-07-11'),
  85. (49, 'Cooper Kim', '(600) 174-8372', '[email protected]', '2016-09-09'),
  86. (50, 'Wilma Melton', '1-849-103-0694', '[email protected]', '2018-07-28');
  87.  
  88. --
  89. -- Indexes for dumped tables
  90. --
  91.  
  92. --
  93. -- Indexes for table `members`
  94. --
  95. ALTER TABLE `members`
  96.     ADD PRIMARY KEY (`id`);
  97.  
  98. --
  99. -- AUTO_INCREMENT for dumped tables
  100. --
  101.  
  102. --
  103. -- AUTO_INCREMENT for table `members`
  104. --
  105. ALTER TABLE `members`
  106.     MODIFY `id` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=51;

Database Connection

Next, the below is script is a PHP file script that handles the connection between the application and the database.

  1. <?php
  2. //Database Credentials
  3. $host = "localhost";
  4. $username = "root";
  5. $pw = "";
  6. $dbname = "sample_db";
  7.  
  8. // Try to connect to database using the given DB credentials
  9. try{
  10.     $conn = new MySQLi($host, $username, $pw, $dbname);
  11.     if(!$conn){
  12.     throw new ErrorException("Database Connection:". $conn->error, $conn->errno);
  13.     }
  14. } catch (Exception $e){
  15.     // Echo return message
  16.     print($e->getMessage());
  17.     die;
  18. }

Page Interface

Here's the HTML file script that contains the page layout and table element on the web page. The file is known as index.html. Note, the Bootstrap Framework, jQuery, and DataTables assets are loaded using CDN which requires an internet connection upon running even locally.

  1. <!DOCTYPE html>
  2. <html lang="en">
  3. <head>
  4.     <meta charset="UTF-8">
  5.     <meta http-equiv="X-UA-Compatible" content="IE=edge">
  6.     <meta name="viewport" content="width=device-width, initial-scale=1.0">
  7.     <title>DataTables with Custom Date Range Filter</title>
  8.     <link rel="preconnect" href="https://fonts.googleapis.com">
  9.     <link rel="preconnect" href="https://fonts.gstatic.com" crossorigin>
  10.     <link rel="stylesheet" href="https://fonts.googleapis.com/css2?family=Material+Symbols+Outlined:opsz,wght,FILL,[email protected],100..700,0..1,-50..200" />
  11.     <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css">
  12.     <link rel="stylesheet" href="https://cdn.datatables.net/1.13.4/css/jquery.dataTables.min.css">
  13.     <link rel="stylesheet" href="style.css">
  14.     <script src="https://code.jquery.com/jquery-3.6.4.min.js" integrity="sha256-oP6HI9z1XaZNBrJURtCoUT5SUnxFr8s3BzRl+cbzUq8=" crossorigin="anonymous"></script>
  15.     <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js"></script>
  16.     <script src="https://cdn.datatables.net/1.13.4/js/jquery.dataTables.min.js"></script>
  17. </head>
  18. <body>
  19.     <div class="content-md-lg py-3">
  20.         <div class="page-title">DataTables with Custom Date Range Filter</div>
  21.         <hr style="margin:auto; width:25px">
  22.         <div class="col-lg-8 col-md-10 col-sm-12 col-12 mx-auto pt-4">
  23.             <div class="card shadow rounded-0">
  24.                 <div class="card-body">
  25.                     <div class="rounded-0">
  26.                         <div class="container-fluid">
  27.                             <!-- Custom Date Range Inputs Wrapper -->
  28.                             <div class="row justify-content-center align-items-center flex-wrap mb-2">
  29.                                 <div class="col-auto">
  30.                                     <label class="form-label text-body h6">Filter Date Registered</label>
  31.                                 </div>
  32.                                 <div class="col-12 col-lg-3 col-md-4 col-sm-12">
  33.                                     <input type="date" name="registered_from" class="form-control form-control-sm rounded-0">
  34.                                 </div>
  35.                                 <div class="col-auto">
  36.                                     <label class="form-label text-body h6">-</label>
  37.                                 </div>
  38.                                 <div class="col-12 col-lg-3 col-md-4 col-sm-12">
  39.                                     <input type="date" name="registered_to" class="form-control form-control-sm rounded-0">
  40.                                 </div>
  41.                             </div>
  42.                             <!-- Custom Date Range Inputs Wrapper -->
  43.  
  44.                             <!-- Data Table -->
  45.                             <table class="table table-sm table-hover table-bordered table-striped" id="membersTbl">
  46.                                 <thead>
  47.                                     <tr>
  48.                                         <th class="text-center">Date Registered</th>
  49.                                         <th class="text-center">Name</th>
  50.                                         <th class="text-center">Email</th>
  51.                                         <th class="text-center">Phone</th>
  52.                                     </tr>
  53.                                 </thead>
  54.                                 <tbody></tbody>
  55.                             </table>
  56.                             <!-- Data Table -->
  57.                         </div>
  58.                     </div>
  59.                 </div>
  60.             </div>
  61.         </div>
  62.     </div>
  63.     <script src="script.js"></script>
  64. </body>
  65. </html>

Stylesheet

Here is the CSS script of the custom design of the web page.

  1. @import url('https://fonts.googleapis.com/css2?family=Courgette&family=Secular+One&display=swap" rel="stylesheet');
  2. :root{
  3.     --secular-font: 'Secular One', sans-serif;
  4. }
  5. *{
  6.     box-sizing: border-box;
  7. }
  8. body *{
  9.     font-family: 'Rubik', sans-serif;
  10. }
  11. /**
  12. Page Design
  13. */
  14. body,
  15. html{
  16.     height: 100%;
  17.     width: 100%;
  18.     margin: 0;
  19.     padding: 0;
  20. }
  21. body{
  22.     background-color: #B9F3E4;
  23. }
  24. .page-title{
  25.     font-size: 2.5rem;
  26.     font-weight: 500;
  27.     color: #fff;
  28.     letter-spacing: 3px;
  29.     font-family: var(--secular-font);
  30.     text-align: center;
  31.     text-shadow: 0px 0px 3px #2020208c;
  32. }
  33. .table-bordered>:not(caption)>*>* {
  34.     border-width: 1px var(--bs-border-width);
  35. }
  36. .dataTables_wrapper>div{
  37.     padding-bottom: .5em !important;
  38. }

JavaScript

Here's the JS file script that contains the codes that initiate dataTables and makes the custom date range input field functional.

  1. var membersDT;
  2. $(document).ready(function(){
  3.         /** 
  4.             * Initializing DataTable
  5.             * Load Data using Ajax
  6.         */
  7.         membersDT = $('#membersTbl').DataTable({
  8.                         processing: true,
  9.                         serverSide: true,
  10.                         columns:[
  11.                             {
  12.                                 width:"20%",
  13.                                 data:'date'
  14.                             },
  15.                             {
  16.                                 width:"40%",
  17.                                 data:'name'
  18.                             },
  19.                             {
  20.                                 width:"20%",
  21.                                 data:'email'
  22.                             },
  23.                             {
  24.                                 width:"20%",
  25.                                 data:'phone'
  26.                             }
  27.                         ],
  28.                         ajax: {
  29.                             method:'POST',
  30.                             url:'dt-query.php',
  31.                             data: {'registered_from' : $('input[name="registered_from"]').val(), 'registered_to' : $('input[name="registered_to"]').val() }
  32.                         },
  33.                         lengthMenu: [ [20, 50,  -1], [20, 50, "All"] ]
  34.                     });
  35.  
  36.     membersDT.on('draw.dt', function(){
  37.         /**
  38.             * Add Event Listener to the custom inputs
  39.             */
  40.         $('input[name="registered_from"], input[name="registered_to"]').change(function(){
  41.                 //Update Ajax data
  42.                 membersDT.context[0].ajax.data = {'registered_from' : $('input[name="registered_from"]').val(), 'registered_to' : $('input[name="registered_to"]').val() }
  43.                 //Update DataTable data
  44.                 membersDT.draw();
  45.         })
  46.     })
  47. })

PHP Query

Here's the PHP file script that contains the source code for retrieving the data and filtered data from the database.

  1. <?php 
  2. //Include Database Connection file script
  3. include_once('db-connect.php');
  4. extract($_POST);
  5.  
  6. //Table Column Name for oredring
  7. $columns_arr = ["abs(unix_timestamp(`date`))", "`name`", "`email`", "`phone`"];
  8.  
  9. //Overall data rows count
  10. $total = $conn->query("SELECT id FROM `members`")->num_rows;
  11.  
  12. $where = "";
  13. // Filter Statement if both Date Inputs are not empty
  14. if(!empty($registered_from) && !empty($registered_to)){
  15.     if(empty($where))
  16.         $where .= " where ";
  17.     $where .= " date_format(`date`, '%Y-%m-%d') BETWEEN '{$registered_from}' AND '{$registered_to}' ";
  18. }
  19.  
  20. // Filter Statement if search value is not empty
  21. if(!empty($search['value'])){
  22.     if(empty($where))
  23.         $where .= " where ";
  24.     else
  25.         $where .= " and ";
  26.         $where .= " (date_format(`date`, '%F %d, %Y') LIKE '%{$search['value']}%' ";
  27.         $where .= "OR `name` LIKE '%{$search['value']}%' ";
  28.         $where .= "OR `email` LIKE '%{$search['value']}%' ";
  29.         $where .= "OR `phone` LIKE '%{$search['value']}%') ";
  30. }
  31.  
  32. // Query Order Statement
  33. $orderBy = " ORDER BY {$columns_arr[$order[0]['column']]} {$order[0]['dir']} ";
  34. // Query Order length or Limit Statement
  35. $limit = " LIMIT {$length} ";
  36. // Query Order start or offset Statement
  37. $offset = " OFFSET {$start} ";
  38.  
  39. // default Filtered Data Row Value
  40. $totalFiltered = 0;
  41.  
  42. // Data Query Statement
  43. $sql = "SELECT * FROM `members` {$where}";
  44.  
  45. //Execution of Query with order, limit, and offset
  46. $qry = $conn->query($sql. "{$orderBy} {$limit} {$offset}");
  47.  
  48. //Execution of Query with with where clause only
  49. $qryFiltered = $conn->query($sql);
  50.  
  51. //Total Filtered Data Rows
  52. $totalFiltered =$qryFiltered->num_rows;
  53.  
  54. // Default Data value
  55. $data = [];
  56. if($qry->num_rows > 0){
  57.     // Data value
  58.     $data =$qry->fetch_all(MYSQLI_ASSOC);
  59.     foreach($data as $k => $row){
  60.         // Update Date Format
  61.         $data[$k]['date'] = date("F d, Y", strtotime($row['date']));
  62.     }
  63. }
  64. // Return the JSON data to datatables
  65. echo json_encode([
  66.     "draw" => $draw,
  67.     "recordsTotal" => $total,
  68.     "recordsFiltered" => $totalFiltered,
  69.     "data" => $data
  70. ]);
  71. // close database connection
  72. $conn->close();
  73. ?>

Snapshots

Here are the snapshots of the overall result of the script I provided above.

DataTables Output

DataTables with Custom Date Range Filter

Filtered DataTables Output

DataTables with Custom Date Range Filter

There you go! I have also provided the complete source code zip file on this website and it is free to download. The download button is located below this tutorial's content. Feel free to download and do some experiments to enhance your knowledge of using DataTables Library.

That's it! I hope this Creating a Custom Date Range Filter in DataTables using JavaScript Tutorial will help you with what you are looking for and will be useful for your current and future web application projects.

Explore more on this website for more Tutorials and Free Source Codes.

Happy Coding =)

Tags

Comments

Add new comment

About text formats