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.





Source Code


tbl_order


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


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;  
 }  
 ?>  

2 comments:

  1. 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