Skip to main content

How to Export Data to CSV File using PHP and MySQL

CSV (comma-separated values) is the most popular file format to store tabular data in plain text. Generally, CSV file is used to import and export data for moving tabular data between programs. Import and export data is the most used feature in the web application, and CSV file format is a best chose for that.
In the earlier tutorial, we have shown How to 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. Also, you will learn to create CSV file in PHP and download and save MySQL data in CSV file using PHP.
To demonstrate Export to CSV functionality, we will build an example script which will export members data from the MySQL database and save in a CSV file using PHP.

Create Table in MySQL Database

The following SQL creates a members table with some basic fields in MySQL database. The members table holds the member’s information which needs to be exported.

CREATE TABLE `members` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `phone` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
 `created` datetime NOT NULL,
 `modified` datetime NOT NULL,
 `status` enum('1','0') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Connect to the MySQL database (dbConfig.php)

To export data from the database, we need to connect to the MySQL database. The dbConfig.php file is used to connect and select the database using PHP and MySQL.

<?php
//DB details
$dbHost     = 'localhost';
$dbUsername = 'root';
$dbPassword = '*****';
$dbName     = 'codexworld';

//Create connection and select DB
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);

if($db->connect_error){
    die("Unable to connect database: " . $db->connect_error);
}

Export to CSV File using PHP

The following two files will be used to export data from database using PHP and MySQL.
index.php

The Bootstrap library is used to style the HTML table and Export link. You can omit it if you don’t want to use Bootstrap style.
<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
The member’s data is retrieved from the MySQL database and listed in the HTML table. The Export link will be provided at the top of the table that navigates to the exportData.php file to export table data to CSV file.

<div class="container">
    <div class="panel panel-default">
        <div class="panel-heading">
            Members list
            <a href="exportData.php" class="btn btn-success pull-right">Export Members</a>
        </div>
        <div class="panel-body">
            <table class="table table-bordered">
                <thead>
                    <tr>
                      <th>Name</th>
                      <th>Email</th>
                      <th>Phone</th>
                      <th>Created</th>
                      <th>Status</th>
                    </tr>
                </thead>
                <tbody>
                <?php
                    //include database configuration file
                    include 'dbConfig.php';
                    
                    //get records from database
                    $query = $db->query("SELECT * FROM members ORDER BY id DESC");
                    if($query->num_rows > 0){ 
                        while($row = $query->fetch_assoc()){ ?>                
                    <tr>
                      <td><?php echo $row['name']; ?></td>
                      <td><?php echo $row['email']; ?></td>
                      <td><?php echo $row['phone']; ?></td>
                      <td><?php echo $row['created']; ?></td>
                      <td><?php echo ($row['status'] == '1')?'Active':'Inactive'; ?></td>
                    </tr>
                    <?php } }else{ ?>
                    <tr><td colspan="5">No member(s) found.....</td></tr>
                    <?php } ?>
                </tbody>
            </table>
        </div>
    </div>
</div>
exportData.php

In the exportData.php file, the following works are processed.
  • Retrieve data from the MySQL database.
  • Create a file pointer using fopen() function.
  • Specify the header columns and put 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 displayed.

<?php
//include database configuration file
include 'dbConfig.php';

//get records from database
$query = $db->query("SELECT * FROM members ORDER BY id DESC");

if($query->num_rows > 0){
    $delimiter = ",";
    $filename = "members_" . date('Y-m-d') . ".csv";
    
    //create a file pointer
    $f = fopen('php://memory', 'w');
    
    //set column headers
    $fields = array('ID', 'Name', 'Email', 'Phone', '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['name'], $row['email'], $row['phone'], $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

Our example script provides an easy way to export data to CSV file in PHP. You can extend the export functionality as per your requirements. If you want to implement this export functionality in client-side script, it can be easily done using JavaScript – Export HTML Table Data to CSV using JavaScript
Are you want to get implementation help, or modify or extend the functionality of this script? Submit paid service request