Export MySQL Records to a CSV File

A CSV (comma-separated values) file is a plain-text interchange format in which each record normally occupies one line and fields are separated by a delimiter. CSV is useful for downloads, spreadsheet exchange and lightweight data transfer.

Example CSV data

1,"John Deo",Four,75,female
2,"Max Ruin",Three,85,male

Fields containing commas, quotes or line breaks need correct CSV escaping. Instead of joining database values manually, PHP's fputcsv() should format each row.

Read the records with PDO

SELECT id, name, class, mark, gender
FROM student
ORDER BY id;

Download database records as CSV

<?php
// config.php creates the PDO connection in $dbo.
require 'config.php';

header('Content-Type: text/csv; charset=UTF-8');
header('Content-Disposition: attachment; filename="my-student-data.csv"');

$output = fopen('php://output', 'w');
fputcsv($output, ['id', 'name', 'class', 'mark', 'gender']);

$stmt = $dbo->query('SELECT id, name, class, mark, gender FROM student ORDER BY id');
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
    fputcsv($output, $row);
}

fclose($output);
exit;
?>

The download headers must be sent before normal page output. php://output streams the CSV directly to the browser, so the complete file does not have to be assembled in memory first.

Related tutorials

Writing CSV with fputcsv() · Reading CSV with fgetcsv() · Download headers · PDO connection · PDO records · Copy rows between tables · Copy a table

Student table SQL dump

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: