Home > Backend Development > PHP Tutorial > How Can I Export MySQL Query Results to CSV in PHP?

How Can I Export MySQL Query Results to CSV in PHP?

DDD
Release: 2024-12-08 22:16:19
Original
490 people have browsed it

How Can I Export MySQL Query Results to CSV in PHP?

Exporting MySQL Query Results to CSV in PHP

To efficiently convert a MySQL query result to a CSV file in PHP, you can utilize the following options:

Utilizing the SELECT * INTO OUTFILE Syntax

One approach is to employ the SELECT * INTO OUTFILE syntax, which enables you to export the results directly to a file on the server. For instance:

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

Programmatic Method Using PHP

Alternatively, you can use a programmatic approach:

$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

The above is the detailed content of How Can I Export 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
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template