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.

Amalberga

what is the filename you get for the empty csv output? – Preglacial

The download is appearing only only as 'download'. I'm confident the condition of the IF statement is being met though. – Amalberga

mysqli_fetch_array will return a single row to php (ie, an array of fields), not the entire result set as an array of rows. This isn't the main issue with your script but does mean you will only every output the first row from your query. – Deanndeanna

Further comment. You are mixing mysqli_* and mysql_* calls. If you query fails your script dies, but tries to output mysql_error(). This will not be meaningful and is not related to mysqli_error(). – Deanndeanna

Answer 1

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

Preglacial

Brilliant, that's solved issue of the download appearing with the incorrect file name. Thanks for that. Unfortunately there is still no content in the file, including no header values. – Amalberga

Can you check my code now.?I have changed this line fputcsv($fh, array_keys($data));. and also the if (!$headerDisplayed){ – Preglacial

Thanks Arun, it makes sense to pass $data as an argument there. Unfortunately there's still no joy though, the csv file still appear blank with no headers. – Amalberga

The above code works for me.. Please check and let me know if any issue – Preglacial

Thanks Arun, that's brilliant. – Amalberga

Answer 2

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

Deanndeanna

Thanks for that, it doesn't seem to like the ',' however here: if ($result = mysqli_fetch_array($query), MYSQLI_ASSOC) . I'm searching as to why that might be. – Amalberga

I put a bracket in the wrong place. Done a few other minor tweaks. The fetch array method is described here - php.net/manual/en/mysqli-result.fetch-array.php – Deanndeanna

Thanks Kickstart, I still still seems to fall over on the following, if ($result = mysqli_fetch_array($query, MYSQLI_ASSOC)) I'm very new to programming in general so I'm struggling to see why the syntax you've used there won't work. – Amalberga

It should work (can't see any typos). Do you get an error message? How it should work is that it assigns the result of mysqli_fetch_array to the variable $result. It is in an IF statement so if the result is false (ie, the fetch failed, such as if no rows were returned) then the code inside the IF is bypassed. What I have done is to use an IF to get the first row (which you want to also process the headers for), and within the IF I have used a WHILE to process subsequent rows. – Deanndeanna

Sorry @Kickstart, I fear the server isn't keeping up with the TTL value. That works now, many thanks. Learnt a lot here. – Amalberga

Answer 3

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).

Scrutable

This is a good idea (and despite doing something with it yesterday I completely forgot about it!). Down side is that the output file is local the the MySQL server rather than the php script. So if you have a separate server for the databases it isn't really possible. – Deanndeanna

I did consider this, however I was hoping to have the CSV appear as a downloadable in the browser. My understanding is the above would appear in the directory on the server? Thanks – Amalberga

@LiamFell - yes, the query would generate a file on the server; you'd then use PHP's readfile() function to dump that file to the browser. So your PHP program would run the query, call readfile(), delete the file to clean up after itself, and then exit. You would still need the headers, but you wouldn't need to open the filehandle or loop through the results. – Scrutable