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!

 

 

TABLE OF CONTENTS

 

DOWNLOAD & NOTES

Here is the download link to the example code, so you don’t have to copy-paste everything.

 

EXAMPLE CODE DOWNLOAD

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.

 

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

 

 

EXPORT DATA FROM MYSQL TO CSV

All right, let us now go into an example of how to export data from MySQL to a CSV file.

 

TUTORIAL VIDEO

 

STEP 1) DUMMY USERS DATABASE

1-users.sql
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)

2a-export-download.php
<?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)

2b-export-save.php
<?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.

 

EXTRAS

That’s all for the examples, and here are a few small extras that may be useful to you.

 

LINKS & REFERENCES

 

THE END

Thank you for reading, and we have come to the end of this guide. I hope that it has helped you with your project, and if you want to share anything, please feel free to comment below. Good luck and happy coding!

4 thoughts on “PHP Export Data From MYSQL To CSV (Simple Example)”

  1. Works beautifully, but it does not download the column headers, I have found examples to do this in mysqli, but not PDO do you have any examples we could follow

Leave a Comment

Your email address will not be published. Required fields are marked *