How to Export database records to CSV File using PHP and MySQL

Pradeep Maurya
export data to csv file using php and mysql

Import and export CSV file data is mostly used in web based application. Generally, CSV file data is used to import and export tabular data between programs because CSV is most popular file format to store tabular data in plain text.

In this article, i will show you how to export mysqldatabase table records to CSV file using PHP. Also , you will get the knowledge to create CSV file in PHP and download the save MySQL data in CSV file.

Before start export to CSV functionality, we will create table in mysql database to store some users records.

Create Users Table in MySql Database

The following MySql query create a users table with basic fields in database.The Users table holds the all user information which is used to export in CSV file.

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
 
CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `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'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
--
-- Dumping data for table `users`
--
 
INSERT INTO `users` (`id`, `name`, `email`, `phone`, `created`, `modified`, `status`) VALUES
(1, 'Pradeep', 'pradeep@gmail.com', '9999999999', '2017-08-10 00:00:00', '2017-08-10 00:00:00', '1'),
(2, 'Vikas', 'vikas@gmail.com', '9999999999', '2017-08-11 00:00:00', '2017-08-11 00:00:00', '1'),
(3, 'Mohit', 'mohit@gmail.com', '988888888', '2017-08-12 00:00:00', '2017-08-12 00:00:00', '0'),
(4, 'Jasbir', 'jasbir@gmail.com', '8888888888', '2017-08-13 00:00:00', '2017-08-13 00:00:00', '1');
 
 

Create MySql database connection

dbconnection.php

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
 
<?php
//DB details
$dbHost     = 'localhost';
$dbUsername = 'root';
$dbPassword = '';
$dbName     = 'csvtutorials';
 
//Create connection and select DB
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
 
if($db->connect_error){
    die("Unable to connect database: " . $db->connect_error);
}
 
?>
 
 

Create index.php file to export data from database using PHP and MySql

index.php

Retrieved user’s data from the database is listed in the HTML table and  Export link  at the top of the table that navigates to the exportcsvData.php file to export table data to CSV file.

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
 
 
<!DOCTYPE html>
<html>
<head>
<title>How to Export database records to CSV File using PHP and MySQL</title>
<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
</head>
<body>
<div class="container">
    <div class="panel panel-default">
        <div class="panel-heading">
            Users Record
            <a href="exportcsvData.php" class="btn btn-success pull-right">Export Users Data to CSV File</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 'dbconnection.php';
                    
                    //get Users 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']; ?></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>
</body>
</html>
 
 

exportcsvData.php

In this file, we will retrive data from database and create file pointer using fopen() function. Specify the header columns and put into CSV file.

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 
 
<?php
//include database configuration file
include 'dbconnection.php';
 
//get User 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 Table 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;
 
?>
 
 

Conclusion

In the example script, you will get an easy way to export data to CSV file in PHP. You can modify the export functionality as per your requirements.

Are you want to get implementation help, or modify or extend the functionality of this script? Submit paid service request


[sociallocker]

[/sociallocker]

Related posts