Export Data From MySQL table to CSV File using PHP

CSV  stands for  Comma separated values. It is one of the most widely used format to transfer data from one application to another in a tabular form. In this post, we are going to learn to export the data from database to a CSV file.

The Script:

<?php 
//Connect and select a database
mysql_connect('localhost','root','');
mysql_select_db('webapp');

//Output headers to make file downloadable
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=spreadsheet.csv');

//Write the output to  buffer
$data = fopen('php://output', 'w');

//Output Column Headings
fputcsv($data,array('Name','Email Address'));

//Retrieve the data from database
$rows = mysql_query('SELECT name, email FROM users');

//Loop through the data to store them inside CSV
while($row = mysql_fetch_assoc($rows)){
 fputcsv($data, $row);
}

As you can see, on line 3 and 4, we are connecting our script with the database. Then, on line 7 we are producing HTTP headers to specify the MIME type of the file which is text/csv. On the next line, we have set the Content-Disposition toattachment to make the file downloadable and spreadsheet.csv is the name of the file which will be downloaded.

Line 11 creates a write-only output stream which allows us to write the data to the buffer. On line 14, we are printing column headings which in out case areName and Email Address,  you can change them as per your requirement. And on line 17, we are retrieving the data from database using a simple MySQL query.

And online 20 and 21, we are looping through retrievedthe data and writing it to our output stream.