Export Query to CSV
It's been requested that I generate reports from the data stored in the database. They want to open them in Excel, so I decided to generate CSVs. Php5 added a nice function, fputcsv, that will format an array as a CSV and write it to the file.
$file = fopen('php://temp/maxmemory:'. (5*1024*1024), 'r+');
while($data = mysql_fetch_assoc($query)){
fputcsv($file, $data);
}
rewind($file);
$export = stream_get_contents($file);
fclose($file);
header('Content-type: application/csv');
header('Content-Disposition: atachment; filename="report.csv"');
echo $export;This works great in most cases, but I was having a problem if the field contained a comma and a double quote. But wait, this function says it escapes double quotes, what's going wrong? What was going wrong is that the data had already been escaped by mysql (\") so wasn't being escaped correctly by fputcsv ("").
Since what I pull out of the database and what go into fputcsv are both arrays, I didn't want to split the array up, remove the slashes then put it back together again by hand. Instead, I found a function that will do it!
function unstrip_array($array){
foreach($array as &$val){
if(is_array($val)){
$val = unstrip_array($val);
}else{
$val = stripslashes($val);
}
}
return $array;
}So with this, the fputcsv line becomes:
fputcsv($file, unstrip_array($data));
It should be noted that this function calls itself on the child array to an infinite depth, so this could cause memory issues on arrays with an excessively large depth. In this case, we only have a single level array, so we should be fine.
Resources:
