Hi, In this post we have looking for discuss topic on Jquery Datatables, How can we perform date range search on Datatables that means we will filter data between two given date and filter Datatables data based on between two given data. Here we will use Jquery Datatables and Ajax as front end operation and for back end we will use PHP script.
If you have use Datatables in your project then you have get features like sorting of table column data, searching of table data, pagination and many more other feature without writing any line of code but if you want to perform date range search then this type of functionality you can not find in Jquery Datatables plugin. So for perform Date range search on Datatables data then you have to write some extra code at back end.
Datatables is a widely used Jquery plugin for displaying data in tabular format and in this you can get build in feature and most of the programmer is used this plugin for displayed data in tabular format on browser and it is very easy to implement in our project. In this plugin you can get client side date range filter but you cannot find server side processing of date range search filter. So in this post we have discuss how to make date range server side search filter by using PHP script with Jquery Ajax.
Source Code
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>
fetch.php
<?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
--
-- 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;
great job sir ! keep going...
ReplyDeleteThanks a million... this has really help my project.
ReplyDeletecan you please add the export button to that table......
ReplyDeletecan you please add the export button to above table sir
ReplyDeletecan you please add export button to above table
ReplyDeleteIs there way I can use date format like "2017-11-21 10:40:00"
ReplyDeletedate('y-m-d h:i:s');
Deletehowto add DATE + TIEM function like "yy-m-dd hh:mm:ss"
ReplyDeleteI keep getting in valid Json. log says undefined index is_date_search
ReplyDeleteI can't see any code for the pagination. Is that a function from jquery?
ReplyDeletevery usage full & give perfectly or contain all things
ReplyDeletehello sir can you please individual column search for the same table above
ReplyDeletecan you please send the code by using sqlserver instead of mysql
ReplyDeleteThank you
ReplyDeletei have this error
ReplyDeleteDataTables warning: table id=order_data - Ajax error. For more information about this error, please see http://datatables.net/tn/7
how can i resolve this ??
Amazing lesson and project I have ever seen!
ReplyDeleteI am thankful to You endlessly for the great job, ideas, creative projects in general!
I want to ask You, is there possibility to get similar project in PHP7 version (php pdo)? :/
Thank You in advance!
Sincerely,
Irana
irana.miriyeva@gmail.com
did you find example for PDO
Deletealso im getting this error
DataTables warning: table id=order_data - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1
Weblesson platform is awesome dear ...
ReplyDeletehow can we export these datatable to csv.
ReplyDeleteThank you in advance.
can you convert this to codeigniter. i need help i would really appreciate it. :(
ReplyDeletei think it same , just different how to write code
Deletei have converted it in codeigniter
Deletecan you add (add, update,delete in this)
ReplyDeleteDataTables warning: table id=order_data - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1
ReplyDeleteWhy this pop-up as an error can you please help me...
Please make a video tutorial for role based access permission in codeigniter using hooks.
ReplyDeletePlease please please. I need this for my project
Hello , how do i add a function to export to excel or pdf or both in this?
ReplyDeleteI have seen your export csv button which export all from mysql statement, I don't know how change the export button code to the filtered date range. Can you show us ?
ReplyDeleteNiiiice lesson :), very useful!
ReplyDeletethis is good job. thank
ReplyDeletehow to use this code in codeigniter framework
ReplyDeleteI want to ask You, is there possibility to get similar project in PHP7 version (php pdo)? :/
ReplyDeleteThank You in advance!
good day can you add code for total sum of amount value depend of filter?
ReplyDeletehow to get total sum of amount value
ReplyDeleteHow can i define date range like 2000 - 2025 or currentdate to next five year or current date to previous five year
ReplyDeletehi
ReplyDeleteits does not work when i use join in fetch.php query
SELECT sp.name,sp.product_category_id,sp.product_subcategory_id,sp.email,sp.mobile,sp.your_message,sp.organisation,sp.form_type,psf.id,psf.features_name,sp.email,sp.created_at,
ps.id,ps.sub_category_name FROM sany_form as sp
LEFT JOIN product_subcategory_features as psf ON ('sp.product_subcategory_id = psf.id')
LEFT JOIN product_subcategory as ps ON ('sp.product_category_id= ps.id')
how to print search result to pdf or export to csv, thank you
ReplyDelete
Delete$(document).ready(function() {
table = $('#example').DataTable( {
dom: 'Bfrtip',
buttons: [
'copy', 'csv', 'excel', 'pdf', 'print'
]
} );
});
hi
ReplyDeleteplease help, how do i add print and export data to pdf to the data table?
ReplyDeleteSir ,
ReplyDeletei want convert fetch data from database into hyperlink. how to made?
how to disable future date in calendar?
ReplyDeletefacing error that when i select date range and after that my normal search box is not functioning because where clause for date range is being added
ReplyDeleteNice and very useful tutorial. I have some small implementation issues with a error "Uncaught TypeError: $(...).datepicker is not a function" and points to the row which in your tutorial is #69 (in my code is #80...). I would send you the code if you can give me your private email... I'm sure you can spot the error quickly. Thank you in advance.
ReplyDeletethanks
ReplyDeleteDate range search and text search are not working together :(
ReplyDeletecan you make a tutorial like this but to generate highcharts?
ReplyDeletecan any one help me how to export the filted data.
ReplyDelete$(document).ready(function() {
table = $('#example').DataTable( {
dom: 'Bfrtip',
buttons: [
'copy', 'csv', 'excel', 'pdf', 'print'
]
} );
});