Last updated on August 19th, 2022 at 09:56 am
You can export data from MySQL table to CSV using php, I am providing complete code especially using mysql_connect for PHP Version 5 and below as well as mysqli_connect for PHP latest versions (5/6/7).
Here is the content of MySQL table named mydb
mysql> select * from mydb;
+----+----------+---------+--------+
| ID | username | country | region |
+----+----------+---------+--------+
| 1 | shane | UK | London |
| 2 | scott | US | Oregon |
+----+----------+---------+--------+
This tutorial require 1 PHP file and 1 table (as shown above) of MySQL database.
1. exportcsv.php
2. The table has 2 fields: id(auto_increment), name(varchar, 50) and put some records about 20 – 30 records into this table. (directly by phpMyAdmin or using sql query)
The exportcsv.php file looks like this :-
PHP version 5 Or Below
<?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');
unlink('export.csv');
?>
PHP Version 5 and Above (using mysqli)
<?php
// Connect database
$database="mydb";
$table="mytablename";
$conn = new mysqli('localhost', '', '');
mysqli_select_db($conn, 'userlogin');
$result=mysqli_query($conn,"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 = mysqli_num_fields($result);
// Add all values in the table to $out.
while ($l = mysqli_fetch_row($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');
unlink('export.csv');
?>
This header options will make sure that the csv file gets downloaded automatically when someone visit the page. You may change the header disposition to “inline” if required.
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename="export.csv"');
readfile('export.csv');
thank youuuuuuuuuuuuuuuuuuuu!!
it is not export an csv on android device
the best………………