skip to content

A PHP admin panel's CSV export of 500,000 rows exhausts memory or arrives only at the end; how do you stream it as a download?

level: seniorimportance: should knowfreq 35%

answer

  1. headers first, then the first row
  2. close stray buffers before streaming
  3. write rows to php://output
  4. flush() does not touch ob_ buffers
  5. status is frozen after the first byte

basics

~20 s

Set the download headers, close any open output buffers, then write each row to php://output as it is fetched and call flush() periodically. Validate everything before the first row, because once output starts the status and headers can no longer change.

solid answer

~40 s

Memory blows up when something holds the whole file: rows collected into an array, or an unlimited output buffer (`output_buffering = On`, or an `ob_start()` with the default chunk size of 0). So: do all checks and queries that can fail **first**; set `Content-Type: text/csv` (PHP appends `;charset=UTF-8` for `text/` types) and `Content-Disposition: attachment; filename="users.csv"`; close every open buffer with `while (ob_get_level() > 0) { ob_end_clean(); }`; then open `php://output`, fetch rows one at a time and write each one, calling `flush()` every few thousand rows. `flush()` pushes PHP's server-API buffer onward but does not touch `ob_*` buffers, and the web server may still buffer. Once the first row is out, headers are sent: a failure at row 300,000 cannot become a 500, so log it and end the file visibly incomplete.

code

php · 22 lines
php
<?php
declare(strict_types=1);

$rows = openUserExport($_GET);     // application: validates, prepares query; may throw

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

while (ob_get_level() > 0) {
    ob_end_clean();                  // drop stray output and unlimited buffers
}

$out = fopen('php://output', 'w');
$n = 0;
foreach ($rows as $row) {           // a generator: one row in memory at a time
    fputcsv($out, $row, ',', '"', '');
    if (++$n % 5000 === 0) {
        flush();
    }
}
fclose($out);
exit;

go deeper

for a junior

Recall that a download needs its headers before any output, and that writing rows one at a time uses less memory than building the whole file.

for a middle

Explain why unlimited output buffers defeat streaming, how to close them, and the difference between flush() and ob_flush().

for a senior

Order the handler so failures happen before headers, stream with bounded memory, and plan for errors after the first byte.

for a principal

Decide when exports stay synchronous and when they move to background jobs with downloadable results, weighing size, timeouts and user experience.

## Where the memory and the delay come from A large export fails in one of two ways: the script hits `memory_limit`, or the browser shows nothing until the whole file is ready. Both mean something is holding the entire file before sending it: - the code builds an array of all rows, or one big string, before writing; - an **output buffer without a size limit** is active: `output_buffering = On`, or an `ob_start()` somewhere in the framework with the default `$chunk_size` of `0`, which keeps everything until the buffer is closed; - a compression layer, or the web server in front of PHP, buffers the response. The first two are inside PHP and are yours to fix. Streaming means every row leaves the process shortly after it is produced, so memory stays flat. ## The order of operations 1. **Everything that can fail goes first.** Authorisation, parameter validation, and preparing the query. If any of it fails, you can still send a proper error status and page. 2. **Set the headers.** `Content-Type: text/csv` - PHP appends `;charset=UTF-8` (from `default_charset`) to any `text/` type without a charset - and `Content-Disposition: attachment; filename="users-2026-09-29.csv"` so the browser saves rather than displays it. Add `Cache-Control` as your policy requires. 3. **Close stray output buffers.** `while (ob_get_level() > 0) { ob_end_clean(); }` discards anything already echoed (a stray newline would corrupt the first line) and removes unlimited buffers, including the automatic one from `output_buffering`. 4. **Write rows as they arrive.** Open `php://output`, which writes through PHP's output layer, fetch rows from the database one at a time, and write each one; CSV encoding options are a separate subject. 5. **Flush periodically.** Call `flush()` every few thousand rows so PHP hands its data to the web server. 6. **End cleanly.** Close the stream and `exit`, so no template or footer is appended. ## What `flush()` does and does not do | Layer | Emptied by | |---|---| | user output buffers (`ob_start()`, `output_buffering`) | `ob_flush()` / `ob_end_flush()`, not `flush()` | | PHP's server-API write buffer | `flush()` | | the web server or proxy in front of PHP | its own configuration | | the browser | nothing you control | This is why step 3 matters: with an unlimited user buffer still open, `flush()` does nothing useful. And a web server that buffers whole responses will still deliver the file at the end, which is its configuration to change, not PHP's. ## Failure after the first byte As soon as the first row leaves PHP, the status line and headers are sent. From then on: - `http_response_code(500)` warns "Cannot set response code - headers already sent" and returns `false`; - `header('Location: ...')` is dropped with the "headers already sent" warning; - the user has already received a `200` and part of a file. So a database error at row 300,000 cannot be reported through HTTP. The practical options are to log it, stop writing, and make the truncation visible (for example, a final line saying the export failed), or to generate large exports in the background and let the user download a finished file. ## Checking that it really streams - Watch the process's memory during an export; with streaming it should stay flat as rows grow. - Request the export with a command-line HTTP client and watch bytes arrive while the query is still running; if everything arrives at once, some layer is buffering. - Confirm `ob_get_level()` is `0` right before the loop in a debug build. - Check the response headers: the `Content-Type` and `Content-Disposition` you set, and no `Content-Length`, since the size is not known in advance. ## Checklist for the admin export - no rows collected in arrays; one row in memory at a time; - no open output buffers during the stream; - `flush()` every N rows; - validation before headers; - `exit` at the end.

  • Why does calling flush() inside the loop not help while ob_start() is active?
    `flush()` empties PHP's server-API write buffer, but it does not touch user-level output buffers. With an `ob_start()` buffer open, the rows are still accumulating in that buffer, so nothing reaches the server API to flush. Close the buffers first, or call `ob_flush()` followed by `flush()`.
  • The export query fails at row 300,000; why can't the handler return a 500 status?
    The status line and headers were sent with the first row, so the client already has a `200` and part of the body. `http_response_code(500)` now only warns "Cannot set response code - headers already sent" and returns `false`. Log the error, stop writing and mark the file as incomplete, or move large exports to a background job.

saying these in an interview costs you the question

  • flush() also empties buffers opened with ob_start().
  • Collecting all rows in an array first is fine for a streamed download.
  • A failure mid-stream can still be turned into a 500 response.
  • output_buffering = On streams output in 4 KB chunks.
  • Stray whitespace before the headers cannot affect a CSV download.