Clever Geek Handbook

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

I want to convert sql data to csv files by clicking on a button. The code snippets I found for converting sql to CSV was in PHP and I am trying to convert it to CakePHP since I work in CakePHP.

Here is the PHP code I want 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;

Decision

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;
?>
+2


source to share


2 answers


First of all, you are not making requests or exiting in the same file in Cake. You request data as usual in the controller, the $this->set()

result is for 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 the layout around your view, you need to set the layout to something else, for example 'ajax'

(which is just an empty layout).

+4


source


deceze correctly outputs the results from the view file. You just need to set some headers for it to appear as a client side file upload. You can simply place these 2 calls at 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 would recommend this helper:



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

I use it and it works well.

+1


source






More articles:


All Articles