Export data from MySQL table to CSV file using PHP

In the previous article, we have explained how to import data from CSV file to MySQL table and in this article, you will learn how to export data from MySQL table to CSV file using PHP. For this, we will create a user interface that contains an export data button. On click this button, the data will be exported to csv file and downloaded.

CSV file stands for "Comma-separated-values", as it uses comma to separate values. This is a widely used file format that stores data in a tabular format. In web development, we can easily import/export to a CSV file and we can also convert any file format to a CSV file. The data in MySQL database are stored in rows. With the help of PHP, we can easily store each database row values to a comma separated sequence.

Export data from MySQL table to CSV file using php

Suppose, we have an 'employee' table in MySql database. You can either copy paste this in your database OR use your existing data.

CREATE TABLE IF NOT EXISTS `employee` (
  `emp_id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_name` varchar(150) NOT NULL,
  `email` varchar(150) NOT NULL,
  `phone` varchar(100) NOT NULL,
  `created_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_enabled` int(11) NOT NULL,
  PRIMARY KEY (`emp_id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

INSERT INTO `employee` (`emp_id`, `emp_name`, `email`, `phone`, `created_date`, `is_enabled`) VALUES
(1, 'John', This email address is being protected from spambots. You need JavaScript enabled to view it.', '2323234543', '2019-06-05 15:36:07', 1),
(2, 'Smith', This email address is being protected from spambots. You need JavaScript enabled to view it.', '9898577442', '2019-05-14 15:36:07', 1),
(3, 'Priska', This email address is being protected from spambots. You need JavaScript enabled to view it.', '9393452387', '2019-05-28 15:36:07', 1),
(4, 'Gaga', This email address is being protected from spambots. You need JavaScript enabled to view it.', '8482764537', '2019-06-29 15:36:07', 1);

config.php

Now, let's create a configuration file to connect to MySQL database. Make sure to replace the 'hostname', 'username', 'password' and 'databasename' with your database credentials and name.

<?php
$conn = new mysqli('hostname', 'username', 'password', 'databasename');
//Check for database connection error
if($conn->connect_error){
  die("Error in DB connection: ".$conn->connect_errno." : ".$conn->connect_error);    
}
?>

Next, we will create a main PHP file 'index.php', that we will call on the browser. This file contains code to select data from MySQL and display in tabular format. We have added a button 'Export Data'. When user clicks on this button data will be exported to a CSV file.

index.php

<?php
 include 'config.php';
?>
<!DOCTYPE html>
<html lang="en">
<head>
  <title>Export data from MySQL table to CSV file using php</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<?php
$select = "SELECT * FROM `employee` ";
$result = $conn->query($select);
if($result->num_rows > 0){ 
 echo '<table class="table table-striped">';
 echo '<tr><th>Employee Name</th>'; 
 echo '<th>Email</th>'; 
 echo '<th>Phone</th>';  
 echo '<th>Enabled</th>';  
 echo '</tr>';
 while($row = $result->fetch_object()){
   $status = ($row->is_enabled == '1')?'Yes':'No';
   echo '<tr>';  
   echo '<td>'. $row->emp_name.'</td>';
   echo '<td>'. $row->email.'</td>';
   echo '<td>'. $row->phone.'</td>';
   echo '<td>'. $status.'</td>';
   echo '</tr>';
 }
 echo '</table>';
}
?>
<a href="exportdata.php" class="btn btn-primary">Export Data</a>
</div>
</body>
</html>

exportdata.php

On click the 'Export Data' button, it is directed to this page -

<?php

include 'config.php';

$select = "SELECT * FROM `employee` ";
$result = $conn->query($select);

if($result->num_rows > 0){
    $separator = ",";
    $filename = "employee_" . date('Y-m-d') . ".csv";
    // Set header content-type to CSV and filename
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="' . $filename . '";');
    // create a file pointer connected to the output stream
    $output = fopen('php://output', 'w');

    //set CSV column headers
    $fields = array('ID', 'Name', 'Email', 'Phone', 'Enable');
    fputcsv($output, $fields, $separator);
    
    
    while($row = $result->fetch_object()){ 
        $status = ($row->is_enabled == '1')?'Yes':'No';
        $lineData = array($row->emp_id, $row->emp_name, $row->email, $row->phone, $status);
	fputcsv($output, $lineData, $separator);
    }
	
    fclose($output);
    exit();
}
?>

In the above file, we have set the content type to 'text.csv'. This makes the CSV file downloadable. Also, set the filename in the header.

header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="' . $filename . '";');

The 'php://output' is write only stream. It allows us to write to the output buffer mechanism. It sends CSV file directly to the browser.

$output = fopen('php://output', 'w');

The 'fputcsv()' function is used to format the field values as a line of CSV and write to the file pointer ($output). The '$separator' is a delimiter and an optional parameter. It allows one character only.