The bufferred and unbuffered queries can be used for a limited amount of records.
For example; while implementing download CSV for a query using buffered way, memory limit issues comes up above 30,000 records to be buffered.
Similarly, For unbuffered the load switched to database server.
This load on both the web (buffered) and MySQL (unbuffered) servers can be reduced as below supporting download CSV for 30,000+ records.
<?php
$shellCommand = 'mysql '
. '--host='.escapeshellarg($hostname).' '
. '--user='.escapeshellarg($username).' '
. '--password='.escapeshellarg($password).' '
. '--database='.escapeshellarg($database).' '
. '--execute='.escapeshellarg($sql).' '
. '| sed -e \'s/"/""/g ; s/\t/","/g ; s/^/"/g ; s/$/"/g\'';
header("Content-type: text/csv");
header("Content-Disposition: attachment; filename={$csvFilename}");
header("Pragma: no-cache");
header("Expires: 0");
echo shell_exec($shellCommand);
?>
There will be a bit of CPU consumption for the sed regex.