million data export to csv

million_data_export_to_csv.php

<?php
header('Content-Description: million-data-export');
header('Content-Type: application/csv');
header("Content-Disposition: attachment; filename=million-data-export.csv");
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');

$dsn = 'mysql:dbname=data-warehouse;host=127.0.0.1';
$user = 'root';
$password = '123';
$options = [
    PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false,
];

try {
    $dbh = new PDO($dsn, $user, $password, $options);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$sql = 'SELECT * FROM tbl WHERE id BETWEEN :start AND :end';
try {
    $fp = fopen('php://output', 'w');
    $stmt = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));

    for ($i=1; $i<=800; $i++) {
        $start = ($i-1)*10000+1;
        $end = $i*10000;
        $stmt->bindParam(':start', $start);
        $stmt->bindParam(':end', $end);
        $stmt->execute();

        while ($row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_NEXT)) {
            fputcsv($fp, $row);
        }
    }

    fclose($fp);
    $stmt = null;
} catch (PDOException $e) {
    print $e->getMessage();
}