Download thousands of DB records in CSV using a chunk strategy to avoid out of memory errors

download-stream.php

<?php

header('Content-Type: text/event-stream');
header('Cache-Control: no-cache');
header('Connection: keep-alive');
header('X-Accel-Buffering: no'); // Disable nginx buffering

// Prevent timeout
set_time_limit(0);
ini_set('max_execution_time', 0);

$limit = 1000;
$offset = 0;
$chunkNumber = 0;

while (true) {
    $results = $db->query("SELECT * FROM table LIMIT $limit OFFSET $offset");
    
    if (empty($results)) {
        // Signal completion
        echo "event: complete\n";
        echo "data: {\"message\": \"Download complete\"}\n\n";
        flush();
        break;
    }
    
    // Convert chunk to CSV/JSON
    $chunkData = convertToFormat($results); // Your format function
    
    // Send chunk
    echo "event: chunk\n";
    echo "data: " . json_encode([
        'chunk' => base64_encode($chunkData), // or just the data if text
        'number' => $chunkNumber,
        'size' => strlen($chunkData)
    ]) . "\n\n";
    
    flush();
    ob_flush();
    
    unset($results, $chunkData);
    $offset += $limit;
    $chunkNumber++;
    
    // Small delay to prevent overwhelming client
    usleep(10000); // 10ms
}