How to Export MySQL Table data as CSV file in PHP

The phpMyAdmin allows us different ways to export the Table data. One of a method is CSV(Comma Separated Value).

There are two ways to create a CSV file with PHP –

  • By comma separated string, or
  • using fputcsv() method

I am using the fputcsv() method (writes an array to the file line by line) in the demonstration, where I am listing MySQL table data in the Tabular format and a button to export data.

When the export button gets clicked then creating a new CSV file and download it.


  1. Table structure
  2. Configuration
  3. HTML and PHP
  4. Create and Download CSV file
  5. Conclusion

1. Table structure

I am using users table in the tutorial example.

CREATE TABLE `users` (
  `username` varchar(80) NOT NULL,
  `name` varchar(50) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `email` varchar(70) NOT NULL

2. Configuration

Create a new config.php to define database connection.

Completed Code

$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */

$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
 die("Connection failed: " . mysqli_connect_error());

3. HTML and PHP

Create a <form> that have a table layout, Textarea, and submit button.

Fetch and display users Table data in <table>. Create an Array variable which initializes while creating a new row.

Store the array value in the <textarea> after serializing the data using serialize() function.

Handle <form > submit on download.php page.

Completed code

include "config.php";

<div class="container">
 <form method='post' action='download.php'>
  <input type='submit' value='Export' name='Export'>
  <table border='1' style='border-collapse:collapse;'>
     $query = "SELECT * FROM users ORDER BY id asc";
     $result = mysqli_query($con,$query);
     $user_arr = array();
     while($row = mysqli_fetch_array($result)){
      $id = $row['id'];
      $uname = $row['username'];
      $name = $row['name'];
      $gender = $row['gender'];
      $email = $row['email'];
      $user_arr[] = array($id,$uname,$name,$gender,$email);
       <td><?php echo $id; ?></td>
       <td><?php echo $uname; ?></td>
       <td><?php echo $name; ?></td>
       <td><?php echo $gender; ?></td>
       <td><?php echo $email; ?></td>
    $serialize_user_arr = serialize($user_arr);
  <textarea name='export_data' style='display: none;'><?php echo $serialize_user_arr; ?></textarea>

4. Create and Download CSV file

Create a new download.php file.

Unserialize the $_POST['export_data'] value using unserialize() function which returns an array and using this to insert data in open file using fputcsv() method.

Within fputcsv() passing two parameters –

  • File variable and
  • An array value.

After successfully file creation preparing it for download and delete it after downloading using unlink() method.

Completed code

$filename = 'users.csv';
$export_data = unserialize($_POST['export_data']);

// file creation
$file = fopen($filename,"w");

foreach ($export_data as $line){


// download
header("Content-Description: File Transfer");
header("Content-Disposition: attachment; filename=".$filename);
header("Content-Type: application/csv; "); 


// deleting file

5. Conclusion

I exported a MySQL table data for this created an array of the content and write to the open file with fputcsv().

You can use it to generate a CSV output of a report.

If you want to know how to import a CSV file data into a MySQL database table you can view my earlier tutorial.

If you found this tutorial helpful then don't forget to share.

Spread the love
  • 1


  1. Dheerendra Yadav said:

    thanks it’s helpfull for me

    March 6, 2019
  2. jeeva rathinam said:

    Hi, i want CSV to send by mail like attachment. not necessary to view in the web.

    March 30, 2019

Leave a Reply

Your email address will not be published. Required fields are marked *