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
Firstly, here is the download link to the example code as promised.
QUICK NOTES
- Create a test database and import
1-users.sql
. - Change the database settings in
2a-export-download.php
and2b-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.
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.
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.
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:
- Connect to the database, remember to change the settings to your own.
- Output the HTTP “save as CSV file” headers.
- 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.
- Connect to the database, as usual.
- Create and open an empty CSV file
$handle = fopen($csvFile, "w")
. - 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
.
- Setting
USEFUL BITS & LINKS
That’s all for the examples, and here are a few small extras that may be useful to you.
LINKS & REFERENCES
- File Open – PHP
- File Put CSV – PHP
- Set Time Limit – PHP
- PDO (Database Object) – PHP
TUTORIAL VIDEO
INFOGRAPHIC CHEAT SHEET

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!
Thank you very much, 2b-export-save.php code file is i was locking for