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.
How to Filter with Date Range
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.
See also
Filter Price Range Slider Using Jquery Ajax in PHP with Mysqli
Create database and table
In the first step we need to create a database and tables, so here I have created a database of tables “Testing” and “Customers” with identifiers and columns with names. You simply create a Users table as described in the SQL query.
SQL Query:
-- -- Database: `testing` -- Table structure for table `customers` -- CREATE TABLE `customers` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREAMENT, `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');
Create Database Connection with MySQL
Create database connection to connect with the MySQL
config.php
<?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();
}
?>Create HTML file and jQuery AJAX
Here we need to create an index.php file and I created a date fields and select date range. Here we are using the jquery date picker to select a date for the search options. we use these one date in our database query with between clauses and filter the dates by two time periods.
include date picker library and jquery library use bootstrap 4 for search filter by date range. use jquery AJAX load data with out refresh page.
index.php
<!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
Create PHP code file to send AJAX request
In this step, we will write code for search data into database customers table using Mysql query. So, we have to create a get_data.php file and put bellow code.
get_date.php
<?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) > ) {
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>";
}
?>You can always support by sharing on social media or recommending my blog to your friends and colleagues. If you have any suggestions or problems about this tutorial, please comment on the form below.😊
