Pages

Subscribe:

Ads 468x60px

Labels

2014年3月16日 星期日

Export MySQL to CSV (Excel) using php

You can export your MySQL database to .csv file format (Microsoft Excel file) easily using php.
This tutorial require 1 PHP file and 1 table of mySQL database.
1. exportcsv.php
2. The table is with 2 fields: id(auto_increment), name(varchar, 50) and put some records about 20 – 30 records into this table. (directly by phpMyAdmin)
The exportcsv.php file looks like this :-
< ?php
// Connect database
$database="mydb";
$table="mytablename";
mysql_connect("localhost","","");
mysql_select_db("mydb");
 
$result=mysql_query("select * from $table");
 
$out = '';
 
// Get all fields names in table "mytablename" in database "mydb".
$fields = mysql_list_fields(mydb,$table);
 
// Count the table fields and put the value into $columns.
$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');
?>