PHP Strings Program: Downloadable CSV File

PHP Strings

Program: Downloadable CSV File

Combining the header() function to change the content type of what your PHP program outputs with the fputcsv() function for data formatting lets you send CSV files to browsers that will be automatically handed off to a spreadsheet program (or whatever application is configured on a particular client system to handle CSV files).

Example   Downloadable CSV file

              $db = new PDO('sqlite:/usr/local/data/sales.db');
              $query = $db->query('SELECT region, start, end, amount FROM sales',                                                                PDO::FETCH_NUM);
              $sales_data = $db->fetchAll();

              // Open filehandle for fputcsv()
              $output = fopen('php://output','w') or die("Can't open php://output");
              $total = 0;

              // Tell browser to expect a CSV file
              header('Content-Type: application/csv');
              header('Content-Disposition: attachment; filename="sales.csv"');

              // Print header row
              fputcsv($output,array('Region','Start Date','End Date','Amount'));
              // Print each data row and increment $total
              foreach ($sales_data as $sales_line) {
                       fputcsv($output, $sales_line);
                       $total += $sales_line[3];
              }
              // Print total row and close file handle
              fputcsv($output,array('All Regions','--','--',$total));
              fclose($output) or die("Can't close php://output");


Example   sends two headers to ensure that the browser handles the CSV output properly. The first header, Content-Type, tells the browser that the output is not HTML, but CSV. The second header, Content-Disposition, tells the browser not to display the output but to attempt to load an external program to handle it. The filename attribute of this header supplies a default filename for the browser to use for the downloaded file.

If you want to provide different views of the same data, you can combine the formatting
code in one page and use a query string variable to determine which kind of data formatting to do. 


Example   Dynamic CSV or HTML

              $db = new PDO('sqlite:/usr/local/data/sales.db');
              $query = $db->query('SELECT region, start, end, amount FROM sales',                                                               PDO::FETCH_NUM);
              $sales_data = $db->fetchAll();

              $total = 0;
              $column_headers = array('Region','Start Date','End Date','Amount');
              // Decide what format to use
              $format = $_GET['format'] == 'csv' ? 'csv' : 'html';

              // Print format-appropriate beginning
              if ($format == 'csv') {
                   $output = fopen('php://output','w') or die("Can't open php://output");
                   header('Content-Type: application/csv');
                   header('Content-Disposition: attachment; filename="sales.csv"');
                   fputcsv($output,$column_headers);
              } else {
                   echo '<table><tr><th>';
                   echo implode('</th><th>', $column_headers);
                   echo '</th></tr>';
              }           

              foreach ($sales_data as $sales_line) {
                   // Print format-appropriate line
                   if ($format == 'csv') {
                        fputcsv($output, $sales_line);
                   } else {
                        echo '<tr><td>' . implode('</td><td>', $sales_line) . '</td></tr>';
                  }
                    $total += $sales_line[3];
              }
              $total_line = array('All Regions','--','--',$total);

              // Print format-appropriate footer
              if ($format == 'csv') {
                   fputcsv($output,$total_line);
                   fclose($output) or die("Can't close php://output");
              } else {
                   echo '<tr><td>' . implode('</td><td>', $total_line) . '</td></tr>';
                   echo '</table>';
              }

Accessing the program in Example with format=csv in the query string causes it to return CSV-formatted output. Any other format value in the query string causes it to return HTML output. The logic that sets $format to CSV or HTML could easily be extended to other output formats such as JSON.

If you have many places where youwant to offer for download the same data in multiple formats, package the code in Example  into a function that accepts an array of data and a format specifier andthen displays the right results.