PHP Export Data From MYSQL To CSV (Simple Example)

Welcome to a quick tutorial and example on how to export data from MYSQL to CSV in PHP. So you want to export a list from the database? Well, it is actually pretty easy to do. Let us walk through a simple example in this guide – Read on!

ⓘ I have included a zip file with all the example source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

TLDR – QUICK SLIDES

Fullscreen Mode – Click Here

 

 

TABLE OF CONTENTS

 

 

 

QUICK NOTES

• Create a test database and import 1-users.sql.
• Change the database settings in 2a-export-download.php and 2b-export-save.php to your own.
• Run 2a-export-download.php for a demo on export to a CSV download, 2b-export-save.php to save to a CSV file on the server.

If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

EXAMPLE CODE DOWNLOAD

Click here to download the example source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

 

STEP 1) DUMMY USERS DATABASE

CREATE TABLE `users` (
  `user_id` bigint(20) NOT NULL,
  `user_email` varchar(255) NOT NULL,
  `user_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `user_email` (`user_email`),
  ADD KEY `user_name` (`user_name`);

ALTER TABLE `users`
  MODIFY `user_id` bigint(20) NOT NULL AUTO_INCREMENT;
 
INSERT INTO `users` (`user_id`, `user_email`, `user_name`) VALUES
(1, 'Jane Doe', 'jane@doe.com'),
(2, 'Joe Doe', 'joe@doe.com'),
(3, 'John Doe', 'john@doe.com');

For this demo, we will use this simple users table with only 3 fields:

• user_id The user ID. Primary key, auto-increment.
• user_email The user’s email address.
• user_name The user’s name.

 

 

STEP 2A) EXPORT CSV (DOWNLOAD CSV FILE)

<?php
// (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN!
$dbHost = "localhost";
$dbName = "test";
$dbChar = "utf8mb4";
$dbUser = "root";
$dbPass = "";
$pdo = new PDO(
  "mysql:host=$dbHost;dbname=$dbName;charset=$dbChar",
  $dbUser, $dbPass, [
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_NAMED
]);

// (B) HTTP CSV HEADERS
header("Content-Type: application/octet-stream");
header("Content-Transfer-Encoding: Binary");
header("Content-disposition: attachment; filename=\"export.csv\"");
 
// (C) GET USERS FROM DATABASE + DIRECT OUTPUT
$out = fopen("php://output", "w");
$stmt = $pdo->prepare("SELECT * FROM `users`");
$stmt->execute();
while ($row = $stmt->fetch()) { fputcsv($out, $row); }
fclose($out);

Yep, that’s all for the so-called “difficult export to CSV”. Not going to explain line-by-line, this should be pretty straightforward:

1. Connect to the database, remember to change the settings to your own.
2. Output the “save as CSV file” HTTP headers.
3. Finally, we fetch the users from the database and output them using fputcsv().

 

 

STEP 2B) EXPORT MYSQL TO CSV (SAVE TO FILE ON SERVER)

<?php
// (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN!
$dbHost = "localhost";
$dbName = "test";
$dbChar = "utf8mb4";
$dbUser = "root";
$dbPass = "";
$pdo = new PDO(
  "mysql:host=$dbHost;dbname=$dbName;charset=$dbChar",
  $dbUser, $dbPass, [
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_NAMED
]);

// (B) CREATE EMPTY CSV FILE ON SERVER
$handle = fopen("export.csv", "w");
if ($handle === false) { exit("Error creating $csvFile"); }
 
// (C) GET USERS FROM DATABASE + WRITE TO FILE
$stmt = $pdo->prepare("SELECT * FROM `users`");
$stmt->execute();
while ($row = $stmt->fetch()) { fputcsv($handle, $row); }
fclose($handle);
echo "DONE!";

Here’s an alternative that will save the CSV file onto the server instead.

1. Connect to the database, as usual.
2. Create and open an empty CSV file $handle = fopen("export.csv", "w").
3. Get the users from the database, use fputcsv() to write them into the CSV file.

 

 

EXTRA) DEALING WITH LARGE EXPORTS

<?php
// (A) SET TIME LIMIT (SECONDS) + KEEP RUNNING EVEN IF PAGE CLOSED
set_time_limit(0);
ignore_user_abort(true);

// (B) PREVENT MULTIPLE EXPORT BY CREATING A "LOCK FILE"
if (file_exists("LOCK-USER-ID.TXT")) { exit("EXPORT ALREADY RUNNING"); }
else { file_put_contents("LOCK-USER-ID.TXT", "1"); }

// (C) SAVE INTO CSV FILE ON SERVER AS USUAL
// SELECT * FROM `TABLE`
// FPUTCSV(...)
 
// (D) SEND EMAIL NOTIFICATION TO USER WHEN DONE
mail("USER@MAIL.COM", "SUBJECT", "DONE - YOU CAN DOWNLOAD REPORT AT LINK HERE");

// (E) UNLOCK
unlink("LOCK-USER-ID.TXT");

The above examples work well enough, but always expect things to screw up. Especially when exporting large datasets, the PHP script will most likely take a long time and timeout. Here is a possible solution:

1. We can use set_time_limit(0) to allow the script to run forever and ignore_user_abort(true) to keep running even after the user closes the window.
2. Impatient users will hammer reload, causing multiple exports to spawn. We can prevent this by creating a dummy LOCK-USER-ID.TXT. If this “lock file” exists, we don’t allow the user to do another export.
3. Export to a CSV file as usual, but save into a secured private drive or password-protected area.
4. On export complete, send an email to the user with the download link.
5. Delete LOCK-USER-ID.TXT to “unlock” and allow another export process.

 

 

LINKS & REFERENCES

• File Open – PHP
• File Put CSV – PHP
• Set Time Limit – PHP
• PDO (Database Object) – PHP

 

TUTORIAL VIDEO

 

INFOGRAPHIC CHEAT SHEET