How to convert mysql table data to Excel or CSV format in CakePHP?

Question

Hi, I want to convert my sql data to csv files while clicking on a button. The code fragments I found for sql to CSV conversion were in PHP, and I'm trying to convert it to CakePHP since I'm working in CakePHP.

Here is the PHP code I'm tring to convert:

$result = mysql_query("SHOW COLUMNS FROM ".$table."");
$i = 0;
if (mysql_num_rows($result) > 0) {
   while ($row = mysql_fetch_assoc($result)) {
        $csv_output .= $row['Field']."; ";
        $i++;
   }
}
$csv_output .= "\n";

$values = mysql_query("SELECT * FROM ".$table."");
while ($rowr = mysql_fetch_row($values)) {
    for ($j=0;$j<$i;$j++) {
           $csv_output .= $rowr[$j]."; ";
    }
    $csv_output .= "\n";
}

$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
print $csv_output;

SOLUTION

Function in the Controller:

function exporttocsv()
{       
 $this->set('headers',$this->Result->find('all',array('fields'=>'Result.label')));
 $this->set('values',$this->Result->find('all',array('fields'=>'Result.value')));    
}

exporttocsv.ctp file:

<?php

foreach($headers as $header):

  $csv_output .=$header['Result']['label'].", ";

endforeach;
$csv_output .="\n";

if(!empty($values)){
foreach($values as $value):

  $csv_output .=$value['Result']['value'].", ";

endforeach;
$csv_output .="\n";
}
else{
echo "There is no data to export.";
}

$filename = "export_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header("Content-disposition: filename=".$filename.".csv");

print $csv_output;

exit;
?>

Answer 1

First of all, you don't do queries and output in the same file in Cake. You query the data as usual in the Controller, $this->set() the result to the view, and in the view you do something like this:

foreach ($results as $result) {
    echo join(', ', $result['COLUMNS']);
    echo "\n";
}

Outputs something like this:

value, varchar(25), NO, , ,
submitter, int(11), NO, , ,
...

Since Cake automatically wraps a layout around your view, you'll have to set the layout to something different, like 'ajax' (which is simply an empty layout).

Answer 2

deceze is correct about outputting the results from the view file. You'll just need to set some headers so that it appears as a file download on the client side. You can simply put these 2 calls in the top of your view:

header("Content-type:application/vnd.ms-excel");
header("Content-disposition:attachment;filename=\"{$filename}\"" );

If you plan on doing csv downloads in more than one place in your application, I'd recommend this helper:

http://bakery.cakephp.org/articles/view/csv-helper-php5

I use it and it works well.