Export data from MySQL table to CSV file using PHP
In the previous article, we explained how to import data from a CSV file into a MySQL table, and in this article, you will learn how to export data from a MySQL table to a CSV file using PHP. For this, we will create a user interface that contains an export data button. On clicking this button, the data will be exported to a CSV file and downloaded.
CSV file stands for "Comma-separated-values", as it uses a 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 the MySQL database is stored in rows. With the help of PHP, we can easily store each database row value as a comma-separated sequence.
Suppose we have an 'employee' table in our MySQL database. You can either copy and paste this into 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 the 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 in the browser. This file contains code to select data from MySQL and display it in tabular format. We have added a button 'Export Data'. When the user clicks on this button, the 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 the 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.
fputcsv($output, $fields, $separator);At last, we have closed an open file pointer.
fclose($output);Related Articles
Submit a form data without page refresh using PHP, Ajax and JavascriptPHP MySQL PDO Database Connection and CRUD Operations
How to insert image in database using PHP
Remove duplicates from array PHP
PHP sanitize input for MySQL
PHP random quote generator
PHP String Contains
PHP calculate percentage of total
PHP Fix: invalid argument supplied for foreach
Locking files with flock()
How to Pass an Array as URL Parameter in PHP
How to generate pdf in PHP using MySQL and MPDF Library
How to Export MySQL Table data as CSV file in PHP
Read CSV file & Import data into MySQL with PHP
Save an emoji in MySQL using PHP
Google reCAPTCHA v2 PHP example
Fetch data from database in PHP and display in PDF
How to create a PDF from a form using PHP
Form Validation with PHP
