Recipe 1.17. 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 1-39 formats the results of an SQL SELECT query as CSV data and provides the correct headers so that it is properly handled by the browser.

Downloadable CSV file

<?php require_once 'DB.php'; // Connect to the database $db = DB::connect('mysql://david:hax0r@localhost/phpcookbook'); // Retrieve data from the database $sales_data = $db->getAll('SELECT region, start, end, amount FROM sales'); // 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 1-39 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. In Example 1-40, the format query string variable controls whether the results of an SQL SELECT query are returned as an HTML table or CSV.

Dynamic CSV or HTML

<?php $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 1-40 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 like XML. If you have many places where you want to offer for download the same data in multiple formats, package the code in Example 1-40 into a function that accepts an array of data and a format specifier and then displays the right results .




PHP Cookbook, 2nd Edition
PHP Cookbook: Solutions and Examples for PHP Programmers
ISBN: 0596101015
EAN: 2147483647
Year: 2006
Pages: 445

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net