PHP: Download MySQL table

本文关键字：table MySQL Download PHP | 更新日期: 2023-09-27

我想将MySQL表下载为CSV文件，但是我收到此错误"允许的内存大小为1342177280字节已耗尽"。我想这是因为文件是首先创建然后下载的。我怎样才能实现用户可以从 0 开始下载文件，在这种情况下，我认为我不需要那么多内存。

这是我到目前为止的代码：

$output         = "";
$table          = "export_table";
$sql            = mysql_query("select * from $table");
$columns_total  = mysql_num_fields($sql);
// Get The Field Name
for ($i = 0; $i < $columns_total; $i++) {
    $heading    =   mysql_field_name($sql, $i);
    $output     .= '"'.$heading.'",';
}
$output .="'n";
// Get Records from the table
while ($row = mysql_fetch_array($sql)) {
for ($i = 0; $i < $columns_total; $i++) {
$output .='"'.$row["$i"].'",';
}
$output .="'n";
}
// Download the file
$filename =  "export.csv";
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename='.$filename);
echo $output;

所以最后这就是我想要的：

$db = mysqli_connect("localhost", "user", "password", "database");
$result = mysqli_query($db, "SELECT * FROM export_table", MYSQLI_USE_RESULT);
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header("Content-Disposition: attachment;filename='"export_table.csv'"");
header("Content-Transfer-Encoding: binary");
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: max-age=0, no-cache, must-revalidate, proxy-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private",false);
$output = fopen('php://output', 'w');
    fputcsv($output, array('ID','Column1','Column2','Column3'));
while ($row = mysqli_fetch_assoc($result))
    {
    fputcsv($output, $row);
    }
fclose($output);
mysqli_free_result($result);
mysqli_close($db);

在

遍历结果集之前，您必须移动标头函数，而不是分配给$output变量，而只是回显它。

// ...
$filename =  "export.csv";
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename='.$filename);
// Just dump the records you fetch from the db into output
while ($row = mysql_fetch_array($sql)) {
    $line = '';
    for ($i = 0; $i < $columns_total; $i++) {
        $line .= '"' . $row["$i"] . '",';
    }
    echo $line, "'n";
}

根据您的具体情况，这可能会有其问题，但是应该可以作为快速而肮脏的解决方案。

使用以下代码：-

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'dbName';
$tableName = 'tableName';
$mysqldump=exec('which mysqldump');   
$command = "$mysqldump --opt -h $dbhost -u $dbuser -p $dbpass $dbname $tableName > $tableName.sql";
exec($command);

如果您需要下载整个数据库，请将$command替换为以下行：-

$command = "$mysqldump --opt -h $dbhost -u $dbuser -p $dbpass $dbname  > $dbName.sql";

希望这对你有所帮助:)