Hi, In this post we have made a topic about Jquery Datatables, the topics discussed are How to do a date range search on Datatables which means we will filter data between two given dates and filter data in Datatables based on the two data provided. Here we will use Jquery Datatables and Ajax as operations to filter using PHP scripts. If you have used Datatables in your project then you have features like sorting data in table columns, table data search, pagination and many other features without having to write any lines of code but if you want to search date ranges then the type of function search You can't find it in the Jquery Datatables plugin. So to do a search for the Date range on the Datatables data, you have to write some additional code on the back. Datatables is a Jquery plugin that is widely used to display data in tabular format and in this case you can get build features and most programmers use this plugin for data displayed in tabular format on the browser and very easy to implement in our project. In this plugin you can get the client-side date range filter but you cannot find the server processing the date range search filter. So in this post we will discuss how to create a date range server-side search filter using PHP scripts with Jquery Ajax.
index.php
<html>
<head>
<title>Date Range Search in Datatables using PHP Ajax</title>
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
<script src="https://cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.15/js/dataTables.bootstrap.min.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.6.4/css/bootstrap-datepicker.css" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.6.4/js/bootstrap-datepicker.js"></script>
<style>
body
{
margin:0;
padding:0;
background-color:#f1f1f1;
}
.box
{
width:1270px;
padding:20px;
background-color:#fff;
border:1px solid #ccc;
border-radius:5px;
margin-top:25px;
}
</style>
</head>
<body>
<div class="container box">
<h1 align="center">Date Range Search in Datatables using PHP Ajax</h1>
<br />
<div class="table-responsive">
<br />
<div class="row">
<div class="input-daterange">
<div class="col-md-4">
<input type="text" name="start_date" id="start_date" class="form-control" />
</div>
<div class="col-md-4">
<input type="text" name="end_date" id="end_date" class="form-control" />
</div>
</div>
<div class="col-md-4">
<input type="button" name="search" id="search" value="Search" class="btn btn-info" />
</div>
</div>
<br />
<table id="order_data" class="table table-bordered table-striped">
<thead>
<tr>
<th>Order ID</th>
<th>Customer Name</th>
<th>Item</th>
<th>Value</th>
<th>Order Date</th>
</tr>
</thead>
</table>
</div>
</div>
</body>
</html>
<script type="text/javascript" language="javascript" >
$(document).ready(function(){
$('.input-daterange').datepicker({
todayBtn:'linked',
format: "yyyy-mm-dd",
autoclose: true
});
fetch_data('no');
function fetch_data(is_date_search, start_date='', end_date='')
{
var dataTable = $('#order_data').DataTable({
"processing" : true,
"serverSide" : true,
"order" : [],
"ajax" : {
url:"fetch.php",
type:"POST",
data:{
is_date_search:is_date_search, start_date:start_date, end_date:end_date
}
}
});
}
$('#search').click(function(){
var start_date = $('#start_date').val();
var end_date = $('#end_date').val();
if(start_date != '' && end_date !='')
{
$('#order_data').DataTable().destroy();
fetch_data('yes', start_date, end_date);
}
else
{
alert("Both Date is Required");
}
});
});
</script>
<?php
//fetch.php
$connect = mysqli_connect("localhost", "root", "", "testing");
$columns = array('order_id', 'order_customer_name', 'order_item', 'order_value', 'order_date');
$query = "SELECT * FROM tbl_order WHERE ";
if($_POST["is_date_search"] == "yes")
{
$query .= 'order_date BETWEEN "'.$_POST["start_date"].'" AND "'.$_POST["end_date"].'" AND ';
}
if(isset($_POST["search"]["value"]))
{
$query .= '
(order_id LIKE "%'.$_POST["search"]["value"].'%"
OR order_customer_name LIKE "%'.$_POST["search"]["value"].'%"
OR order_item LIKE "%'.$_POST["search"]["value"].'%"
OR order_value LIKE "%'.$_POST["search"]["value"].'%")
';
}
if(isset($_POST["order"]))
{
$query .= 'ORDER BY '.$columns[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].'
';
}
else
{
$query .= 'ORDER BY order_id DESC ';
}
$query1 = '';
if($_POST["length"] != -1)
{
$query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$number_filter_row = mysqli_num_rows(mysqli_query($connect, $query));
$result = mysqli_query($connect, $query . $query1);
$data = array();
while($row = mysqli_fetch_array($result))
{
$sub_array = array();
$sub_array[] = $row["order_id"];
$sub_array[] = $row["order_customer_name"];
$sub_array[] = $row["order_item"];
$sub_array[] = $row["order_value"];
$sub_array[] = $row["order_date"];
$data[] = $sub_array;
}
function get_all_data($connect)
{
$query = "SELECT * FROM tbl_order";
$result = mysqli_query($connect, $query);
return mysqli_num_rows($result);
}
$output = array(
"draw" => intval($_POST["draw"]),
"recordsTotal" => get_all_data($connect),
"recordsFiltered" => $number_filter_row,
"data" => $data
);
echo json_encode($output);
?>
-- -- Database: `testing` -- -- -------------------------------------------------------- -- -- Table structure for table `tbl_order` -- CREATE TABLE `tbl_order` ( `order_id` int(11) NOT NULL, `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 ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- 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, '2017-01-14'), (2, 'Eddie M. Douglas', 'Aluminium Heavy Windows', 2000.00, '2017-01-08'), (3, 'Oscar D. Scoggins', 'Plaster Of Paris', 150.00, '2016-12-29'), (4, 'Clara C. Kulik', 'Spin Driller Machine', 350.00, '2016-12-30'), (5, 'Christopher M. Victory', 'Shopping Trolley', 100.00, '2017-01-01'), (6, 'Jessica G. Fischer', 'CCTV Camera', 800.00, '2017-01-02'), (7, 'Roger R. White', 'Truck Tires', 2000.00, '2016-12-28'), (8, 'Susan C. Richardson', 'Glass Block', 200.00, '2017-01-04'), (9, 'David C. Jury', 'Casing Pipes', 500.00, '2016-12-27'), (10, 'Lori C. Skinner', 'Glass PVC Rubber', 1800.00, '2016-12-30'), (11, 'Shawn S. Derosa', 'Sony HTXT1 2.1-Channel TV', 180.00, '2017-01-03'), (12, 'Karen A. McGee', 'Over-the-Ear Stereo Headphones ', 25.00, '2017-01-01'), (13, 'Kristine B. McGraw', 'Tristar 10" Round Copper Chef Pan with Glass Lid', 20.00, '2016-12-30'), (14, 'Gary M. Porter', 'ROBO 3D R1 Plus 3D Printer', 600.00, '2017-01-02'), (15, 'Sarah D. Hunter', 'Westinghouse Select Kitchen Appliances', 35.00, '2016-12-29'), (16, 'Diane J. Thomas', 'SanDisk Ultra 32GB microSDHC', 12.00, '2017-01-05'), (17, 'Helena J. Quillen', 'TaoTronics Dimmable Outdoor String Lights', 16.00, '2017-01-04'), (18, 'Arlette G. Nathan', 'TaoTronics Bluetooth in-Ear Headphones', 25.00, '2017-01-03'), (19, 'Ronald S. Vallejo', 'Scotchgard Fabric Protector, 10-Ounce, 2-Pack', 20.00, '2017-01-03'), (20, 'Felicia L. Sorensen', 'Anker 24W Dual USB Wall Charger with Foldable Plug', 12.00, '2017-01-04'); -- -- Indexes for dumped tables -- -- -- Indexes for table `tbl_order` -- ALTER TABLE `tbl_order` ADD PRIMARY KEY (`order_id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `tbl_order` -- ALTER TABLE `tbl_order` MODIFY `order_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;
Did you know there is a 12 word phrase you can speak to your man... that will induce deep feelings of love and instinctual attractiveness for you deep inside his heart?
ReplyDeleteBecause hidden in these 12 words is a "secret signal" that fuels a man's impulse to love, treasure and look after you with all his heart...
12 Words Who Fuel A Man's Love Impulse
This impulse is so built-in to a man's brain that it will make him try harder than ever before to take care of you.
In fact, fueling this influential impulse is so essential to achieving the best possible relationship with your man that once you send your man one of these "Secret Signals"...
...You'll immediately notice him expose his heart and mind for you in such a way he's never expressed before and he'll see you as the one and only woman in the galaxy who has ever truly attracted him.