Export MySQL data to CSV file Using PHP
Sometime we need MySQL data in CSV format and as we know CSV is good user readable format.
So here is simple MySQL to CSV export class written in PHP.
First create "csvexport" class and define required variables class csvexport { var $separator = ','; var $line_break = "\n"; var $field_names = true; var $dbhost = ''; // mysql database server var $dbuser = ''; // database username var $dbpass = ''; // database password var $dbname = ''; // database name var $data = ''; var $error = ''; var $result = false; var $temp_path = ''; }
Next add constructor function in class with two parameters SQL query and CSV filename and in constructor function write MySQL database connection. Define database information in above variable list.
function __construct($sql,$filename) { $Conn = mysql_connect($this->dbhost, $this->dbuser, $this->dbpass) or $this->error_msg("Error: Invalid MySQL Server Information"); if (!$Conn) $this->error_msg("Error: Invalid MySQL Server Information"); $DB_select = mysql_select_db($this->dbname, $Conn); if (!$DB_select) $this->error_msg("Error: Invalid MySQL Database"); if ($this->temp_path == '') $this->temp_path = sys_get_temp_dir(); $this->filename = $filename; $this->sql = $sql; $this->export(); }
Here are some required functions/methods which are used while exporting MySQL data in CSV. Add this functions to above class.
//sql execution function which will return resource id function execute($sql) { if ($sql!="") { $result = mysql_query($sql) or $this->error_msg("Error: Check MySQL Query($sql)"); if ($result) return $result; else return false; } } //function to check number of record of resource id function count_check($result) { if ($result) { if (mysql_num_rows($result) > 0) return true; else return false; } } //function to fetch mysql data from resource id in associative array function recordset($result) { if ($result) { while($row = mysql_fetch_assoc($result)) $data[] = $row; } return $data; } //function to display error message function error_msg($msg) { if ($msg != '') die($msg); else return true; }
Finally the export function to fetch data from mysql databse and create csv format to download
function export() { $rs = $this->execute($this->sql); if (!$this->count_check($rs)) $this->error_msg("Error: No Data"); else { $rs_data = $this->recordset($rs); if (is_array($rs_data) && count($rs_data) > 0) { $i = 0; foreach($rs_data as $rs_data_row) { if ($this->field_names == true) { if ($i == 0) { $data_keys = array_keys($rs_data_row); if (count($data_keys) > 0) $this->data = implode($this->separator,$data_keys); $this->data .= $this->line_break; } $t_str = ''; foreach($data_keys as $dkey) { $t_str .= '"'.html_entity_decode($rs_data_row[$dkey]).'"'.$this->separator; } if ($t_str != '') $this->data .= trim($t_str,$this->separator).$this->line_break; } $i++; } if ($this->data != '') { if (file_put_contents($this->temp_path.$this->filename,$this->data) == FALSE) { $this->error_msg("Error: Cannot write to file(".$this->filename.")"); } else { // code to download file in csv format header("Content-type: octet/stream"); header("Content-disposition: attachment; filename=".basename($this->temp_path.$this->filename)); header("Content-Length: ".filesize($this->temp_path.$this->filename)); readfile($this->temp_path.$this->filename); } } } } }
As we have created csvexport class to export MySQL data in CSV file, now below is the code by which we create csv file and download it
$filename = 'data.csv'; // download CSV filename $field_name = "FIELD1,FIELD2,FIELD3,...."; // database table fields which are to be exported in csv file $sql = "SELECT $field_name FROM TABLE_NAME"; // final SQL query new csvexport($sql,$filename);
Hello,
I try to generate csv file from database.
cat i set filesize 50MB in header part like this
header(“Content-Length:.filesize=’50Mb'”);