
Hello friends, Sometimes we need data which are showing on the website in excel or csv format to manipulate manually. So it is a easiest to get data into csv format with export feature. In the PHP we can write a script to export data into csv format. I am going to tell you a simple and easiest way. Please follow the steps given below –
<?php $data_list = array ( array('aaa', 'bbb', 'ccc', 'dddd'), array('123', '456', '789'), array('"aaa"', '"bbb"') ); $fp = fopen('file.csv', 'w'); foreach ($data_list as $fields) { fputcsv($fp, $fields); } fclose($fp); ?>
This is a simple example of array data. It will create file.csv file and write array data into it.
Export CSV file from mysql database data :
Method 1 –
In this method we will fetch data from database then put each data into a variable and then from the header it will automatic download the csv file with the data fetched from the database.
<?php $db_record = 'XXXXXXXXX'; $where = 'WHERE 1 ORDER BY 1'; // filename to be exported $csv_filename = 'db_export_'.$db_record.'_'.date('Y-m-d').'.csv'; $host_name = "localhost"; $user_name = "XXXXXXXXX"; $db_pass = "XXXXXXXXX"; $db_name = "XXXXXXXXX"; // Database connecten voor alle services mysql_connect($host_name , $user_name , $db_pass) or die('Could not connect: ' . mysql_error()); mysql_select_db($db_name ) or die ('Could not select database ' . mysql_error()); $csv_export = ''; // query to get data from database $query = mysql_query("SELECT * FROM ".$db_record." ".$where); $field = mysql_num_fields($query); // create line with field names for($i = 0; $i < $field; $i++) { $csv_export.= mysql_field_name($query,$i).';'; } // newline (seems to work both on Linux & Windows servers) $csv_export.= ' '; // loop through database query and fill export variable while($row = mysql_fetch_array($query)) { // create line with field values for($i = 0; $i < $field; $i++) { $csv_export.= '"'.$row[mysql_field_name($query,$i)].'";'; } $csv_export.= ' '; } // Export the data and prompt a csv file for download header("Content-type: text/x-csv"); header("Content-Disposition: attachment; filename=".$csv_filename.""); echo($csv_export); ?>
Method 2 :
In this method we will use fputcsv() method to write data into .csv file.
$host_name = "localhost"; $user_name = "XXXXXXXXX"; $db_pass = "XXXXXXXXX"; $db_name = "XXXXXXXXX"; $connection = mysql_connect($host_name , $user_name , $db_pass) or die('Could not connect: ' . mysql_error()); mysql_select_db($db_name ) or die ('Could not select database ' . mysql_error()); $sql = mysql_query("SELECT * FROM ".$db_record." WHERE 1 ORDER BY 1"); $res = mysql_query($sql,$conecction); $file= fopen('file.csv', 'w'); while($row = mysql_fetch_assoc($res)){ fputcsv($file, $row); } fclose($file);
In the above code all the data fetched from database through query will be written in file.csv file. You can choose anyone of the method according to your needs.