In this tutorial we are going to learn how to export data in csv file using php and mysql.
CSV(Comma Separated Values) is the most popular file format to store the data.
CSV file stores the data as comma separated format. Generally it used for to import data or to export data as tabular format.
To demonstrate export data in csv file we will take one example.

Create Table in MySql(Phpmyadmin)

Below snippet shows the users table structure, we have users table with some basic fields. The users table holds the basic user 
details which we are going to export in CSV file.

CREATE TABLE `users` (
 `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_at` datetime NOT NULL,
 `updated_at` 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(Phpmyadmin) config.php

Firstly, we need to connect to the mysql database to export the data in csv. Below are the code
Snippet. In config.php we have to provide the database host name, Database user name, Database password and database name.

<?php
//Database details
$db_host     = 'localhost';
$db_username = 'root';
$db_password = '*****';
$db_name     = 'tutorialsmint';

//Create connection and select DB
$db = new mysqli($db_host, $db_username, $db_password, $db_name);

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


Index.php Users details listing

In this file we have fetched the data from users table and shown in tabular format. On clicking export button users details will be exported in csv file.
For styling purpose we have used bootstrap css.

Below is the code snippet

<div class="container">
    <div class="panel panel-default">
        <div class="panel-heading">
            Users list
            <a href="export.php" class="btn btn-success pull-right">Export Users</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 users 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_at']; ?></td>
                      <td><?php echo ($row['status'] == '1')?'Active':'Inactive'; ?></td>
                    </tr>
                    <?php } }else{ ?>
                    <tr><td colspan="5">No user(s) found.....</td></tr>
                    <?php } ?>
                </tbody>
            </table>
        </div>
    </div>
</div>


Export.php 

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

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

if($query->num_rows > 0){
    $delimiter = ",";
    $filename = "users_" . 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_at'], $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;

?>

Written by
Check out this Author's contributed articles.
If you like Tutorialsmint and would like to contribute, you can also write an article using Write an Article. See your article appearing on the Tutorialsmint main page and help other programmers to enhance their knowledge.

Comments(0)

RECENT TUTORIALS
Wed Dec 26 • 08:00 AM GMT
Wed Dec 26 • 08:00 AM GMT
Wed Dec 26 • 08:00 AM GMT
Wed Dec 26 • 08:00 AM GMT
Wed Dec 26 • 08:00 AM GMT
Edit Post
Add Reply