Philadelphia Reflections

The musings of a physician who has served the community for over six decades

Related Topics

No topics are associated with this blog

Create and send CSV files from PHP

Here's how CSV files are created and downloaded on this site. No saving the file or data import into Excel ... Excel just opens with the data automatically. Very handy.

This function as shown pulls all the field names to create a CSV header and then pulls every field from every row in the table. There is no need to know the field names, the data types or the size of the table. Quotes in the data are double-quoted and the result is surrounded by quotes.

CSV calls for each field to be contained in double quotes.


Pull data from a database using standard PHP MySQL functions:

<?php

$db_link        = mysql_connect(DB_HOST, DB_USER, DB_PSWD);
$db_selected    = mysql_select_db(DB_DATABASE, $db_link);

# Create the CSV file header from the database-table field names
$query          = "SHOW COLUMNS FROM table";
$result         = mysql_query($query);

$csv_output     = NULL;
while ($row = mysql_fetch_assoc($result))
	{
	$csv_output .= '"' . str_replace('"', '""', $row["Field"]) . '",';
	}
$csv_output  = substr($csv_output, 0, -1) . "\n";  // remove trailing "," and add a line break

# Pull all the rows
$query          = "SELECT * FROM table";
$result         = mysql_query($query);

# loop through database records creating one comma-delimed line per row
while ($row = mysql_fetch_assoc($result))
	{
	foreach ($row as $key => $value)
	  {
	  $$key = $value;
	  $$key = str_replace('"', '""', $$key);
	  $var  = $$key;
	  $csv_output .= "\"$var\",";
	  }
	$csv_output .= "\n";
	}

# send the file
$size_in_bytes		= strlen($csv_output);
$csv_file		= "filename_" . date("Y-m-d") . ".csv";
	
$ContentType		= "Content-type: application/vnd.ms-excel";
$ContentLength		= "Content-Length: $size_in_bytes";
$ContentDisposition	= "Content-Disposition: attachment; filename=\"$csv_file\"";

header($ContentType);
header($ContentLength);
header($ContentDisposition);

echo "$csv_output"; 

?>

I generally use compression with output buffering to speed things up:

ob_start("ob_gzhandler");
      .
      .
      .
ob_end_flush();

To use in your HTML:

<button onclick="window.location='CSVoutput.php'" 
	style="font-size:85%;width:100px;">Download<br />CSV file</button>

(my thanks to https://centricle.com/tools/html-entities/ for HTML encoding)

Originally published: Thursday, June 12, 2008; most-recently modified: Monday, June 04, 2012

This is exactly what I needed. Thank you.
Posted by: John Eubanks   |   Nov 5, 2011 2:33 AM