Tuesday 18 June 2019

How to use Date Range Filter For Export Data to CSV File in PHP



This is one more post on Exporting of Mysql data. In this post we will learn How to export Mysql data to CSV file by using PHP script. But here we add on feature like date range, that means here we will discuss how to export only those mysql data which has come between two define date to CSV file format by using PHP script. So, here you can learn How to export mysql data for specific date into Excel sheet or CSV file format via PHP.

If you have already learn How to Export Data to CSV file or Excel Sheet using PHP script. But suppose we do not want to export whole Mysql data into CSV file or Excel spread sheet but we want to Export those Data to CSV file with PHP which has come between select date range. So, at that time this turial will help you to learn How to export mysql data to CSV file or Excel sheet by using date range filter with PHP script. This is feature will add usability of your web application and use can freely to export those data which he want to export and he do not export whole unwanted data. This feature will reduce your website bandwidth and reduce load on your mysql database because only required or filtered data only has been exported into CSV file or Excel sheet.

In this post we want to Export date range filter data in CSV file using PHP then in PHP there are many PHP build in file system function is available for export data to CSV file. Here we have use some PHP function like fopen(), fputcsv(), fclose() for Export data to CSV file. Here fopen() function will open file in PHP stream. After this fputcsv() function will write data in open file and fclose() function will close open file. So, this basic PHP function has been used for export data to CSV file in PHP. But here we have not only export mysql data to csv file but also here we have export filtered data exported into csv file. So for filter data here we have use date range that means define two date and only export only those data which has come between this two define data. This we can done in mysql query which you can find below. For date range selection here we have use bootstrap date picker plugin. So, this simple tutorial on How to use Date Range Filter with Export Data to CSV File in PHP. Below you can find complete source code also.







Source Code


Database (tbl_order)



DROP TABLE IF EXISTS `tbl_order`;

CREATE TABLE `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 AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;

/*Data for the 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,'2019-06-14'),
(2,'Eddie M. Douglas','Aluminium Heavy Windows',2000.00,'2019-06-08'),
(3,'Oscar D. Scoggins','Plaster Of Paris',150.00,'2019-05-29'),
(4,'Clara C. Kulik','Spin Driller Machine',350.00,'2019-05-30'),
(5,'Christopher M. Victory','Shopping Trolley',100.00,'2019-06-01'),
(6,'Jessica G. Fischer','CCTV Camera',800.00,'2019-06-02'),
(7,'Roger R. White','Truck Tires',2000.00,'2019-05-28'),
(8,'Susan C. Richardson','Glass Block',200.00,'2019-06-04'),
(9,'David C. Jury','Casing Pipes',500.00,'2019-05-27'),
(10,'Lori C. Skinner','Glass PVC Rubber',1800.00,'2019-05-30'),
(11,'Shawn S. Derosa','Sony HTXT1 2.1-Channel TV',180.00,'2019-06-03'),
(12,'Karen A. McGee','Over-the-Ear Stereo Headphones ',25.00,'2019-06-01'),
(13,'Kristine B. McGraw','Tristar 10\" Round Copper Chef Pan with Glass Lid',20.00,'2019-05-30'),
(14,'Gary M. Porter','ROBO 3D R1 Plus 3D Printer',600.00,'2019-06-02'),
(15,'Sarah D. Hunter','Westinghouse Select Kitchen Appliances',35.00,'2019-05-29'),
(16,'Diane J. Thomas','SanDisk Ultra 32GB microSDHC',12.00,'2019-06-05'),
(17,'Helena J. Quillen','TaoTronics Dimmable Outdoor String Lights',16.00,'2019-06-04'),
(18,'Arlette G. Nathan','TaoTronics Bluetooth in-Ear Headphones',25.00,'2019-06-03'),
(19,'Ronald S. Vallejo','Scotchgard Fabric Protector, 10-Ounce, 2-Pack',20.00,'2019-06-03'),
(20,'Felicia L. Sorensen','Anker 24W Dual USB Wall Charger with Foldable Plug',12.00,'2019-06-04');


index.php



<?php

$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");

$start_date_error = '';
$end_date_error = '';

if(isset($_POST["export"]))
{
 if(empty($_POST["start_date"]))
 {
  $start_date_error = '<label class="text-danger">Start Date is required</label>';
 }
 else if(empty($_POST["end_date"]))
 {
  $end_date_error = '<label class="text-danger">End Date is required</label>';
 }
 else
 {
  $file_name = 'Order Data.csv';
  header("Content-Description: File Transfer");
  header("Content-Disposition: attachment; filename=$file_name");
  header("Content-Type: application/csv;");

  $file = fopen('php://output', 'w');

  $header = array("Order ID", "Customer Name", "Item Name", "Order Value", "Order Date");

  fputcsv($file, $header);

  $query = "
  SELECT * FROM tbl_order 
  WHERE order_date >= '".$_POST["start_date"]."' 
  AND order_date <= '".$_POST["end_date"]."' 
  ORDER BY order_date DESC
  ";
  $statement = $connect->prepare($query);
  $statement->execute();
  $result = $statement->fetchAll();
  foreach($result as $row)
  {
   $data = array();
   $data[] = $row["order_id"];
   $data[] = $row["order_customer_name"];
   $data[] = $row["order_item"];
   $data[] = $row["order_value"];
   $data[] = $row["order_date"];
   fputcsv($file, $data);
  }
  fclose($file);
  exit;
 }
}

$query = "
SELECT * FROM tbl_order 
ORDER BY order_date DESC;
";

$statement = $connect->prepare($query);
$statement->execute();
$result = $statement->fetchAll();

?>

<html>
 <head>
  <title>Daterange Mysql Data Export to CSV in PHP</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" />
  <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>
 </head>
 <body>
  <div class="container box">
   <h1 align="center">Daterange Mysql Data Export to CSV in PHP</h1>
   <br />
   <div class="table-responsive">
    <br />
    <div class="row">
     <form method="post">
      <div class="input-daterange">
       <div class="col-md-4">
        <input type="text" name="start_date" class="form-control" readonly />
        <?php echo $start_date_error; ?>
       </div>
       <div class="col-md-4">
        <input type="text" name="end_date" class="form-control" readonly />
        <?php echo $end_date_error; ?>
       </div>
      </div>
      <div class="col-md-2">
       <input type="submit" name="export" value="Export" class="btn btn-info" />
      </div>
     </form>
    </div>
    <br />
    <table 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>
     <tbody>
      <?php
      foreach($result as $row)
      {
       echo '
       <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>
       ';
      }
      ?>
     </tbody>
    </table>
    <br />
    <br />
   </div>
  </div>
 </body>
</html>

<script>

$(document).ready(function(){
 $('.input-daterange').datepicker({
  todayBtn:'linked',
  format: "yyyy-mm-dd",
  autoclose: true
 });
});

</script>

4 comments:

  1. Hello Sir!

    This article is really helpful, I have just tried this code and it's working like charm.

    Can you please post same article in laravel? I have a laravel project and I need to export the data with the date range.

    Thank you.

    ReplyDelete
  2. How to remove blank lines from the end of the csv file? It can be seen when opening it with a text editor

    ReplyDelete