How to Export data from a database to CSV file in PHP

02nd, January 2018 By

Today we check how to export data from database to CSV files. The comma-separated values (CSV) files  store data in plain text form.Plain text means data is in sequence of characters.In CSV file you can store any number of records.Some time in our work on php we need to create the csv file from our database .

Export CSV

Its very easy to create the csv files from database.CSV file might be used to transfer information from a database program to a spreadsheet.

<?php 
// Connect to database for the users include('database.php');
 $sql = "SELECT username, email FROM users ORDER BY username"; 
$results = mysql_query($sql); 
// The filename 
$filename = 'example.csv'; 
// The w+ parameter will wipe out and overwrite any existing file with the same name 
$file = fopen($filename, 'w+'); 
// Write the csv file column titles 
fputcsv($file, array('Username','Email')); 
// Write all the user records to the csv file 
foreach($results as $row) 
{ 
fputcsv($file, array($row['username'], $row['email'])); } 
// Finish writing the file 
fclose($file); 
?>

Using this code you will export your data to csv file.Here first you will need to connect to database.Then have to get file which you want to write.The make that file like here example.csv writable.
Then use fputcsv() function to add the file columns titles.Then you will add any data that you want to add in your example.csv file using fputcsv() function.At last close the file using the fclose() function.Now your all data from database added in your example.csv file.

Download CSV File

Some time you will need that csv file will able to download from browser.Using these mime type you will make the file will download  from browser.

    <? mysql_connect('localhost','root',''); 
mysql_select_db('test'); 
$sql = "select user_name,email from student";
 // Query Database 
$filename = 'file.csv'; 
$rsSearchResults = mysql_query($sql) or die(mysql_error()); 
$out = '';
 // fiels to export 
$out .='User Name,Email';
 $out .="\n"; 
// Add all values in the table 
while ($row = mysql_fetch_array($rsSearchResults)) 
{ $out .=''.$row["email"].','; $out .="\n"; }
 // Output to browser with appropriate mime type 
header("Content-type: text/x-csv"); 
header("Content-Disposition: attachment; filename=$filename"); 
echo $out; 
exit; 
?>

Hope these code help you.

Leave a Reply

Your email address will not be published. Required fields are marked *