How To Export MySQL Data To CSV File With PHP

In my previous post, I wrote how you can import data from a CSV file to your MySQL database. Now I am going to write how you can export data to a CSV file as a backup. You may have seen this kind feature in many websites where we can download report in CSV format. Gmail also allows users to download contacts in CSV formats. This kind of feature lets users of your web app to download data into their system.

To add similar kind for feature in your website, you can use PHP to export data in CSV form with PHP.

Concept is simple. You need to first create the CSV file structure. By structure, I mean the column names. You can also leave this step and your exported data will not have any label. Your exported data will look same as it look in the PHPmyadmin.

Export MySQL Data To CSV File With PHP

See the code

<?php
$database="database";
$table="company";
mysql_connect("localhost","root","");
mysql_select_db("database");

$result=mysql_query("select * from $table");

$out = '';
$fields = mysql_list_fields($database,$table);

$columns = mysql_num_fields($fields);

// Put the name of all fields to $out.
for ($i = 0; $i < $columns; $i++) {
$l=mysql_field_name($fields, $i);
$out .= '"'.$l.'",';
}
$out .="\n";

// Add all values in the table to $out.
while ($l = mysql_fetch_array($result)) {
for ($i = 0; $i < $columns; $i++) {
$out .='"'.$l["$i"].'",';
}
$out .="\n";
}

// Open file export.csv.
$f = fopen ('export.csv','w');

// Put all values from $out to export.csv.
fputs($f, $out);
fclose($f);

header('Content-type: application/csv');
header('Content-Disposition: attachment; filename="export.csv"');
readfile('export.csv');

?>

You can use this code directly. just change the name of file, database connection, and table name according to your own application. This change is necessary. In case you have any problem to implementing this code, you can comment below. I will try to help you as soon as possible.

In above code, I used data from a single table. You can also use the same process for exporting data by adding a complex query to get data from multiple table. This process is useful for report kind of things.

Read: how to capture image from webcam with PHP