cannot export to csv using pdo

Question

I am retrieving data from the database and downloading the data into a csv file. the functionality is working as expected, however the csv file that is being downloaded also contains the page source code, I do not know why this is happening and have been trying for a long time. I hope someone can help me with this. below is the source code:

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;

}

The following screenshot explains what I mean: Click Here

And here is how I am invoking the method:

<?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

Your script only saves the file to disk and is populated with data, but it is not return in the response of call

One solution if the created file is accessible by apache/nginx server you could redirect to it. Add this to the end of your function.

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

Another solution is not to print to file but write to a variable and return it with the headers for returning a CSV file, so that the browser interprets it as a csv file:

$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;