PHP: Writing a MYSQL query to CSV

Question

I'm attempting to write a MySQLi query to a downloadable CSV. The following headers open a stream for the CSV:

$fileName = ''; //empty file name, file name is cast later
header("Cache=Control: must-revalidate, post-check=0, pre-check=0");
header('Content-Description: File Transfer');
header("Content-type: text/csv");
header("Content-Disposition: attachment; filename={$fileName}");
header("Expires: 0");
header("Pragma: public");
$fh = @fopen( 'php://output', 'w' );

Below this I have the following which attempts to query and cast each row of the query to a new line in a CSV file:

if(isset($_POST["Submit"]) && ($_POST['Weight'] == 'Weight')){
    $fileName .= 'OutputWeightNull.csv';
    $query = mysqli_query($conn, 'SELECT * FROM  `Some_Table` WHERE WEIGHT = 0 OR weight IS NULL')or die(mysql_error());
    $result = mysqli_fetch_array($query) or die(mysql_error());
    $headerDisplayed = false;
    foreach ($result as $data){
    if (!headerDisplayed){
    fputcsv($fh, array_keys());
    $headerDisplayed = true;
        }
        fputcsv($fh, $data);
    }
}

The CSV is downloading to the browser as desired, however it's appearing empty, the query results are not being sent to the CSV. Could anyone point me in the right direction as to why this might be.

This is my my first attempt at a PHP script more complex that the hello world. Apologies if the answers are very simple or obvious.

Answer 1

MySQL has a built in query type for outputting data direct to CSV format.

Modify your SELECT query so it's in the following format:

SELECT fieldlist FROM table
INTO OUTFILE 'filename.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Now you won't have to do any of that looping or anything like that; all PHP has to do is run that query, then do a readfile() on the CSV file generated by MySQL, and finally delete it from disk afterward.

This will save a lot of memory for your PHP program, and will also run a lot faster (it'll be really noticable if you have a large data set).

Answer 2

Try like this

if(isset($_POST["Submit"]) && ($_POST['Weight'] == 'Weight')){
 $fileName = 'OutputWeightNull.csv';
 $fh = @fopen( 'php://output', 'w' );
 header("Cache=Control: must-revalidate, post-check=0, pre-check=0");
 header('Content-Description: File Transfer');
 header("Content-type: text/csv");
 header("Content-Disposition: attachment; filename={$fileName}");
 header("Expires: 0");
 header("Pragma: public");

 $query = mysqli_query($conn, 'SELECT * FROM  `Some_Table` WHERE WEIGHT = 0 OR weight IS NULL')or die(mysql_error());
 $headerDisplayed = false;
 while($result = mysqli_fetch_array($query, MYSQLI_ASSOC)) {  
  if (!$headerDisplayed){
   fputcsv($fh, array_keys($result));
   $headerDisplayed = true;
  }
  fputcsv($fh, $result);
 }
}

you need to place the csv header inside 'if' loop

Answer 3

Cleaning the code up a touch to loop around the returned rows, and use the mysqli_ error functions:-

<?php 

if(isset($_POST["Submit"]) && ($_POST['Weight'] == 'Weight'))
{
    $fileName = ''; //empty file name, file name is cast later
    header("Cache=Control: must-revalidate, post-check=0, pre-check=0");
    header('Content-Description: File Transfer');
    header("Content-type: text/csv");
    header("Content-Disposition: attachment; filename={$fileName}");
    header("Expires: 0");
    header("Pragma: public");
    $fh = @fopen( 'php://output', 'w' );

    $fileName .= 'OutputWeightNull.csv';
    $query = mysqli_query($conn, 'SELECT * FROM  `Some_Table` WHERE WEIGHT = 0 OR weight IS NULL') or die(mysqli_error($conn));
    if ($result = mysqli_fetch_array($query, MYSQLI_ASSOC))
    {
        fputcsv($fh, array_keys($result));
        fputcsv($fh, $result);
        while ($result = mysqli_fetch_array($query, MYSQLI_ASSOC))
        {
            fputcsv($fh, $result);
        }
    }
}