in this tutorial we are going to discuss search by date range in PHP MySQL using jQuery DatePicker
In this example, We will discuss you how to use jquery datepicker to filter data with PHP & mysql.You can easily and simply use jquery datepicker to filter data with PHP mysql.
We are using jQuery DatePicker to choose the records for the search options. These date inputs are used to form, a DB query to read data rows within two dates by the use of BETWEEN clause.
1. Table structure
In this tutorial, we are 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. Database 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 HTML file. Display data from employee table and list in the HTML 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>
Have Fun!
0 Comments