Home > Database > Mysql Tutorial > How to Efficiently Convert MySQL Query Results to CSV in PHP?

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

Linda Hamilton
Release: 2024-12-24 16:59:17
Original
594 people have browsed it

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;
Copy after login

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";
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template