PHP export MySQL data to Excel file (fputcsv)_PHP tutorial

// Output the Excel file header, you can replace user.csv with the file you want Name
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="user.csv"');
header('Cache -Control: max-age=0');

// Get data from the database. In order to save memory, do not read the data into the memory at once. Just read it line by line from the handle
$ sql = 'select * from tbl where ...';
$stmt = $db->query($sql);

// Open the PHP file handle, php://output means direct output Go to the browser
$fp = fopen('php://output', 'a');

// Output Excel column name information
$head = array('name', ' Gender', 'Age', 'Email', 'Phone', '...');
foreach ($head as $i => $v) {
// CSV Excel supports GBK encoding, Must be converted, otherwise the code will be garbled
$head[$i] = iconv('utf-8', 'gbk', $v);
}

// Write the data to fputcsv File handle
fputcsv($fp, $head);

// Counter
$cnt = 0;
// Every $limit line, refresh the output buffer, not too big , not too small
$limit = 100000;

// Fetch data row by row without wasting memory
while ($row = $stmt->fetch(Zend_Db::FETCH_NUM)) {

$cnt ++;
if ($limit == $cnt) { //Refresh the output buffer to prevent problems caused by too much data
ob_flush();
flush ();
$cnt = 0;
}

foreach ($row as $i => $v) {
$row[$i] = iconv('utf- 8', 'gbk', $v);
}
fputcsv($fp, $row);
}