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.
Contents
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.2
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
When form submitted first check the date range selected or not. If selected then execute the SQL query to fetch records.