Date range search with jQuery Datepicker using Ajax, PHP & MySQL
In this article, we’ll discuss the date range search with jQuery Datepicker using Ajax, PHP and MySQL.
Here, we will see how to search data from a MySQL database based on the datetime" title="DateTime">date between two given ranges. We are using jQuery Datepicker to select dates for filter the data.
You may also like following articles related to AJAX.
- file-upload-using-jquery-ajax-and-php" title="Drag and drop multiple file upload using jQuery, Ajax, and PHP">Drag and drop multiple file upload using jQuery, Ajax, and PHP
- Dynamic dependent select box using jQuery, Ajax, and PHP
- Ajax POST request with jQuery and PHP
Demo Example
Output - Date range search with jQuery Datepicker using Ajax, PHP & MySQL - Clue Mediator
Steps to implement Date Range Search with jQuery DatePicker
- table in database">Create table in database
- Database connection
- Create HTML page
- Add Ajax call
- Create PHP action file
- Output
File Structure
-
date-range-search-php
- action.php
- db_config.php
- index.php
1. Create table in database
First, we will create a table named `orders` in the database. Run the following script to create a table with dummy records in the database.
# Create orders table
CREATE TABLE `orders` (
order_number int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
customer_name varchar(255) NOT NULL,
purchased_items varchar(255) NOT NULL,
price double(12,2) NOT NULL,
purchased_date date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
# Inserting data in the order table
INSERT INTO `orders` (`order_number`, `customer_name`, `purchased_items`, `price`, `purchased_date`) VALUES
(1, 'John Derosa', 'iPhone', 745.00, '2020-11-05'),
(2, 'Oscar Thomas', 'CCTV Camera', 956.00, '2020-11-12'),
(3, 'Karen Victory', 'Glass Block', 250.00, '2020-11-16'),
(4, 'David McGee', 'Shuttering Plywood', 1450.00, '2020-11-23'),
(5, 'Ronald Gary', 'CCTV Camera', 765.00, '2020-12-10'),
(6, 'Jessica Hunter', 'Casing Pipes', 600.00, '2020-12-14'),
(7, 'Helena Sorensen', 'Apple Watch', 590.00, '2020-12-23'),
(8, 'Lori Hunter', 'Glass PVC Rubber', 1700.00, '2020-12-25'),
(9, 'Diane Nathan', 'Microwave', 200.00, '2021-01-01'),
(10, 'Oscar Jury', 'Macbook', 1600.00, '2021-01-08');
2. Database connection
We have to create a `db_config.php` file and add the following code to connect the database.
db_config.php
<!--?php
$dbhost = "localhost"; // host name
$dbuser = "root"; // username
$dbpass = ""; // password
$db = "demo"; // database name
$con = mysqli_connect($dbhost, $dbuser, $dbpass , $db) or die($con);
?-->
3. Create HTML page
Now, we will create a file named `index.php` where two input fields for the date are shown.
When the search button is clicked, the Ajax request is sent to the `action.php` file. The PHP script will filter the data based on the start date and end date using MySQL query with `between` cause. So the filtered data will be shown on the web page without refreshing the page using Ajax.
index.php
<!--?php
// include database connection file
include('db_config.php');<p-->
$query = "SELECT * FROM orders ORDER BY order_number desc";
$result = mysqli_query($con, $query);
?>
<title>Date range search with jQuery Datepicker using Ajax, PHP & MySQL - Clue Mediator</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.css">
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.4.0/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"></script>
<div class="container">
<h4>Date range search with jQuery Datepicker using Ajax, PHP & MySQL - <a href="https://www.cluemediator.com/" target="_blank" rel="noopener noreferrer">Clue Mediator</a></h4>
<br>
<div class="row">
<div class="col-md-2">
<input type="text" name="from_date" id="from_date" class="form-control dateFilter" placeholder="From Date">
</div>
<div class="col-md-2">
<input type="text" name="to_date" id="to_date" class="form-control dateFilter" placeholder="To Date">
</div>
<div class="col-md-2">
<input type="button" name="search" id="btn_search" value="Search" class="btn btn-primary">
</div>
</div>
<br>
<div class="row">
<div class="col-md-8">
<div id="purchase_order">
<table class="table table-bordered">
<tbody><tr>
<th width="5%">Order Number</th>
<th width="30%">Customer Name</th>
<th width="40%">Item</th>
<th width="15%">Price</th>
<th width="10%">Purchased Date</th>
</tr>
<!--?php
while($row = mysqli_fetch_array($result))
{
?-->
<tr>
<td><!--?php echo $row["order_number"]; ?--></td>
<td><!--?php echo $row["customer_name"]; ?--></td>
<td><!--?php echo $row["purchased_items"]; ?--></td>
<td>$ <!--?php echo $row["price"]; ?--></td>
<td><!--?php echo $row["purchased_date"]; ?--></td>
</tr>
<!--?php
}
?-->
</tbody></table>
</div>
</div>
</div>
</div>
4. Add Ajax call
Let’s add the following code in the `index.php` file that sends `from_date` and `to_date` to the server-side script via Ajax request when the search button is clicked.
Get the response from the `action.php` file in table format and display the HTML data to the div element.
<script>
$(document).ready(function () {
$('.dateFilter').datepicker({
dateFormat: "yy-mm-dd"
});
$('#btn_search').click(function () {
var from_date = $('#from_date').val();
var to_date = $('#to_date').val();
if (from_date != '' && to_date != '') {
$.ajax({
url: "action.php",
method: "POST",
data: { from_date: from_date, to_date: to_date },
success: function (data) {
$('#purchase_order').html(data);
}
});
}
else {
alert("Please Select the Date");
}
});
});
</script>
5. Create PHP action file
At last, we will create a file named `action.php`. The Ajax request is sent to this PHP file and then filter the data from the database based on the request id (from_date and to_date).
action.php
<!--?php
// include database connection file
include('db_config.php');<p-->
if(isset($_POST["from_date"], $_POST["to_date"])) {
$orderData = "";
$query = "SELECT * FROM orders WHERE purchased_date BETWEEN '".$_POST["from_date"]."' AND '".$_POST["to_date"]."'";
$result = mysqli_query($con, $query);
$orderData .='
';
if(mysqli_num_rows($result) > 0)
{
while($row = mysqli_fetch_array($result))
{
$orderData .='
';
}
}
else
{
$orderData .= '
';
}
$orderData .= '<table class="table table-bordered">
<tbody><tr>
<th width="5%">Order Number</th>
<th width="30%">Customer Name</th>
<th width="40%">Item</th>
<th width="15%">Price</th>
<th width="10%">Purchased Date</th>
</tr><tr>
<td>'.$row["order_number"].'</td>
<td>'.$row["customer_name"].'</td>
<td>'.$row["purchased_items"].'</td>
<td>'.$row["price"].'</td>
<td>'.$row["purchased_date"].'</td>
</tr><tr>
<td colspan="5">No Order Found</td>
</tr></tbody></table>';
echo $orderData;
}
?>
6. Output
Run the project and check the output in the browser.
That’s it for today.
Thank you for reading. Happy Coding..!! 🙂
