jQuery Datepicker to filter records with PHP MySQL

Date filter makes 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 new config.php file.

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

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 defined format in MySQL database table otherwise you need to convert.

If you found this tutorial helpful then don't forget to share.
Spread the love
  • 2
  •  
  •  
  •  
  •  

4 Comments

  1. Ashar said:

    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

    July 13, 2018
    Reply
    • Yogesh Singh said:

      When form submitted first check the date range selected or not. If selected then execute the SQL query to fetch records.

      July 13, 2018
      Reply
  2. Arshad Ali said:

    Please help
    to fetch mysql data select only single date

    February 26, 2019
    Reply
    • Yogesh Singh said:

      Use equal to (=) to select only specific date records.

      February 27, 2019
      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *