This simple tutorial belongs to newbie php and cakephp developer, Here i’ll tell you the simplest trick to export your mysql data into excel/csv, Every time we need this while generating reports so this is the simplest line of code to do this But If you want more customize report you must use any third party report grnerator plugin which provide more flexibility to generate reports.

Now first i’ll tell how to do this in core php almost same line of code will follow in cakephp.


Method-1

Create a function in php named exportInExcel()

<?php
function exportInExcel($fileName, $headerRow, $data) {
  ini_set('max_execution_time', 1600); //increase max_execution_time to 10 min if data set is very large
  $fileContent = implode("\t ", $headerRow)."\n";
  foreach($data as $result) {
   $fileContent .=  implode("\t ", $result)."\n";
  }
 header('Content-type: application/ms-excel'); /// you can set csv format
 header('Content-Disposition: attachment; filename='.$fileName);
 echo $fileContent;
exit;
}
?>

Now call this function where you want to export your data into excel..
Lets assume we have a table of books and we need to export from mysql database to excel.

<?php
$fileName = "bookreport_".date("d-m-y:h:s").".xls";
//$fileName = "bookreport_".date("d-m-y:h:s").".csv";
$headerRow = array("Book Title", "ISBN No.", "Auther");
$data = array(
           array('Book Title1', '1111111111', 'Rohit Kumar-1'),
           array('Book Title2', '2222222222', 'Rohit Kumar-2'),
           array('Book Title3', '3333333333', 'Rohit Kumar-3'),
           array('Book Title4', '4444444444', 'Rohit Kumar-4') 
          );
exportInExcel($fileName, $headerRow, $data);
?>

Note: where $data values come from database, must ensure that array should same as you seen in example.



Method-2

In this method we have created simple php class for mysql to csv export via PHP. So you can add this simple class in your project and use this class to export MySql data in Excel CSV format easily.

PHP Class – Export MYSQL data to CSV using PHP

<?php
class H_Mysql_Export {
    public $headerAry = array();
    public $dataAry = array();
    public $directory = './';
    public $filename = 'h_export_';
    public $filepath = '';
 
    public function init(){
        $this->filepath = $this->directory.$this->filename.time();  
    }
 
    public function csv(){
        $this->init();
        $this->filepath = $this->filepath.".csv";
        $handle = fopen($this->filepath, 'w+');
        fputcsv($handle, $this->headerAry);
        foreach($this->dataAry as $csvdata){
            fputcsv($handle, $csvdata); 
        }       
        fclose($handle);
    }
 
    public function download(){     
        if (file_exists($this->filepath)) {
            header('Content-Description: File Transfer');
            header('Content-Type: application/octet-stream');
            header('Content-Disposition: attachment; filename='.basename($this->filepath));
            header('Content-Transfer-Encoding: binary');
            header('Expires: 0');
            header('Cache-Control: must-revalidate');
            header('Pragma: public');
            header('Content-Length: ' . filesize($this->filepath));
            ob_clean();
            flush();
            readfile($this->filepath);
        }
        else {
            echo $this->filepath.' Doesnt Exist!';  
        }
    }
 
    public function delete(){
        unlink($this->filepath);    
    }
}
?>

Below we have creates simple php code to export static data into CSV format using above class.

$export = new H_Mysql_Export();
$export->headerAry = array();
$export->dataAry = array();
$export->csv();
$export->download();

Using PHP and mysql

mysql_connect("localhost","root","");
mysql_select_db("db");
 
$query = mysql_query("SELECT * FROM cds");
while($row = mysql_fetch_assoc($query)){
    $data[] = $row;
}
 
print_r($data);
 
include('classFile.php');
 
$export = new H_Mysql_Export();
$export->headerAry = array('Title','Interpret','jahr','ID');    // TABLE COLUMN NAMES
$export->dataAry = $data;                   // TABLE DATA ARRAY FROM MYSQL
$export->filename = 'Sample';                   // CUSTOM FILE NAME 
$export->directory = 'files/';                  // DIRECTORY NAME
$export->csv();                         // INITIALIZATION
$export->download();                        // AUTOMATIC DOWNLOAD
$export->delete();                      // AUTOMATIC DELETE DOWNLOADED FILE



Method-3: in Cakephp

Now lets talk about the cakephp just like core php we can do same thing but here we’ll create a component to do this in better manner.

Go to app/Controller/Component and create file name ExportXlsComponent.php

ExportXlsComponent.php

<?php
App::uses('Component', 'Controller');
class ExportXlsComponent extends Component {
 
function export($fileName, $headerRow, $data) {
 ini_set('max_execution_time', 1600); //increase max_execution_time to 10 min if data set is very large
  $fileContent = implode("\t ", $headerRow)."\n";
  foreach($data as $result) {
   $fileContent .=  implode("\t ", $result)."\n";
  }
 header('Content-type: application/ms-excel'); /// you can set csv format
 header('Content-Disposition: attachment; filename='.$fileName);
 echo $fileContent;
exit;
}
?>

After that call this component in your controller file where you want to export data into excel.

BooksController.php

<?php
class BooksController extends AppController {
public $components = array('ExportXls');
 
 function reports() {
  $this-autoRender = false;
  $this->layout = false;
$fileName = "bookreport_".date("d-m-y:h:s").".xls";
//$fileName = "bookreport_".date("d-m-y:h:s").".csv";
$headerRow = array("Book Title", "ISBN No.", "Auther");
$data = array(
           array('Book Title1', '1111111111', 'Rohit Kumar-1'),
           array('Book Title2', '2222222222', 'Rohit Kumar-2'),
           array('Book Title3', '3333333333', 'Rohit Kumar-3'),
           array('Book Title4', '4444444444', 'Rohit Kumar-4') 
          );
  $this->ExportXls->export($fileName, $headerRow, $data);
  }
}
?>

Thanks :)

FREE PHP Script: Export MYSQL data to CSV using PHP

Following are the list of popular top rated hand picked Export MYSQL data to CSV using PHP.

I Hope you liked Hand-picked list of Export MYSQL data to CSV using PHP, Don’t forget to Subscribe My Public Notebook for more useful Hand-picked PHP and MySql code examples, tutorials and articles.