How to export data from MySQL table to csv using php

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');

Demo

How to export data from MySQL table to csv using php

Related Tutorials:

Related Post

How to get web page title and meta tags using PHP

How to Create Image thumbnail using PHP

Watermark images using PHP and GD Library

3 thoughts on “How to export data from MySQL table to csv using php”

Leave a Reply Cancel reply