[RESOLVED] Export to CSV Script tweak?

_sh

Hi all,

I'm currently using the following code to export the contents of a SQL Query to a CSV download, its working beautifully however I need to add the column headers to the top of the CSV file but can't work out how to retrieve them and alter my script.

Could someone please help?

<?php
	$result = mysql_query($csvsql, $conn);
	$out = ''; 

$columns = mysql_num_fields($result);

// Add all values in the table to $out. 
while ($l = mysql_fetch_array($result)) {
for ($i = 0; $i < $columns; $i++) {
$out .='"'.$l["$i"].'",';
}
$out .="\n";
}

header('Content-type: application/csv');
header('Content-Disposition: attachment; filename="export_' . date("Y-m-d") . '.csv"');
echo $out;
exit;
?>

Thanks!

NogDog

This is how I'd likely do it (with some other tweaks to make use of the [man]fgetcsv/man function).

<?php
$result = mysql_query($csvsql, $conn);
$csvFile = tmpfile();
// get headings:
$row = mysql_fetch_assoc($result);
fputcsv($csvFile, array_keys($row));
mysql_data_seek($result, 0); // reset result row pointer
// Add all values in the table to $out.
while ($row = mysql_fetch_row($result)) {
   fputcsv($csvFile, $row);
}
rewind($csvFile); // file pointer back to beginning
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename="export_' . date("Y-m-d") . '.csv"');
fpassthru($csvFile); // outputs the tmp file
fclose($csvFile);
exit;
?>

bradgrafelman

A couple of slight modifications to NogDog's example code (to skip using an external temp file):

<?php
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename="export_' . date("Y-m-d") . '.csv"');
$stdout = fopen('php://stdout');

$result = mysql_query($csvsql, $conn);
$row = mysql_fetch_assoc($result);
fputcsv($csvFile, array_keys($row));

// Add all values in the table to $out. 
do {
   if($row)
      fputcsv($stdout, $row);
} while ($row = mysql_fetch_row($result));

fclose($stdout); 
exit; 
?>

_sh

bradgrafelman;10983664 wrote:

A couple of slight modifications to NogDog's example code (to skip using an external temp file):

<?php
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename="export_' . date("Y-m-d") . '.csv"');
$stdout = fopen('php://stdout');

$result = mysql_query($csvsql, $conn);
$row = mysql_fetch_assoc($result);
fputcsv($csvFile, array_keys($row));

// Add all values in the table to $out. 
do {
   if($row)
      fputcsv($stdout, $row);
} while ($row = mysql_fetch_row($result));

fclose($stdout); 
exit; 
?>

Thanks to both of you, really appreciate the help!

_sh

Something wrong here, I have just implemented it and found that although the CSV file can be downloaded, it contains a loop of error messages:

Here's one of the loops...

Warning: fopen() expects at least 2 parameters, 1 given in /home/results.php on line 137

Warning: fputcsv() expects parameter 1 to be resource, null given in /home/results.php on line 141

bradgrafelman

Ah, well I was just tweaking it on-the-fly without testing it. This:

$stdout = fopen('php://stdout');

should have been this:

$stdout = fopen('php://stdout', 'w');

_sh

bradgrafelman;10983781 wrote:
Ah, well I was just tweaking it on-the-fly without testing it. This:
$stdout = fopen('php://stdout');
should have been this:
$stdout = fopen('php://stdout', 'w');

Sorry for the late reply. I have just tried adding this modification to the script but the CSV file that downloads still throws an error but no longer contains the looped errors:

<br />
<b>Warning</b>:  fputcsv() expects parameter 1 to be resource, null given
in <b>/home/results.php</b> on line <b>119</b><br />

bradgrafelman

Change the first parameter of the fputcsv() call so that it matches the variable name of the file resource (e.g. what [man]fopen/man returns) that was opened.

_sh

bradgrafelman;10984105 wrote:
Change the first parameter of the fputcsv() call so that it matches the variable name of the file resource (e.g. what [man]fopen/man returns) that was opened.

Evening Brad! So I end up with this?

	header('Content-type: application/csv');
	header('Content-Disposition: attachment; filename="export_' . date("Y-m-d") . '.csv"');
	$stdout = fopen('php://stdout', 'w');

$result = mysql_query($csvsql, $conn);
$row = mysql_fetch_assoc($result);
fputcsv($stdout, array_keys($row));

// Add all values in the table to $out. 
do {
	if($row)
	fputcsv($stdout, $row);
}	while ($row = mysql_fetch_row($result));

fclose($stdout); 
exit;

It still doesn't work, now I get a blank CSV without any errors?

bradgrafelman

For one, comment out the two header() lines so you can just view the output in your browser window - makes debugging a tad easier.

Second, do you know if the SQL query is actually returning any results? Try outputting the number of rows that were matched - what do you get?

_sh

bradgrafelman;10984113 wrote:
For one, comment out the two header() lines so you can just view the output in your browser window - makes debugging a tad easier.

Second, do you know if the SQL query is actually returning any results? Try outputting the number of rows that were matched - what do you get?

I tried removing the header rows which then just returns a blank screen - echoing the number of rows shows 27 (which is correct for this specific query). Any other ideas?

bradgrafelman

Yeah, one more... try changing the fopen() to open the resource named 'php://output' and see if the data shows up (I'm hoping/betting it will - got my PHP streams all mixed up 🙁).

_sh

bradgrafelman;10984116 wrote:
Yeah, one more... try changing the fopen() to open the resource named 'php://output' and see if the data shows up (I'm hoping/betting it will - got my PHP streams all mixed up 🙁).

You are a genius 😃 I can now go to bed!!

Thanks for your help Brad, good work!