2 Steps To Export From MYSQL To CSV In PHP (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.

 

 

QUICK SLIDES

 

TABLE OF CONTENTS

Download & Notes	Export CSV	Useful Bits & Links
Tutorial Video	The End

 

 

 

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` int(11) NOT NULL,
  `user_email` varchar(255) NOT NULL,
  `user_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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` int(11) 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 = "utf8";
$dbUser = "root";
$dbPass = "";
try {
  $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
    ]
  );
} catch (Exception $ex) { exit($ex->getMessage()); }

// (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
$stmt = $pdo->prepare("SELECT * FROM `users`");
$stmt->execute();
while ($row = $stmt->fetch()) {
  echo implode(",", [$row["user_id"], $row["user_email"], $row["user_name"]]);
  echo "\r\n";
}

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 HTTP “save as CSV file” headers.
3. Finally, we just fetch the users from the database and output them – CSV is literally text, using a comma to separate each column.

 

 

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 = "utf8";
$dbUser = "root";
$dbPass = "";
try {
  $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
    ]
  );
} catch (Exception $ex) { exit($ex->getMessage()); }

// (B) CREATE EMPTY CSV FILE ON SERVER
$csvFile = "export.csv";
$handle = fopen($csvFile, "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()) {
  // print_r($row);
  fputcsv($handle, [$row["user_id"], $row["user_email"], $row["user_name"]]);
}
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($csvFile, "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(300);
ignore_user_abort(true);

// (B) PREVENT MULTIPLE EXPORT
// (B1) CHECK IF LOCK EXIST, CREATE LOCK IF NOT
if (file_exists("LOCK-USER-ID.TXT")) { exit("EXPORT ALREADY RUNNING"); }
else { file_put_contents("LOCK-USER-ID.TXT", "1"); }

// (B2) RUN EXPORT AS USUAL
// ...
 
// (B3) SEND EMAIL NOTIFICATION TO USER WHEN DONE
mail("USER@MAIL.COM", "SUBJECT", "DONE - YOU CAN DOWNLOAD REPORT AT LINK HERE");

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

Now, the above examples will work well enough, but you can expect a couple of things to screw up…

• When exporting large sets of data, the script will most likely take a long time and timeout. We can use set_time_limit() to change the time limit.
• Impatient users will hammer reload, causing multiple exports to spawn. Not good. We can prevent this with:
  • Setting ignore_user_abort(true), the script will continue to run even after the user closes the page or navigates away.
  • Create a dummy LOCK-USER-ID.TXT. If this file exists, a CSV export is already running, we don’t allow the user to do another export.
  • Export to the CSV file as usual, but this time to a secured private drive or password-protected area.
  • Send an email to the user with the download link on complete, delete the dummy LOCK-USER-ID.TXT.

 

 

LINKS & REFERENCES

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

 

TUTORIAL VIDEO

 

INFOGRAPHIC CHEAT SHEET