PHP MySQL Date Range Search with jQuery DatePicker

Hi Guys,

In this tutorial,I will learn you how to use jquery datepicker to filter records with php mysql.you can easy and simply use jquery datepicker to filter records with php mysql.

we are using jQuery DatePicker to choose the dates for the search options. These date inputs are used to form a database query to read rows within two dates by the use of BETWEEN clause.

1. Table structure

In this example,I am using employee table

 
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.

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

In this step,you can use datepicker,filter in this file. Display records from employee table and list in the table.

 
<?php  
include "config.php"; 
?> 
<!doctype html> 
<html> 
 <body > 
   <!-- CSS --> 
   <link href="jquery-ui.min.css" target="_blank" rel="nofollow"  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>