Export Data to CSV File using PHP and MySQL

CSV (comma-separated values) is the most popular file format to store data in plain text for offline uses. Generally, a CSV file is used to import and export data for moving data between programs. Import and export data is the most used feature in the web application, and CSV file format is the best choice for that.

The import and export, both features are implemented easily with PHP in the web application. In the previous tutorial, we have discussed about Import CSV File Data into MySQL database using PHP. In this tutorial, we will show you how to export data from MySQL database to CSV file using PHP.

To demonstrate Export to CSV functionality, we will build an example script that will export member’s data from the MySQL database and save it in a CSV file using PHP.

• Fetch data from the database using PHP and MySQL.
• Create a CSV file in PHP and save data in it.
• Export MySQL data and download it in a CSV file using PHP.

Create Database Table

To store the data, a table needs to be created in the database. The following SQL creates a members table with some basic fields in the MySQL database.

CREATE TABLE `members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `gender` enum('Male','Female') COLLATE utf8_unicode_ci NOT NULL,
  `country` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `created` datetime NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1=Active | 0=Inactive',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Database Configuration (dbConfig.php)

The dbConfig.php is used to connect the database. Specify the database host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName) as per your MySQL database credentials.

<?php 
// Database configuration 
$dbHost     = "localhost"; 
$dbUsername = "root"; 
$dbPassword = "root"; 
$dbName     = "codexworld"; 
 
// Create database connection 
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName); 
 
// Check connection 
if ($db->connect_error) { 
    die("Connection failed: " . $db->connect_error); 
}

Data List from MySQL Database (index.php)

Initially, all the member’s data is fetched from the database and listed in a tabular format.

• An EXPORT button is placed at the top of the data list.
• By clicking the Export button, the data is exported from the database and allow to download on a local drive as a CSV file.

<!-- Export link -->
<div class="col-md-12 head">
    <div class="float-right">
        <a href="exportData.php" class="btn btn-success"><i class="dwn"></i> Export</a>
    </div>
</div>

<!-- Data list table --> 
<table class="table table-striped table-bordered">
    <thead class="thead-dark">
        <tr>
            <th>#ID</th>
            <th>Name</th>
            <th>Email</th>
            <th>Gender</th>
            <th>Country</th>
            <th>Created</th>
            <th>Status</th>
        </tr>
    </thead>
    <tbody>
   <?php 
    // Fetch records from database 
    $result = $db->query("SELECT * FROM members ORDER BY id ASC"); 
    if($result->num_rows > 0){ 
        while($row = $result->fetch_assoc()){ 
    ?>
        <tr>
            <td><?php echo $row['id']; ?></td>
            <td><?php echo $row['first_name'].' '.$row['last_name']; ?></td>
            <td><?php echo $row['email']; ?></td>
            <td><?php echo $row['gender']; ?></td>
            <td><?php echo $row['country']; ?></td>
            <td><?php echo $row['created']; ?></td>
            <td><?php echo ($row['status'] == 1)?'Active':'Inactive'; ?></td>
        </tr>
    <?php } }else{ ?>
        <tr><td colspan="7">No member(s) found...</td></tr>
    <?php } ?>
    </tbody>
</table>

For this example script, the Bootstrap library is used to style the HTML table and buttons. So, include the Bootstrap CSS library and custom stylesheet file (if any).

<!-- Bootstrap library -->
<link rel="stylesheet" href="assets/bootstrap/bootstrap.min.css">

<!-- Stylesheet file -->
<link rel="stylesheet" href="assets/css/style.css">

Export Data to CSV File using PHP (exportData.php)

The exportData.php file handles the data export and CSV file download process using PHP and MySQL.

• Retrieve data from the MySQL database.
• Create a file pointer using fopen() function.
• Specify the header columns and put data into the CSV file.
• Output each row of the data, format line as CSV, and write to file pointer.
• Set Content-Type and Content-Disposition to force the browser to download the file rather than display it.

<?php 
 
// Load the database configuration file 
include_once 'dbConfig.php'; 
 
// Fetch records from database 
$query = $db->query("SELECT * FROM members ORDER BY id ASC"); 
 
if($query->num_rows > 0){ 
    $delimiter = ","; 
    $filename = "members-data_" . date('Y-m-d') . ".csv"; 
     
    // Create a file pointer 
    $f = fopen('php://memory', 'w'); 
     
    // Set column headers 
    $fields = array('ID', 'FIRST NAME', 'LAST NAME', 'EMAIL', 'GENDER', 'COUNTRY', 'CREATED', 'STATUS'); 
    fputcsv($f, $fields, $delimiter); 
     
    // Output each row of the data, format line as csv and write to file pointer 
    while($row = $query->fetch_assoc()){ 
        $status = ($row['status'] == 1)?'Active':'Inactive'; 
        $lineData = array($row['id'], $row['first_name'], $row['last_name'], $row['email'], $row['gender'], $row['country'], $row['created'], $status); 
        fputcsv($f, $lineData, $delimiter); 
    } 
     
    // Move back to beginning of file 
    fseek($f, 0); 
     
    // Set headers to download file rather than displayed 
    header('Content-Type: text/csv'); 
    header('Content-Disposition: attachment; filename="' . $filename . '";'); 
     
    //output all remaining data on a file pointer 
    fpassthru($f); 
} 
exit; 
 
?>

Export Data to Excel in PHP

Conclusion

This example code provides an easy way to export data to CSV file in PHP. You can enhance or customize the functionality of this Export to CSV script as per your needs. If you want to implement this export functionality in client-side script, use JavaScript to do it – Export HTML Table Data to CSV using JavaScript