Hello friends in this post we are going to discuss how to make php script for search mysql data between two date without page refresh by using Jquey date picker with php and Ajax. Here we will search data from mysql database based on date between two given date range and when we will click on filter button then it will send request to ajax method and by ajax method it will send request to php script and then after php script search data from database based on two given dates and send back to ajax method and we can show filter data on web page without page refresh. Here we have use jquery date picker for choose the dates for the search options and based on this two dates we will use this two dates into database query with between clause and we will filter data based on two date range. Here we have make a simple php script which has been called by ajax function. Suppose we have not select both or any one of the date then it will not filter order data and return back alert message. But suppose we have select both date then it filter order data between from date and two date. When we have click on filter button then it has been execute jquery code in which we have make ajax request and it send request to php script and php script work on server side and it search data from order table based on which date we have select through jquery date picker. Then after php script send back data to ajax method in html table format and though jquery we have display which data we have received from server side in html format we have display on we page without page refresh event.
Check Out - Online Demo
Ajax PHP MySQL Date Range Search using jQuery DatePicker
Order Data
From Date
To Date
| ID | Customer Name | Item | Value | Order Date |
|---|---|---|---|---|
| 20 | Felicia L. Sorensen | Anker 24W Dual USB Wall Charger with Foldable Plug | $ 12.00 | 2016-10-04 |
| 19 | Ronald S. Vallejo | Scotchgard Fabric Protector, 10-Ounce, 2-Pack | $ 20.00 | 2016-10-03 |
| 18 | Arlette G. Nathan | TaoTronics Bluetooth in-Ear Headphones | $ 25.00 | 2016-10-03 |
| 17 | Helena J. Quillen | TaoTronics Dimmable Outdoor String Lights | $ 16.00 | 2016-10-04 |
| 16 | Diane J. Thomas | SanDisk Ultra 32GB microSDHC | $ 12.00 | 2016-10-05 |
| 15 | Sarah D. Hunter | Westinghouse Select Kitchen Appliances | $ 35.00 | 2016-09-29 |
| 14 | Gary M. Porter | ROBO 3D R1 Plus 3D Printer | $ 600.00 | 2016-10-02 |
| 13 | Kristine B. McGraw | Tristar 10" Round Copper Chef Pan with Glass Lid | $ 20.00 | 2016-09-30 |
| 12 | Karen A. McGee | Over-the-Ear Stereo Headphones | $ 25.00 | 2016-10-01 |
| 11 | Shawn S. Derosa | Sony HTXT1 2.1-Channel TV | $ 180.00 | 2016-10-03 |
| 10 | Lori C. Skinner | Glass PVC Rubber | $ 1800.00 | 2016-09-30 |
| 9 | David C. Jury | Casing Pipes | $ 500.00 | 2016-09-27 |
| 8 | Susan C. Richardson | Glass Block | $ 200.00 | 2016-10-04 |
| 7 | Roger R. White | Truck Tires | $ 2000.00 | 2016-09-28 |
| 6 | Jessica G. Fischer | CCTV Camera | $ 800.00 | 2016-10-02 |
| 5 | Christopher M. Victory | Shopping Trolley | $ 100.00 | 2016-10-01 |
| 4 | Clara C. Kulik | Spin Driller Machine | $ 350.00 | 2016-09-30 |
| 3 | Oscar D. Scoggins | Plaster Of Paris | $ 150.00 | 2016-09-29 |
| 2 | Eddie M. Douglas | Aluminium Heavy Windows | $ 2000.00 | 2016-10-08 |
| 1 | David E. Gary | Shuttering Plywood | $ 1500.00 | 2016-10-14 |
Source Code
index.php
<?php
$connect = mysqli_connect("localhost", "root", "", "testing");
$query = "SELECT * FROM tbl_order ORDER BY order_id desc";
$result = mysqli_query($connect, $query);
?>
<!DOCTYPE html>
<html>
<head>
<title>Webslesson Tutorial | Ajax PHP MySQL Date Range Search using jQuery DatePicker</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
<script src="http://code.jquery.com/ui/1.10.3/jquery-ui.js"></script>
<link rel="stylesheet" href="http://code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">
</head>
<body>
<br /><br />
<div class="container" style="width:900px;">
<h2 align="center">Ajax PHP MySQL Date Range Search using jQuery DatePicker</h2>
<h3 align="center">Order Data</h3><br />
<div class="col-md-3">
<input type="text" name="from_date" id="from_date" class="form-control" placeholder="From Date" />
</div>
<div class="col-md-3">
<input type="text" name="to_date" id="to_date" class="form-control" placeholder="To Date" />
</div>
<div class="col-md-5">
<input type="button" name="filter" id="filter" value="Filter" class="btn btn-info" />
</div>
<div style="clear:both"></div>
<br />
<div id="order_table">
<table class="table table-bordered">
<tr>
<th width="5%">ID</th>
<th width="30%">Customer Name</th>
<th width="43%">Item</th>
<th width="10%">Value</th>
<th width="12%">Order Date</th>
</tr>
<?php
while($row = mysqli_fetch_array($result))
{
?>
<tr>
<td><?php echo $row["order_id"]; ?></td>
<td><?php echo $row["order_customer_name"]; ?></td>
<td><?php echo $row["order_item"]; ?></td>
<td>$ <?php echo $row["order_value"]; ?></td>
<td><?php echo $row["order_date"]; ?></td>
</tr>
<?php
}
?>
</table>
</div>
</div>
</body>
</html>
<script>
$(document).ready(function(){
$.datepicker.setDefaults({
dateFormat: 'yy-mm-dd'
});
$(function(){
$("#from_date").datepicker();
$("#to_date").datepicker();
});
$('#filter').click(function(){
var from_date = $('#from_date').val();
var to_date = $('#to_date').val();
if(from_date != '' && to_date != '')
{
$.ajax({
url:"filter.php",
method:"POST",
data:{from_date:from_date, to_date:to_date},
success:function(data)
{
$('#order_table').html(data);
}
});
}
else
{
alert("Please Select Date");
}
});
});
</script>
filter.php
<?php
//filter.php
if(isset($_POST["from_date"], $_POST["to_date"]))
{
$connect = mysqli_connect("localhost", "root", "", "testing");
$output = '';
$query = "
SELECT * FROM tbl_order
WHERE order_date BETWEEN '".$_POST["from_date"]."' AND '".$_POST["to_date"]."'
";
$result = mysqli_query($connect, $query);
$output .= '
<table class="table table-bordered">
<tr>
<th width="5%">ID</th>
<th width="30%">Customer Name</th>
<th width="43%">Item</th>
<th width="10%">Value</th>
<th width="12%">Order Date</th>
</tr>
';
if(mysqli_num_rows($result) > 0)
{
while($row = mysqli_fetch_array($result))
{
$output .= '
<tr>
<td>'. $row["order_id"] .'</td>
<td>'. $row["order_customer_name"] .'</td>
<td>'. $row["order_item"] .'</td>
<td>$ '. $row["order_value"] .'</td>
<td>'. $row["order_date"] .'</td>
</tr>
';
}
}
else
{
$output .= '
<tr>
<td colspan="5">No Order Found</td>
</tr>
';
}
$output .= '</table>';
echo $output;
}
?>
Database
--
-- Table structure for table `tbl_order`
--
CREATE TABLE IF NOT EXISTS `tbl_order` (
`order_id` int(11) NOT NULL AUTO_INCREMENT,
`order_customer_name` varchar(255) NOT NULL,
`order_item` varchar(255) NOT NULL,
`order_value` double(12,2) NOT NULL,
`order_date` date NOT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;
--
-- Dumping data for table `tbl_order`
--
INSERT INTO `tbl_order` (`order_id`, `order_customer_name`, `order_item`, `order_value`, `order_date`) VALUES
(1, 'David E. Gary', 'Shuttering Plywood', 1500.00, '2016-10-14'),
(2, 'Eddie M. Douglas', 'Aluminium Heavy Windows', 2000.00, '2016-10-08'),
(3, 'Oscar D. Scoggins', 'Plaster Of Paris', 150.00, '2016-09-29'),
(4, 'Clara C. Kulik', 'Spin Driller Machine', 350.00, '2016-09-30'),
(5, 'Christopher M. Victory', 'Shopping Trolley', 100.00, '2016-10-01'),
(6, 'Jessica G. Fischer', 'CCTV Camera', 800.00, '2016-10-02'),
(7, 'Roger R. White', 'Truck Tires', 2000.00, '2016-09-28'),
(8, 'Susan C. Richardson', 'Glass Block', 200.00, '2016-10-04'),
(9, 'David C. Jury', 'Casing Pipes', 500.00, '2016-09-27'),
(10, 'Lori C. Skinner', 'Glass PVC Rubber', 1800.00, '2016-09-30'),
(11, 'Shawn S. Derosa', 'Sony HTXT1 2.1-Channel TV', 180.00, '2016-10-03'),
(12, 'Karen A. McGee', 'Over-the-Ear Stereo Headphones ', 25.00, '2016-10-01'),
(13, 'Kristine B. McGraw', 'Tristar 10" Round Copper Chef Pan with Glass Lid', 20.00, '2016-09-30'),
(14, 'Gary M. Porter', 'ROBO 3D R1 Plus 3D Printer', 600.00, '2016-10-02'),
(15, 'Sarah D. Hunter', 'Westinghouse Select Kitchen Appliances', 35.00, '2016-09-29'),
(16, 'Diane J. Thomas', 'SanDisk Ultra 32GB microSDHC', 12.00, '2016-10-05'),
(17, 'Helena J. Quillen', 'TaoTronics Dimmable Outdoor String Lights', 16.00, '2016-10-04'),
(18, 'Arlette G. Nathan', 'TaoTronics Bluetooth in-Ear Headphones', 25.00, '2016-10-03'),
(19, 'Ronald S. Vallejo', 'Scotchgard Fabric Protector, 10-Ounce, 2-Pack', 20.00, '2016-10-03'),
(20, 'Felicia L. Sorensen', 'Anker 24W Dual USB Wall Charger with Foldable Plug', 12.00, '2016-10-04');
Very useful, keep going Sir.
ReplyDeletehow to limit the results at the first page (and the after date search) and have pegination like 1-25 1 2 3 4 5
ReplyDeleteNice Post, It's very helpful
ReplyDeletethanks
ReplyDeleteThe solutions which you provide are really helpful. Thank you
ReplyDeleteGood job.....go ahead
ReplyDeletehow to export the result after filter the date?
ReplyDeleteit display data of every month of a selected date
ReplyDeletecan u help to find error
ReplyDeletei want to export this filter date in csv how it possible to do please give me solution
ReplyDeleteThanks alot i really appreciate your effort man
ReplyDeleteNice job
ReplyDeleteNice job
ReplyDeleteThankYou , Very Useful & easy way to learn <3
ReplyDeleteplzzzzzzzzzz make
ReplyDeletesearch combining with date, name, n everything in data
thanka
ReplyDeleteHow to show the count of records in this script?
ReplyDeleteLoved this post you really helped me
ReplyDeletethanks
ReplyDeletecan you please do this in codeigniter
ReplyDeleteHow do you clear filter??
ReplyDeletehello i am getting sql error i hope you can help me with this mysqli_query() expects parameter 1 to be mysqli, boolean given in 5
ReplyDeleteand
mysqli_fetch_array() expects parameter 1 to be mysqli_result, null given in 42
hello, for me in local xmapp that filter section is working, but in my production server which is in AWS, it is not working, could you help me .
ReplyDeleteteşekkürlerr
ReplyDeleteerrol baykara teşekkürlerhhhhh
ReplyDeleteWarning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\web\admin\filter.php on line 22
ReplyDeleteerros ??? solve this
very helpfull info mate keep up
ReplyDeleteNice, but how do you apply style?
ReplyDeletehow to retreive the sum of those two date retrieved?
ReplyDelete