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();

    }...

@u_mulder I need a prompt to popup and ask the user for the download location — Iommiund
– Iommiund, Commented Apr 1, 2017 at 12:43
@u_mulder I`m kind of an ametaur, hence my post here, so I would appreciate your help on this matter. thanks — Iommiund
– Iommiund, Commented Apr 1, 2017 at 12:49
Don't just try to guess how functions work. Look at the examples in the documentation and, if they're obscure to you, find examples or tutorials somewhere else. — Álvaro González
– Álvaro González, Commented Apr 1, 2017 at 16:27

Alex Efimov · Accepted Answer · 2017-04-01 16:21:43Z

2

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;

edited Apr 1, 2017 at 16:21

answered Apr 1, 2017 at 12:49

Alex Efimov

3,7531 gold badge27 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

u_mulder Over a year ago

+= for strings is javascript approach.

Alex Efimov Over a year ago

Works in PHP also.

Iommiund Over a year ago

@AlexEfimov This is very close to what I wanted, my only problem now is that for some reason the csv file consists of the source code for the page instead of the returned data from db. But the rest is what I expected to happen.

Alex Efimov Over a year ago

Which solution are you using? Are you trying to also write to file on disk and return the output? Or redirect?

Iommiund Over a year ago

@AlexEfimov I am trying the second solution, it works as I expected, but when I open the csv file this contains the source code along with the results returned.

|

Collectives™ on Stack Overflow

cannot export to csv using pdo

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related