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.

jQuery Datepicker to filter records with PHP MySQL


Contents

  1. Table structure
  2. Configuration
  3. HTML & PHP
  4. Demo
  5. Conclusion

1. Table structure

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. 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 is selected or not if selected then prepare 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

Pick dates and click the Search button.


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.

9 thoughts on “jQuery Datepicker to filter records with PHP MySQL”

  1. hi thanks for sharing this post and its working fine
    but here is one thing that all my db records print on page load
    i just want only when data retrieve when i select the data range
    thanks

    Reply
  2. Helpful article, The same concept i have posted in my blog to filter the records using jquery date picker. Actually my requirement is how to disable the weekends jquery datepicker if you have any idea please suggest thanks in advance

    Reply
    • Hi Valli,
      Yes, you can. Use onSelect event on datepicker –

      $('.dateFilter').datepicker({
      dateFormat: "yy-mm-dd",
      onSelect: function(dateText) {
      console.log("date: " + this.value);
      }
      });

      Submit the form when both dates are selected.

      Reply

Leave a Comment