PHP code to convert a MySQL query to CSV

PHP code to convert a MySQL query to CSV

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Avoiding Temporary Files When Exporting MySQL Data to CSV in PHP

Exporting query results directly from a database without relying on intermediate files on the server's filesystem is often a requirement for portable scripts or environments where disk space and permissions are restricted. The primary goal here is to stream data directly from mysql_query (or PDO) into the output buffer, ensuring that no temporary file needs to be created, written to, and subsequently deleted. This approach eliminates the need to configure specific directory paths or manage file-system permissions for the PHP process.

To achieve this, you must handle two distinct responsibilities: formatting the raw database rows into CSV syntax and managing the HTTP headers required for a browser download. If you skip the header configuration, the script will output plain text instead of triggering a file download, which breaks the user experience in web applications.

Formatting Data for CSV Compliance

The most common pitfall in CSV generation is not handling special characters correctly. A standard approach involves iterating over your result set and escaping commas, double quotes, and newline characters within the field values.

Consider a scenario where you have a simple query selecting user names and descriptions. You need to ensure that if a description contains a comma, it gets enclosed in quotes, and any existing double quotes inside the text are escaped by doubling them.

<?php
// Assume $pdo is your active PDO connection
$stmt = $pdo->query("SELECT name, description FROM users");

$csvRows = [];
foreach ($stmt as $row) {
    $name = $row['name'];
    $description = $row['description'];

    // Escape double quotes by doubling them
    $safeName = str_replace('"', '""', $name);
    $safeDescription = str_replace('"', '""', $description);

    // Enclose fields in quotes if they contain a comma or newline
    $field1 = strpos($safeName, ',') !== false || strpos($safeName, "\n") !== false ? '"' . $safeName . '"' : $safeName;
    $field2 = strpos($safeDescription, ',') !== false || strpos($safeDescription, "\n") !== false ? '"' . $safeDescription . '"' : $safeDescription;

    // Build the row string
    $csvRow = "$field1,$field2";
    
    // Add header row first if this is the initial iteration
    if ($name === $row['name']) { // This logic needs adjustment for actual headers, see below
         $header = "name,description\n";
         echo $header;
    } else {
        echo $csvRow . "\n";
    }
}
?>

A more robust implementation usually separates the header row logic from the loop. You can fetch columns using fetchColumnNames() if available in your driver, or simply define them based on the query structure. The key is that each iteration of the loop outputs a string directly to stdout, bypassing any file system operations entirely.

Handling Special Characters and Newlines

One specific technical detail often overlooked is the handling of newline characters within CSV fields. According to RFC 4180, if a field contains line breaks, double quotes, or commas, it must be enclosed in double-quotes. Furthermore, any double-quote character within that data must be escaped by preceding it with another double-quote.

The previous code snippet handles this, but a cleaner approach often uses str_replace combined with conditional checks for the enclosing quotes. This ensures that simple fields like "John Doe" do not receive unnecessary quote marks, keeping the CSV parsable by standard spreadsheet applications without cluttering the data view.

Triggering the Browser Download

Once the data is formatted, you must instruct the web browser to treat this output as a file download rather than displaying it inline. This requires setting the Content-Type header to text/csv and adding a Content-Disposition header that specifies the filename.

header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename="export_' . date('Y-m-d') . '.csv"');
header('Cache-Control: no-cache, must-revalidate'); // HTTP/1.1

// Start output buffering to ensure headers are sent before content
ob_start(); 

Using ob_start() is crucial here because it captures the output generated by your echo statements within the loop. While modern servers often handle this automatically, explicitly managing the output buffer ensures that headers are sent correctly without accidental whitespace issues (like BOM characters) interfering with the CSV format.

Performance Considerations for Large Datasets

When dealing with large result sets, memory consumption becomes a critical factor. Loading an entire dataset into an array before processing it is inefficient and can lead to Out of Memory errors on production servers. Streaming, as demonstrated in the examples above, processes one row at a time. This keeps the memory footprint constant regardless of whether you are exporting 100 rows or 10 million rows.

However, if you require complex calculations or aggregations on the client-side before generating the CSV, streaming might not be feasible. In those cases, processing the data in chunks using PDO::FETCH_COLUMN and fetching limited batches is a viable alternative to loading everything into memory at once.

For projects built with Laravel, utilizing packages like laravel-excel via Spatie offers a higher-level abstraction for these tasks. While they often rely on temporary files internally for large exports due to complexity, understanding the underlying streaming mechanics helps when configuring these libraries to optimize performance or handle specific edge cases where file system access is restricted.

Our network