11-Apr-2023
.
Admin
-0-min.png)
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....
#PHP
#Jquery
#Jqury UI
