Thursday 6 October 2016

Ajax with PHP MySQL Date Range Search using jQuery DatePicker




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');

29 comments:

  1. Very useful, keep going Sir.

    ReplyDelete
  2. how to limit the results at the first page (and the after date search) and have pegination like 1-25 1 2 3 4 5

    ReplyDelete
  3. The solutions which you provide are really helpful. Thank you

    ReplyDelete
  4. how to export the result after filter the date?

    ReplyDelete
  5. it display data of every month of a selected date

    ReplyDelete
  6. i want to export this filter date in csv how it possible to do please give me solution

    ReplyDelete
  7. Thanks alot i really appreciate your effort man

    ReplyDelete
  8. ThankYou , Very Useful & easy way to learn <3

    ReplyDelete
  9. plzzzzzzzzzz make


    search combining with date, name, n everything in data

    ReplyDelete
  10. How to show the count of records in this script?

    ReplyDelete
  11. Loved this post you really helped me

    ReplyDelete
  12. can you please do this in codeigniter

    ReplyDelete
  13. hello 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

    and
    mysqli_fetch_array() expects parameter 1 to be mysqli_result, null given in 42

    ReplyDelete
  14. 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 .

    ReplyDelete
  15. errol baykara teşekkürlerhhhhh

    ReplyDelete
  16. Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\web\admin\filter.php on line 22
    erros ??? solve this

    ReplyDelete
  17. very helpfull info mate keep up

    ReplyDelete
  18. how to retreive the sum of those two date retrieved?

    ReplyDelete