Skip to content

All topics  /  jQuery

PHP MySQL Date Range Search with jQuery DatePicker

jQuery ·

When “PHP MySQL Date Range Search with jQuery DatePicker” moves through design, development and browser testing, this operations example can clarify hand-offs and time spent on revisions while leaving code quality, accessibility and released behaviour as the real measures of success.

For API changes, browser support and current usage patterns, compare the snippet with the official jQuery website before adopting it in production.

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

It will help you....

More on this topic