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

PHP Code for Converting MySQL Query to CSV

In PHP, there are efficient methods for converting MySQL query results to CSV format, minimizing the need for temporary files:

Method 1: SELECT INTO OUTFILE

SELECT * INTO OUTFILE "c:/mydata.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n"
FROM my_table;

This command directly exports the query results to a CSV file. Refer to the MySQL documentation for its syntax and options.

Method 2: Manual Conversion

$select = "SELECT * FROM table_name";
$export = mysql_query ($select) or die ("Sql error : " . mysql_error());

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

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

if ($data == "") $data = "\n(0) Records Found!\n";

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=your_desired_name.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";

This method involves looping through the query results row by row, formatting and outputting each field as a comma-separated value. The first row contains the field names.

These approaches provide portable solutions for exporting MySQL query results to CSV format, offering flexibility and efficient processing.

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