Thursday 15 June 2017

Date Range Search in Datatables using PHP Ajax


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;

47 comments:

  1. great job sir ! keep going...

    ReplyDelete
  2. Thanks a million... this has really help my project.

    ReplyDelete
  3. can you please add the export button to that table......

    ReplyDelete
  4. can you please add the export button to above table sir

    ReplyDelete
  5. can you please add export button to above table

    ReplyDelete
  6. Is there way I can use date format like "2017-11-21 10:40:00"

    ReplyDelete
  7. howto add DATE + TIEM function like "yy-m-dd hh:mm:ss"

    ReplyDelete
  8. I keep getting in valid Json. log says undefined index is_date_search

    ReplyDelete
  9. I can't see any code for the pagination. Is that a function from jquery?

    ReplyDelete
  10. very usage full & give perfectly or contain all things

    ReplyDelete
  11. hello sir can you please individual column search for the same table above

    ReplyDelete
  12. can you please send the code by using sqlserver instead of mysql

    ReplyDelete
  13. i have this error
    DataTables 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 ??

    ReplyDelete
  14. Amazing lesson and project I have ever seen!
    I 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

    ReplyDelete
    Replies
    1. did you find example for PDO

      also 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

      Delete
  15. Weblesson platform is awesome dear ...

    ReplyDelete
  16. how can we export these datatable to csv.
    Thank you in advance.

    ReplyDelete
  17. can you convert this to codeigniter. i need help i would really appreciate it. :(

    ReplyDelete
    Replies
    1. i think it same , just different how to write code

      Delete
    2. i have converted it in codeigniter

      Delete
  18. can you add (add, update,delete in this)

    ReplyDelete
  19. DataTables warning: table id=order_data - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1



    Why this pop-up as an error can you please help me...

    ReplyDelete
  20. Please make a video tutorial for role based access permission in codeigniter using hooks.
    Please please please. I need this for my project

    ReplyDelete
  21. Hello , how do i add a function to export to excel or pdf or both in this?

    ReplyDelete
  22. I 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 ?

    ReplyDelete
  23. Niiiice lesson :), very useful!

    ReplyDelete
  24. how to use this code in codeigniter framework

    ReplyDelete
  25. I want to ask You, is there possibility to get similar project in PHP7 version (php pdo)? :/
    Thank You in advance!

    ReplyDelete
  26. good day can you add code for total sum of amount value depend of filter?

    ReplyDelete
  27. how to get total sum of amount value

    ReplyDelete
  28. How can i define date range like 2000 - 2025 or currentdate to next five year or current date to previous five year

    ReplyDelete
  29. hi
    its 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')

    ReplyDelete
  30. how to print search result to pdf or export to csv, thank you

    ReplyDelete
    Replies

    1. $(document).ready(function() {
      table = $('#example').DataTable( {
      dom: 'Bfrtip',
      buttons: [
      'copy', 'csv', 'excel', 'pdf', 'print'
      ]
      } );
      });

      Delete
  31. please help, how do i add print and export data to pdf to the data table?

    ReplyDelete
  32. Sir ,
    i want convert fetch data from database into hyperlink. how to made?

    ReplyDelete
  33. how to disable future date in calendar?

    ReplyDelete
  34. facing 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

    ReplyDelete
  35. Nice 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.

    ReplyDelete
  36. Date range search and text search are not working together :(

    ReplyDelete
  37. can you make a tutorial like this but to generate highcharts?

    ReplyDelete
  38. can any one help me how to export the filted data.

    $(document).ready(function() {
    table = $('#example').DataTable( {
    dom: 'Bfrtip',
    buttons: [
    'copy', 'csv', 'excel', 'pdf', 'print'
    ]
    } );
    });

    ReplyDelete