How to Efficiently Export MySQL Query Results to CSV in PHP?

Efficient MySQL Query Conversion to CSV in PHP

To convert a MySQL query to CSV in PHP, a popular method is to leverage SQL's INTO OUTFILE command. With this approach, you can specify the output destination and formatting options:

SELECT * INTO OUTFILE "/path/to/file.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n"
FROM my_table;

Another efficient option is to programmatically fetch the query results and build the CSV manually:

$select = "SELECT * FROM my_table";
$export = mysql_query($select);
$fields = mysql_num_fields($export);

$header = '';
for ($i = 0; $i < $fields; $i++) {
    $header .= mysql_field_name($export, $i) . "\t";
}

$data = '';
while ($row = mysql_fetch_row($export)) {
    $line = '';
    foreach ($row as $value) {
        $value = str_replace('"', '""', $value);
        $line .= '"' . $value . '"' . "\t";
    }
    $data .= trim($line) . "\n";
}

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=my_file.csv");
print "$header\n$data";

This approach allows you to customize the field delimiters and quote characters to suit your specific requirements.

The above is the detailed content of How to Efficiently Export MySQL Query Results to CSV in PHP?. For more information, please follow other related articles on the PHP Chinese website!