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.

Demo Download

Create a Table
Database Configuration
HTML and PHP
Create and Download CSV file
Demo
Conclusion

1. Create a Table

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. Database 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 that 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 download.php file for CSV export.

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 prepare 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.

Contents