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!

 

 

TLDR – QUICK SLIDES

Fullscreen Mode – Click Here

 

 

TABLE OF CONTENTS

 

 

 

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.

 

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

EXAMPLE CODE DOWNLOAD

Click here for the source code on GitHub gist, just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

 

LINKS & REFERENCES

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

 

TUTORIAL VIDEO

 

INFOGRAPHIC CHEAT SHEET