PHP MySQL Date Range Search with jQuery DatePicker

PHP , jquery , Jqury UI

Nicesnippets

199

19-08-2020


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


Recommended Posts