Writing MySQL results to a new CSV with auto-download

[csdude55]

I have a somewhat complex MySQL (well, MariaDB) query with a handful of user-defined variables. After it processes on my end, I want to write the results to a CSV file and then automatically download it to the user's computer.

This is a special password-protected script, so I'm not forcing anything on anyone; the file is the whole reason they're here.

In phpMyAdmin, I can do this by running the query then click "Query results operations > Export", then when I select CSV it automatically downloads the file. That's what I'm trying to do in my own script.

My first thought was to do this in MySQL:

SELECT *
 INTO OUTFILE '/home/example/www/new.csv'
 FIELDS TERMINATED BY ','
 ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
  FROM tableA
  WHERE [blah blah blah]

and then supply a text link that the user could right-click and Save As. But when I run that in PMA I get an Access Denied error, even though the directory is owned by "root root" and chmod 777.

I could put the work on PHP by using a WHILE loop and manually creating the text, then use file_put_contents() to write the file. With 50+ columns it would be a little bit of a pain to write, but I'm pretty sure that would bypass the permission error.

But before I do that, can you guys and gals think of a "better" way to create the CSV file and let the user download the results?

[lammert]

You should not store the output in a file, but write it directly to the browser with the correct Mime type. Something like:

<?php
  header( "Content-Description: File Transfer" );
  header( "Content-Disposition: attachment; filename=export.csv" );
  header( "Content-Transfer-Encoding: binary" );
  header( "Content-Type: text/csv" );

  flush();
  // Here the contents produced by your SQL query
?>

[csdude55]

Ahh. So I would echo from a while loop that manually wraps each field in quotes and ends it with a comma, then ends the line with a "\n"? Eg (typed for this, not tested),

header( "Content-Description: File Transfer" );
header( "Content-Disposition: attachment; filename=export.csv" );
header( "Content-Transfer-Encoding: binary" );
header( "Content-Type: text/csv" );

flush();

$query = "SELECT blah blah blah";
$sth = mysqli_query($dbh, $query);

while (list($a, $b, $c,...) = mysqli_fetch_row($sth))
 echo <<<EOF
"$a","$b","$c",...

EOF;

[lammert]

Yes, that should work.