How to Export MySQL Table data as CSV file in PHP

The phpMyAdmin allows us different ways to export the Table data. One of the 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.

How to Export MySQL Table data as CSV file in PHP


Contents

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

1. Table structure

I am using users table in the example.

CREATE TABLE `users` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `username` varchar(80) NOT NULL,
  `name` varchar(50) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `email` varchar(70) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Configuration

Create a new config.php to define database connection.

Completed Code

<?php
$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

<?php 
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;'>
    <tr>
     <th>ID</th>
     <th>Username</th>
     <th>Name</th>
     <th>Gender</th>
     <th>Email</th>
    </tr>
    <?php 
     $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);
   ?>
      <tr>
       <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>
      </tr>
   <?php
    }
   ?>
   </table>
   <?php 
    $serialize_user_arr = serialize($user_arr);
   ?>
  <textarea name='export_data' style='display: none;'><?php echo $serialize_user_arr; ?></textarea>
 </form>
</div>

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

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

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

foreach ($export_data as $line){
 fputcsv($file,$line);
}

fclose($file);

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

readfile($filename);

// deleting file
unlink($filename);
exit();

5. Demo

View Demo


6. 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 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.
Are you want to get implementation help, or modify or extend the functionality of this script? Submit paid service request.

35 thoughts on “How to Export MySQL Table data as CSV file in PHP”

  1. Hello, your code does not work …
    The downloaded CSV file is empty.

    something happens in the file download.php Please help.

    Reply
  2. This is fantastic – thanks. I would like one additional feature. My downloaded file has all of the data but not the headers (column titles)
    How can I include them in the export?

    Reply
      • This seems to overwrite the first output line of the query, in the csv file.
        So when using this, I can see the headers however the first data line is missing.
        Please advise.

      • nvm…. I fixed it by putting arr[0] above the arr[].
        Like:
        $user_arr[0] = xxxxxxxxxxxxxxxxx
        $user_arr[] = xxxxxxxxxxxxxxxxx

      • Are you able to show the code that you did by adding in those lines? I have tried to add this and it shuts down my site saying it can’t handle the request.

  3. Here is a odd thing. I had a working setup. It was in the root of my website. I wanted to put the setup in a dir of its own. I created a directory named ‘export’ and put the files in it. Then the export part failed. The main page revealed the data I wanted but the csv file was always blank. NOTE: My website is a WordPress installation. So this may be a WordPress ‘feature’
    Any ideas?

    Reply
  4. I have been just leaving it as such:

    I have also tried:
    ( download.php is located in the same dir.)
    And

    In each case it initiates the download and I get a downloaded csv file. It’s just empty.
    I honestly think this is a WordPress issue somehow. I have resisted making these pages “theme” supported for simplicity. I have been able to put a login script in front of your stuff so I *can* leave it all in the root.

    Reply
  5. HA – it stripped the tags – DUH
    Trying again
    I have been just leaving it as such:
    “”
    I have also tried:
    “” ( download.php is located in the same dir.)
    And
    “”
    In each case it initiates the download and I get a downloaded csv file. It’s just empty.
    I honestly think this is a WordPress issue somehow. I have resisted making these pages “theme” supported for simplicity. I have been able to put a login script in front of your stuff so I *can* leave it all in the root.

    Reply
  6. Last time with no tags – please delete the other posts

    form method=’post’ action=’../export/download.php’
    or
    ‘export/download.php’
    or
    download.php

    All create a empty csv file.

    Reply
  7. Getting 2 errors with your script: Notice: unserialize(): Error at offset 155747 of 410837 bytes in Warning: Invalid argument supplied for foreach() in

    Any ideas on how to fix? I’ve did some googling but no luck, the errors are in the download file.

    Reply
  8. Its using POST, infact its the same as your script provided in the article. Any other suggestions? I can send you my script if you like for you to glance at? Cheers
    Jake.

    Reply
  9. downloads well for me but the table formatting is removing all 0 in front. I want to apply text kind of formatting or just export the details as it is without it removing the preceding zeros.

    Reply
  10. Thanks for this wonderful content , but I am also facing the same issue as @Steve Devine . Always getting empty CSV.

    Reply
  11. For anyone possibly having the empty CSV file, please check and make sure that the Internet User has writing permission in the directory you’re using. May not work for all, but worth a check.

    Reply
  12. Hi Yogesh, This is a really great blog and example. One question, is there a good method to include the table headers or other text content in the download csv?

    Reply

Leave a Comment