How to Export Data to CSV File With Date Filter Using PHP & MySQL

This is another contribution to exporting MySQL data. In this post, you will learn how to export MySQL data to a CSV file using a PHP script. But here we add features like the date range, which means that only the mysql data that lies between two defined dates is exported to the CSV file format using a PHP script. Learn how to export MySQL data for a specific date in an Excel worksheet or CSV file format using PHP.

If you've already learned how to export data to a CSV file or Excel spreadsheet using a PHP script. Suppose we don't want to export complete MySQL data to a CSV file or Excel spreadsheet, but we want to export this data to a CSV file using PHP that falls within the selected date range. This section shows you how to export MySQL data to a CSV file or Excel spreadsheet using the PHP script date range filter. This feature increases the usability of your web application and allows you to freely export the data you want to export, and does not export unwanted integer data. This feature reduces the bandwidth of your website and the load on your MySQL database, since only the required or filtered data has been exported to a CSV file or an Excel spreadsheet.

In this release, we want to export date range filter data to a CSV file using PHP. In PHP, many PHP compilation functions are then available in the file system to export data to a CSV file. Here we used some PHP functions like fopen (), fputcsv (), fclose () to export data to a CSV file. Here the function fopen () opens the file in PHP order. After this function, fputcsv () writes data to the open file and the fclose () function closes the opened file. Therefore, this basic PHP function was used to export data to a CSV file in PHP. But here we not only export MySQL data to a CSV file, we also export filtered exported data to a CSV file. A date range is used for the filter data, ie only two dates are defined and only the dates that were inserted between these two defined dates are exported. We can do this in the MySQL query found below. We used the start date selection plug-in to select the date range. In this simple tutorial, you will learn how to use the date range filter to export data to a CSV file in PHP. You can also find the full source code below.

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>How to Export Data to CSV File With Date Filter Using PHP & MySQL</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">How to Export Data to CSV File With Date Filter Using PHP & MySQL</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>