jQuery Datepicker to filter records with PHP MySQL

Date filter makes it easier to list records according to specific date range selection.

The user can pick dates and search the list. Records will be displayed according to date selection.

To add datepicker you can use jQuery UI, Bootstrap, or any other jQuery plugin.

In this tutorial, I am using jQuery UI to add datepicker widget and filter the records with PHP MySQL.

Demo Download

Create a Table
Database Configuration
HTML & PHP
Demo
Conclusion

1. Create a Table

I am using employee table in the example.

Here, I defined date_of_join field of date type. Perform date filter on this field.

CREATE TABLE `employee` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `emp_name` varchar(70) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `date_of_join` date NOT NULL,
  `email` varchar(80) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Database Configuration

Create a config.php file for the database configuration.

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. HTML & PHP

Download jQuery UI from here if it does not exist in your project.

Search form

Create a search <form> which has two <input type='text' class='dateFilter'> elements and a submit button.

Initialize datepicker on $('.dateFilter') and set the date format to "yy-mm-dd".

List

Display records from employee table and list in the <table>.

Date filter

On <form > submit check the dates are selected or not if selected then prepare a query where use between for date range selection.

Completed Code

<?php 
include "config.php";
?>
<!doctype html>
<html>
 <body >
   <!-- CSS -->
   <link href='jquery-ui.min.css' rel='stylesheet' type='text/css'>

   <!-- Script -->
   <script src='jquery-3.3.1.js' type='text/javascript'></script>
   <script src='jquery-ui.min.js' type='text/javascript'></script>
   <script type='text/javascript'>
   $(document).ready(function(){
     $('.dateFilter').datepicker({
        dateFormat: "yy-mm-dd"
     });
   });
   </script>

   <!-- Search filter -->
   <form method='post' action=''>
     Start Date <input type='text' class='dateFilter' name='fromDate' value='<?php if(isset($_POST['fromDate'])) echo $_POST['fromDate']; ?>'>
 
     End Date <input type='text' class='dateFilter' name='endDate' value='<?php if(isset($_POST['endDate'])) echo $_POST['endDate']; ?>'>

     <input type='submit' name='but_search' value='Search'>
   </form>

   <!-- Employees List -->
   <div style='height: 80%; overflow: auto;' >
 
     <table border='1' width='100%' style='border-collapse: collapse;margin-top: 20px;'>
       <tr>
         <th>Name</th>
         <th>Date of Join</th>
         <th>Gender</th>
         <th>Email</th>
       </tr>

       <?php
       $emp_query = "SELECT * FROM employee WHERE 1 ";

       // Date filter
       if(isset($_POST['but_search'])){
          $fromDate = $_POST['fromDate'];
          $endDate = $_POST['endDate'];

          if(!empty($fromDate) && !empty($endDate)){
             $emp_query .= " and date_of_join 
                          between '".$fromDate."' and '".$endDate."' ";
          }
        }

        // Sort
        $emp_query .= " ORDER BY date_of_join DESC";
        $employeesRecords = mysqli_query($con,$emp_query);

        // Check records found or not
        if(mysqli_num_rows($employeesRecords) > 0){
          while($empRecord = mysqli_fetch_assoc($employeesRecords)){
            $id = $empRecord['id'];
            $empName = $empRecord['emp_name'];
            $date_of_join = $empRecord['date_of_join'];
            $gender = $empRecord['gender'];
            $email = $empRecord['email'];

            echo "<tr>";
            echo "<td>". $empName ."</td>";
            echo "<td>". $date_of_join ."</td>";
            echo "<td>". $gender ."</td>";
            echo "<td>". $email ."</td>";
            echo "</tr>";
          }
        }else{
          echo "<tr>";
          echo "<td colspan='4'>No record found.</td>";
          echo "</tr>";
        }
        ?>
      </table>
 
    </div>
 </body>
</html>

4. Demo

View Demo

5. Conclusion

You can use any other jQuery plugin to add datepicker on the HTML element instead of jQuery UI.

Date format must be similar to the defined format in MySQL database table otherwise you need to convert.

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

Contents

1. Create a Table

2. Database Configuration

3. HTML & PHP

4. Demo

5. Conclusion