Export MySQL Data to CSV or Excel in PHP (The Correct Way)

Exporting query results to a spreadsheet is one of the most requested features in any admin panel. It is also one of the easiest to get subtly wrong — in ways that corrupt data, exhaust memory on large tables, or hand an attacker code execution on your users' machines.

This guide covers exporting MySQL data to CSV and Excel in PHP properly: streaming output, the encoding Excel actually expects, escaping that survives real-world data, and the formula injection risk almost every tutorial ignores.

Why the common approach fails

The version that circulates widely builds a tab-separated string by hand and sends it with an .xls extension:

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=export.xls");
print "$header\n$data";

Four things are wrong with it:

• It is not an Excel file. It is tab-separated text wearing an .xls extension. Modern Excel shows a "the file format does not match the extension" warning before it will open it.
• Hand-rolled escaping breaks. Any value containing a quote, comma, tab or newline needs careful handling. Doing it manually with str_replace works until the first address field with a line break in it.
• Everything is concatenated into one string before sending. Export 200,000 rows and you exhaust memory_limit.
• No character encoding is declared, so accented characters and non-Latin scripts arrive as mojibake.

PHP has a built-in function that solves the escaping problem correctly: fputcsv(). Use it.

The correct CSV export

<?php
declare(strict_types=1);

$pdo = new PDO(
    'mysql:host=localhost;dbname=shop;charset=utf8mb4',
    $user,
    $pass,
    [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]
);

$filename = 'orders-' . date('Y-m-d') . '.csv';

header('Content-Type: text/csv; charset=UTF-8');
header('Content-Disposition: attachment; filename="' . $filename . '"');
header('Cache-Control: no-store, no-cache');

// Write straight to the response instead of building a string in memory.
$out = fopen('php://output', 'w');

// UTF-8 BOM — without it Excel on Windows assumes the legacy locale encoding
// and renders anything non-ASCII as garbage.
fwrite($out, "\xEF\xBB\xBF");

$stmt = $pdo->query('SELECT id, customer_name, email, total, created_at FROM orders');

$headerWritten = false;
foreach ($stmt as $row) {
    if (!$headerWritten) {
        fputcsv($out, array_keys($row));
        $headerWritten = true;
    }
    fputcsv($out, array_map('sanitiseCell', $row));
}

fclose($out);

Three details carry most of the value here.

php://output writes directly to the response stream. Memory usage stays flat regardless of row count, because nothing accumulates.

The UTF-8 BOM — those three bytes \xEF\xBB\xBF — is what tells Excel the file is UTF-8. Omit it and a customer named "Müller" opens as "Müller". This is the single most common complaint about PHP CSV exports.

fputcsv() handles quoting, embedded commas, embedded newlines and doubled quotes according to the CSV specification. You do not need to think about it.

The security issue: CSV formula injection

This is the part most tutorials omit entirely, and it matters.

When Excel or Google Sheets opens a CSV, any cell beginning with =, +, - or @ is interpreted as a formula, not text. If your application exports user-supplied data — names, addresses, support ticket subjects — an attacker can register with a "name" like:

=cmd|'/c calc.exe'!A1

When an administrator exports the user list and opens it, Excel prompts to enable the external link. Enough people click yes that this is a well-established attack, catalogued as CSV injection or formula injection.

The fix is to prefix any cell starting with those characters so the spreadsheet treats it as literal text:

function sanitiseCell(mixed $value): string
{
    $value = (string) $value;

    // A leading =, +, - or @ makes a spreadsheet treat the cell as a formula.
    // Tab, carriage return and newline can also start a formula context.
    if ($value !== '' && str_contains("=+-@\t\r", $value[0])) {
        return "'" . $value;
    }

    return $value;
}

Apply it to every cell that could contain user input. The leading apostrophe is not shown by Excel — it is the standard "treat as text" marker.

Large exports without exhausting memory

Streaming the output is only half the problem. By default PDO buffers the entire result set in PHP memory before you iterate it. For a million-row table that fails regardless of how you write the file.

Turn buffering off for the export query:

$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

$stmt = $pdo->query('SELECT id, customer_name, email, total FROM orders');

foreach ($stmt as $row) {
    fputcsv($out, array_map('sanitiseCell', $row));
}

With an unbuffered query, rows are pulled from MySQL one at a time. Memory stays constant whether you export a hundred rows or ten million.

Two caveats. You cannot run another query on the same connection until the result set is fully consumed. And long exports can hit max_execution_time, so for very large jobs either raise the limit, disable output buffering with ob_end_flush() so the browser sees progress, or move the export to a background job that emails a download link.

Producing a real Excel file

CSV is the right choice most of the time — universal, small, streamable. Use a genuine .xlsx file when you need multiple sheets, formatting, formulas, or column types that survive the round trip (long numbers that Excel would otherwise mangle into scientific notation, or leading zeros it would strip).

Install the standard library:

composer require phpoffice/phpspreadsheet

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\DataType;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Orders');

$sheet->fromArray(['ID', 'Customer', 'Email', 'Total'], null, 'A1');
$sheet->getStyle('A1:D1')->getFont()->setBold(true);

$rowNumber = 2;
foreach ($stmt as $row) {
    $sheet->setCellValue("A{$rowNumber}", $row['id']);
    $sheet->setCellValue("B{$rowNumber}", $row['customer_name']);

    // Force text so long IDs and leading zeros are not reformatted.
    $sheet->setCellValueExplicit("C{$rowNumber}", $row['email'], DataType::TYPE_STRING);

    $sheet->setCellValue("D{$rowNumber}", $row['total']);
    $rowNumber++;
}

$sheet->getStyle("D2:D{$rowNumber}")->getNumberFormat()->setFormatCode('#,##0.00');

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="orders.xlsx"');

(new Xlsx($spreadsheet))->save('php://output');

Note that PhpSpreadsheet builds the whole workbook in memory — roughly 1 KB per cell. For exports beyond about 50,000 rows, either use its ReadFilter and chunking features or fall back to CSV.

Checklist

• Use fputcsv() rather than concatenating strings — it escapes correctly.
• Write to php://output so memory stays flat.
• Emit the UTF-8 BOM so Excel does not mangle non-ASCII characters.
• Sanitise cells beginning with = + - @ to prevent formula injection.
• Disable PDO query buffering for large result sets.
• Send an honest Content-Type; do not label a text file .xls.
• Never interpolate user input into the SQL that drives the export — use prepared statements.
• Check authorisation before exporting. A bulk export endpoint is a data breach waiting to happen if it is not behind the same permission checks as the screen it mirrors.

Get these right and a CSV export is a few dozen lines that will handle any table you point it at, without corrupting a single accented character.