Hi friends, In this post we will discuss how to create a PHP script to find dates in MySQL between two dates without refreshing the page with jQuery to select dates with PHP and Ajax. Here we will look for data from the MySQL database based on the date between two specific time periods. When we select the date range, than request is sent to the ajax method and the ajax method sends the request to the PHP script and then the data to Search the PHP script from the database based on the two given dates and return back to the Ajax method.
We can see the filtering of data on the web page without refreshing the page. Here we are using the jquery date picker to select a date for the search options. Based on these two dates, we use these one date in our database query with between clauses and filter the dates by two time periods. Here we are creating a simple PHP script called by Ajax functions. Assuming we do not select both or either of these dates, the user information will not be filtered and an alert will be returned. Suppose we select both dates and then filter users dates between date and two dates. it will run the JQuery code where we make a request to ajax. It sends a request to the PHP script and the PHP script is executed on the server side and looks up data from the customers table based on the date we selected the JQuery option on that date. After the PHP script sends data back to the Ajax method in HTML table format, and although jquery shows what data we received from the server in HTML format, the page view is displayed without any page update events.
Table of Contents
See also
Here you only need to take a few steps to proceed.
- Create Database and table
- Create Database Connection with MySQL
- Create HTML file and jQuery AJAX
- Create PHP code file to send AJAX request
Step 1 : Create database and table
SQL Query:
--
-- Database: `testing`
-- Table structure for table `customers`
--
CREATE TABLE `customers` (
`id` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`email` varchar(50) NOT NULL,
`username` varchar(50) NOT NULL,
`dob` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
--
-- Dumping data for table `customers`
--
INSERT INTO `customers` (`id`, `name`, `email`, `username`, `dob`) VALUES
(1, 'michel tyson', 'micheltyson7@gmail.com', 'tyson123', '2020-11-16'),
(2, 'seth', 'seth7@gmail.com', 'seth23', '2020-11-30'),
(3, 'roman', 'roman7@gmail.com', 'roman45', '2020-11-20'),
(4, 'john', 'john12@gmail.com', 'john12', '2020-11-30'),
(5, 'johnny smith', 'johnnysmith12@gmail.com', 'johnnysmith12', '2020-11-19'),
(6, 'smith', 'smith12@gmail.com', 'smith123', '2020-11-25'),
(7, 'deo smith', 'deosmith12@gmail.com', 'deosmith12', '2020-10-20');
Step 2 : Create Database Connection with MySQL
<?php
// Database configuration
$hostname = "localhost";
$username = "root";
$password = "";
$dbname = "testing";
// Create database connection
$conn = mysqli_connect($hostname, $username, $password, $dbname);
// Check connection
if(mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
exit();
}
?>
Step 3 : Create HTML file and jQuery AJAX
<!DOCTYPE html>
<html lang="en">
<head>
<title>Filter or Search with Date range using jQuery AJAX with PHP Mysql</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<link rel="stylesheet" type="text/css" href="https://cdn.jsdelivr.net/npm/daterangepicker/daterangepicker.css" />
</head>
<body>
<div class="card text-center" style="padding:20px;">
<h3>Filter or Search with Date Range using jQuery AJAX with PHP Mysql</h3>
</div><br>
<div class="container">
<div class="row">
<div class="col-md-3"></div>
<div class="col-md-6">
<div class="form-group">
<label for="dob"><b>Date of Birth Range</b></label>
<input type="text" name="daterange" class="form-control" placeholder="Date of Birth Range" />
</div>
</div>
<div class="col-md-12 col-sm-12">
<table class="table table-striped" id="tableData">
<thead>
<tr>
<th>S.no</th>
<th>Name</th>
<th>Email</th>
<th>Date of birth</th>
<th>Username</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</div>
</div>
<script type="text/javascript" src="https://cdn.jsdelivr.net/jquery/latest/jquery.min.js"></script>
<script type="text/javascript" src="https://cdn.jsdelivr.net/momentjs/latest/moment.min.js"></script>
<script type="text/javascript" src="https://cdn.jsdelivr.net/npm/daterangepicker/daterangepicker.min.js"></script>
</body>
</html>
<script type="text/javascript">
$(document).ready(function(){
var fromDate = "";
var toDate = "";
getDateRangeRecord(fromDate,toDate);
$('input[name="daterange"]').on('apply.daterangepicker', function(ev, picker) {
$(this).val(picker.startDate.format('YYYY-MM-DD') + ' to ' + picker.endDate.format('YYYY-MM-DD'));
});
$('input[name="daterange"]').on('cancel.daterangepicker', function(ev, picker) {
$(this).val('');
});
$('input[name="daterange"]').daterangepicker({
opens: 'center',
autoUpdateInput: false,
},
// From to To date range function
function(start, end) {
var fromDate = start.format('YYYY-MM-DD');
var toDate = end.format('YYYY-MM-DD');
if(fromDate !== "" && toDate !== "") {
getDateRangeRecord(fromDate,toDate);
}
});
// jQuery AJAX code
function getDateRangeRecord(fromDate, toDate){
$.ajax({
url : "get_data.php",
type : "POST",
cache: false,
data : {fromDate:fromDate, toDate:toDate},
success:function(result){
$("#tableData tbody").html(result);
}
});
}
});
</script>
Output
Step 4 : Create PHP code file to send AJAX request
<?php
// Include database connectivity
include_once('config.php');
if (!empty($_POST['fromDate']) && !empty($_POST['toDate'])) {
$fromDate = $_POST['fromDate'];
$toDate = $_POST['toDate'];
$query = "SELECT * FROM customers WHERE dob BETWEEN '{$fromDate}' AND '{$toDate}'";
}else{
$query = "SELECT * FROM customers ORDER BY id ASC";
}
$result = mysqli_query($conn, $query);
$output = "";
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
$output.="<tr align='text-center'>
<td>{$row["id"]}</td>
<td>{$row["name"]}</td>
<td>{$row["email"]}</td>
<td>{$row["dob"]}</td>
<td>{$row["username"]}</td>
</tr>";
}
echo $output;
}else{
echo "<h3>No record found</h3>";
}
?>
