Storing data from MySQL table as CSV using PDO via browser

Question

I have a form that writes data to a MySQL database. I want the user to be able to download their data in CSV format after final submission.

My code is currently dumping the contents of the database into the browser, i.e. it is being written to the page, rather than to a csv file. I would like to send them to a link and be given the option to download a file.

Here is my current code:

$dbo = new PDO('mysql:host=localhost;dbname=db1', $username, $password);
$sql = "SELECT * FROM table1";
$qry = $dbo->prepare($sql);

// Execute the statement
$qry->execute();
var_dump($qry->fetch(PDO::FETCH_ASSOC));
$data = fopen('/tmp/db_user_export_".time().".csv', 'w');
while ($row = $qry->fetch(PDO::FETCH_ASSOC))
{
    echo "Success";
    // Export every row to a file
    fputcsv($data, $row);
}

The current result is a page with a dump of all the data from the table. No file is being created in the location desired. Where am I going wrong?

mbinette · Accepted Answer · 2012-09-22 06:57:33Z

2

The client doesn't know it is a CSV file (it's just text after all!).

Try to add this BEFORE any output (at the top of your script):

header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=file.csv");
header("Pragma: no-cache");
header("Expires: 0");

Basically, you're telling the client/browser that the data you will be sending a CSV file.

That should work.

You can find more information about headers there: http://php.net/manual/en/function.header.php

answered Sep 22, 2012 at 6:57

mbinette

5,0923 gold badges27 silver badges32 bronze badges

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

1 Comment

Gideon Over a year ago

That's awesome, made it work perfectly, thank you! Don't suppose you live in Vancouver so I can buy you a beer hah? Been trying to figure that out for ages.

Collectives™ on Stack Overflow

Storing data from MySQL table as CSV using PDO via browser

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related