Introduction

Exporting a MySQL query to CSV in PHP is usually just a loop: run the query, fetch rows, and write them with fputcsv. The important parts are using a proper database API, handling headers and encoding deliberately, and streaming the output so large exports do not consume too much memory.

Query with PDO and Stream the Output

A clean PDO-based example looks like this:

1<?php
2$pdo = new PDO('mysql:host=localhost;dbname=app;charset=utf8mb4', 'user', 'pass');
3$stmt = $pdo->query('SELECT id, name, email FROM users ORDER BY id');
4
5$output = fopen('php://output', 'w');
6fputcsv($output, ['id', 'name', 'email']);
7
8while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
9    fputcsv($output, $row);
10}
11
12fclose($output);

fputcsv handles proper field quoting, which is one reason it is better than building CSV lines by string concatenation.

Send It as a Download

If the goal is a browser download, add headers before writing output:

header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename="users.csv"');

Then keep writing to php://output. This streams the CSV directly to the client instead of creating a temporary file first.

Keep the Query Safe and Explicit

If the query uses user input, switch from query() to a prepared statement:

$stmt = $pdo->prepare('SELECT id, name, email FROM users WHERE status = :status');
$stmt->execute(['status' => 'active']);

Security matters here because export endpoints often feel “internal” and then quietly become externally reachable later.

Handle Large Exports as Streams

For large result sets, streaming row by row is the right default. It avoids loading the full result into memory before writing the CSV.

That is why a fetch loop plus fputcsv is usually better than fetching all rows into an array first.

Decide Whether to Include a Header Row

Many CSV consumers expect column names on the first line. If you know the schema up front, write them explicitly. If the columns are dynamic, you may need to inspect the first row or query metadata and then emit a matching header line.

The key is to be deliberate. Some integrations want headers, some do not.

CSV Shape Is Part of the Contract

Think about how downstream tools will consume the file. Column order, header names, delimiter expectations, and newline behavior are all part of whether the export is actually useful once it leaves PHP.

Writing to a File Uses the Same Core Pattern

If the CSV should be saved on disk instead of streamed to the browser, open a real file handle rather than php://output and keep the same fputcsv loop. The export logic stays the same; only the destination changes.

Common Pitfalls

• Building CSV lines manually instead of using fputcsv.
• Fetching the full query result into memory before writing the export.
• Forgetting download headers when the output should be a browser file download.
• Using raw user input in the query instead of prepared statements.
• Ignoring encoding and then being surprised when spreadsheet software displays characters incorrectly.

Spreadsheet Consumers Often Have Extra Expectations

Some CSV consumers care about UTF-8 BOMs, delimiter choice, or line-ending conventions. The export loop may be correct while the receiving tool still looks wrong because its CSV expectations differ from yours.

Summary

• Run the query, fetch rows, and write them with fputcsv.
• Use PDO and prepared statements for safer database access.
• Stream to php://output for browser downloads and large exports.
• Set CSV response headers when the result should download immediately.
• Let fputcsv handle quoting instead of manually building lines.