Edited at

PHPで巨大なCSVをデータベースからダウンロードさせる

PDO::MYSQL_ATTR_USE_BUFFERED_QUERYをfalseしてstreamで出力するだけでOK

CSV/TSVについてはこれを参照

[PHP] Mac版Excelと互換性のあるCSVファイルを出来るだけ効率よく作成する

https://qiita.com/mpyw/items/2795bef3ed561f4cf4e9


WindowsとMacに対応(tsv)


tsv.php

<?php

header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=export.tsv");

try {
$pdo = new PDO('mysql:dbname=bigtable;host=127.0.0.1;port=3306;charset=utf8mb4',
'root',
'password',
[
\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
\PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
\PDO::ATTR_EMULATE_PREPARES => true,
\PDO::ATTR_TIMEOUT => 10,
\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false
]);

$stmt = $pdo->prepare("SELECT * FROM ...");

if ($stmt->execute()) {
$stream = fopen('php://output', 'wb');
stream_filter_prepend($stream, 'convert.iconv.UTF-8/UTF-16LE');
fwrite($stream, "\xEF\xBB\xBF");
fputcsv($stream, [
"ID",
"値",
"日付"
],"\t");

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
fputcsv($stream, [
$row['id'],
$row['wbgt_value'],
$row["wbgt_date"]
],"\t");
}
fclose($stream);
}
}catch (\PDOException $e){
var_dump($e);
}



Windows限定(csv)


csv.php

<?php

header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=export.csv");

try {
$pdo = new PDO('mysql:dbname=bigtable;host=127.0.0.1;port=3306;charset=utf8mb4',
'root',
'password',
[
\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
\PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
\PDO::ATTR_EMULATE_PREPARES => true,
\PDO::ATTR_TIMEOUT => 10,
\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false
]);

$stmt = $pdo->prepare("SELECT * FROM ...");

if ($stmt->execute()) {
$stream = fopen('php://output', 'wb');
stream_filter_prepend($stream, 'convert.iconv.UTF-8/CP932//TRANSLIT//IGNORE');
fputcsv($stream, [
"ID",
"値",
"日付"
],",");

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
fputcsv($stream, [
$row['id'],
$row['wbgt_value'],
$row["wbgt_date"]
],",");
}
fclose($stream);
}
}catch (\PDOException $e){
var_dump($e);
}


参考:

PHPで大量レコードを”垂れ流しで”CSVダウンロードする方法

https://blog.suzukishouten.co.jp/archives/1362

[PHP] Mac版Excelと互換性のあるCSVファイルを出来るだけ効率よく作成する

https://qiita.com/mpyw/items/2795bef3ed561f4cf4e9

php:// — さまざまな入出力ストリームへのアクセス

http://php.net/manual/ja/wrappers.php.php

PHPのstream wrapperをさわってみた

http://d.hatena.ne.jp/i_ogi/20100217/stream

phpmyadmin/libraries/classes/Plugins/Export/ExportCsv.php

https://github.com/phpmyadmin/phpmyadmin/blob/master/libraries/classes/Plugins/Export/ExportCsv.php