How to Export MySQL Table data as CSV file in PHP

Exporting MySQL table data to a CSV file is a common need in web development. It provides the flexibility of converting your database records into an easily readable and shareable format.

Whether you want to generate reports, transfer data to another system, or create backups, knowing how to export MySQL data to CSV using PHP can be incredibly useful.

This article guides you through the process of exporting MySQL table data as a CSV file using PHP.

How to Export MySQL Table data as CSV file in PHP

Demo Download

Table of Content

Create a Table
Establishing a Database Connection
Display MySQL table data and Enable CSV export
PHP: Exporting Data to 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
);

2. Establishing a Database Connection

Create a new config.php to define database connection.

<?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. Display MySQL table data and Enable CSV export

Write code to display users table data in tabular format on the frontend and allows users to export the data as a CSV file.

Creates a form with a submit button. Set <form > action attribute to "download.php".
Inside the form, a table structure is defined with column headers for ID, Username, Name, Gender, and Email.
Retrieve the users table data using a SELECT query and stores it in the $result variable.
Loop on the $result. The values from each row are assigned to variables ($id, $uname, $name, $gender, $email) and stored in an array ($user_arr).
Within the loop, the table rows are dynamically generated using echo statements to display the retrieved data in the respective table cells.
The $user_arr array is serialized using the serialize() function and stored in a hidden textarea named export_data. This serialized data will be used for exporting to CSV format.
Upon form submission, the serialized data in the textarea is sent to the download.php file for further processing, which is responsible for generating and delivering the CSV file.

<?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. PHP: Exporting Data to CSV File

Create a download.php file for CSV export.

1. File and Data Preparation:

Define a filename for the CSV file as “users.csv”.
The serialized data from the textarea, received via the $_POST as export_data, is unserialized using the unserialize() function and stored in the $export_data variable.

2. File Creation and CSV Writing:

A file with the specified filename is created using the fopen() function with the “w” mode, allowing writing to the file.
Using foreach loop iterate over the unserialized data array, and each line of data is written to the CSV file using the fputcsv() function.
Once all data is written, the file is closed using fclose().

3. Downloading the CSV File:

Headers are set to specify the file transfer and instruct the browser to treat the file as an attachment with the provided filename.
Set Content-Type header to “application/csv”.
The contents of the CSV file are read using the readfile() function, which outputs the file content for download.

4. Cleanup:

After the file has been downloaded, the file is deleted using the unlink() function to remove it from the server.

5. Exiting the Script:

The exit() function is called to terminate the script execution after the file has been downloaded and deleted, preventing any further processing.

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

The code we discussed offers a simple and straightforward way to export MySQL table data as a CSV file using PHP. By creating an array of the data and using the fputcsv() function, the code effectively writes the content into a file.

This capability provides several advantages for your PHP-based web applications. You can easily generate CSV files for creating reports, analyzing data, sharing information with external systems, and collaborating with others.

When utilizing this code, remember to customize it to fit your specific needs. Ensure that you name the files appropriately and consider any security measures required to protect sensitive data.

If you’re interested in learning how to import CSV file data into a MySQL database table using PHP, you can refer to this tutorial.

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