How to export data in csv file using php and mysql

By | July 13, 2017
0 Comment
export csv file

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.

Post Views: 1,145

Related Posts