php - 无法使用 pdo 导出到 csv

Question

我正在从数据库中检索数据并将数据下载到 csv 文件中。该功能按预期工作，但是正在下载的 csv 文件还包含页面源代码，我不知道为什么会发生这种情况，并且已经尝试了很长时间。我希望有人能帮助我解决这个问题。下面是源代码:

public function exportSalesCsv()
{

$conn = new PDO('mysql:host=' . config::get('mysql/host') . ';dbname=' . config::get('mysql/db'), config::get('mysql/username'), config::get('mysql/password'));
$sql = "SELECT 
            CONCAT(u.name, ' ', u.surname) AS salesPerson,
            t.initiation_timestamp AS saleDate,
            t.customer_account_id AS customerAccount,
            tt.transaction_type AS transactionType,
            r.resource_unique_value AS soldResource,
            CONCAT(rb.resource_brand,
                    ' ',
                    rm.resource_model,
                    ' ',
                    rt.resource_type) AS resourceModel,
            r.voucher_value_id as value
        FROM
            ims.transactions t
                INNER JOIN
            ims.resources r ON t.resource_id = r.resource_id
                INNER JOIN
            ims.resource_models rm ON r.resource_model_id = rm.resource_model_id
                INNER JOIN
            ims.resource_brands rb ON rm.resource_brand_id = rb.resource_brand_id
                INNER JOIN
            ims.resource_types rt ON rm.resource_type_id = rt.resource_type_id
                INNER JOIN
            ims.users u ON t.uid = u.uid
                INNER JOIN
            ims.transaction_types tt ON t.transaction_type_id = tt.transaction_type_id
        WHERE
            t.transaction_type_id = 3
                AND YEAR(t.initiation_timestamp) = YEAR(CURDATE()) 
        UNION SELECT 
            CONCAT(u.name, ' ', u.surname) AS salesPerson,
            o.closing_timestamp AS saleDate,
            o.customer_id AS customerAccount,
            tt.transaction_type AS transactionType,
            r.resource_unique_value AS soldResource,
            CONCAT(rb.resource_brand,
                    ' ',
                    rm.resource_model,
                    ' ',
                    rt.resource_type) AS resourceModel,
            r.voucher_value_id as value
        FROM
            orders o
                INNER JOIN
            ims.users u ON o.initiation_uid = u.uid
                INNER JOIN
            ims.transaction_types tt ON o.order_type_id = tt.transaction_type_id
                INNER JOIN
            ims.resources r ON o.resource_id = r.resource_id
                INNER JOIN
            ims.resource_models rm ON r.resource_model_id = rm.resource_model_id
                INNER JOIN
            ims.resource_brands rb ON rm.resource_brand_id = rb.resource_brand_id
                INNER JOIN
            ims.resource_types rt ON rm.resource_type_id = rt.resource_type_id
        WHERE
            o.order_type_id = 4
                AND order_status_id = 1
                AND YEAR(o.closing_timestamp) = YEAR(CURDATE())";
$results = $conn->query($sql);

$response = implode(",", array('salesPerson','saleDate','customerAccount','transactionType','soldResource','resourceModel','value'));
$response .= "\n";

foreach($results as $row)
{
    $response .= implode(",",array($row['salesPerson'], $row['saleDate'], $row['customerAccount'], $row['transactionType'], $row['soldResource'], $row['resourceModel'], $row[escape('value')]));
    $response .= "\n";
}

header('Content-type: text/csv');
header('Content-disposition: attachment;filename=salesExport.csv');

echo $response;

return;

}

以下屏幕截图解释了我的意思:Click Here

这是我调用该方法的方式:

<?php
require_once 'core/init.php';
include_once ("header.php");

if ($user->isLoggedIn()){

//check if user has permission
if ($user->hasPermission('reports') || $user->hasPermission('allAccess')){

    $inventory = new inventory();

    if(isset($_POST['exportSalesCsv'])){

        $inventory->exportSalesCsv();

    }...

Answer 1

您的脚本仅将文件保存到磁盘并填充数据，但不会在调用响应中返回

如果创建的文件可由 apache/nginx 服务器访问，您可以重定向到它。将其添加到函数的末尾。

header('Location: ' . $url_to_file);
return;

另一种解决方案是不打印到文件，而是写入变量并与返回 CSV 文件的 header 一起返回，以便浏览器将其解释为 csv 文件:

$results = $conn->query($sql);

$response = implode(",", array('salesPerson','saleDate','customerAccount','transactionType','soldResource'));
$response .= "\n";

foreach($results as $row)
{
    $response .= implode(",",array($row['salesPerson'], $row['saleDate'], $row['customerAccount'], $row['transactionType'], $row['soldResource']));
    $response .= "\n";
}

header('Content-type: text/csv');
header('Content-disposition: attachment;filename=' . strtotime("now") . '.csv');

echo $response;

return;