Page 1 of 1

Opening CSV file

Posted: Fri Jun 18, 2010 3:32 pm
by marnieg
Here is my code to output some data from my database to a CSV file.

Two problems

1. It is writing the data as one long string. I thought the "\n" would have caused a line break.
2. It is writing the data to the browser. I want it to a file and where does the file get written.

Code: Select all

$query = mysql_query("SELECT distinct(enroll_email) FROM enrollment where enroll_email is not null and enroll_email <> ' ' order by enroll_email");
$file = 'export';
while($row = mysql_fetch_array($query)) {
               
               $enroll .= $row['enroll_email'] . "\n";
               }
              
$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/octetstream");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header("Content-disposition: filename=".$filename.".csv");
echo $enroll;
exit;

I have tried several types of headers and different file names but no difference. :?

Re: Opening CSV file

Posted: Fri Jun 18, 2010 3:48 pm
by Jade
Did you notice your CSV file doesn't have any commas....?

Re: Opening CSV file

Posted: Fri Jun 18, 2010 5:05 pm
by McInfo
marnieg wrote:1. It is writing the data as one long string. I thought the "\n" would have caused a line break.
Line breaks caused by \n are not visible on an HTML page unless you look in the page source or the \n is located between pre tags.
2. It is writing the data to the browser. I want it to a file and where does the file get written.
Do you want to save the file to the server?

Code: Select all

file_put_contents($filename, $data);
If you want to download the file, your script appears to work. It might benefit from slightly different headers, though.

Code: Select all

header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="'.$filename.'"');
Enable error_reporting so you can identify potential problems, such as using concatenation on an undefined variable.

Re: Opening CSV file

Posted: Mon Jun 21, 2010 9:26 am
by marnieg
So here is my code after I made the changes you suggested

$file = 'export';
while($row = mysql_fetch_array($query)) {

$enroll = $row['enroll_email']."\n";
}

$filename = $file."_".date("Y-m-d_H-i",time()."csv");
header('Content-type: text/csv');
header('Content-disposition: attachment; filename="'.$filename.'"');
file_put_contents($filename,$enroll);
exit;
?>
I'm getting two errors now. One saying undefined function for the file_put_contents. The other is the generic headers problem on the two header statements

Warning: Cannot modify header information - headers already sent by (output started at /homepages/41/d283671596/htdocs/emaillist.php:10) in /homepages/41/d283671596/htdocs/emaillist.php on line 13

Warning: Cannot modify header information - headers already sent by (output started at /homepages/41/d283671596/htdocs/emaillist.php:10) in /homepages/41/d283671596/htdocs/emaillist.php on line 15

Re: Opening CSV file

Posted: Mon Jun 21, 2010 9:41 am
by marnieg
Resolved the "cannot modify header information problem", there was a tab before the closure of the ?> line. Now I'm getting the error about the undefined function in my csv file. My hosting server supports PHP 4,5 and 6(beta). Do I need to hard code this function in my file or use a different one?

Re: Opening CSV file

Posted: Mon Jun 21, 2010 9:49 am
by marnieg
I did a phpinfo and my hosting company is defaulting to 4.4.9 and this function doesn't exist until 5.0 so I'll check with them on the configuration so that I can use this function.

Re: Opening CSV file

Posted: Mon Jun 21, 2010 10:17 am
by marnieg
Made the filename with extension .php5 to force hosting server to use this version and the function works now, but I'm not getting any data in my file.

Here's the final code again.

$query = mysql_query("SELECT distinct(enroll_email) FROM enrollment where enroll_email is not null and enroll_email <> ' ' order by enroll_email");
$file = 'export';
while($row = mysql_fetch_array($query)) {
$enroll = $row['enroll_email']."\n"; }
$filename = $file."_".date("Y-m-d_H-i",time()).".csv";
header('Content-type: text/csv');
header('Content-disposition: attachment; filename="'.$filename.'"');
file_put_contents($filename,$enroll);
exit;

My $enroll variable has the data I want in the file, which is just an email address from my database. This was working when I had it printing to the browser and not to a file.

Re: Opening CSV file

Posted: Mon Jun 21, 2010 10:42 am
by marnieg
Since I'm fetching multiple rows of data and placing them in the variable $enroll do I need to do an implode?

I tried changing the $enroll = $row statement to

$enroll .= $row['enroll_email']."\n";

and then the file_put_contents statement to
file_put_contents($filename, implode(", $enroll));

but now I get an "unexpected $end" error. I am using <?php tag so it's not the short tag problem. :?

Re: Opening CSV file

Posted: Mon Jun 21, 2010 10:58 am
by McInfo
marnieg wrote:

Code: Select all

header('Content-type: text/csv');
header('Content-disposition: attachment; filename="'.$filename.'"');
file_put_contents($filename,$enroll);
Choose one or the other -- use headers to force download or use file_get_contents() to save to the server. It doesn't make sense to send these headers if you don't send CSV data along with them.

In this code, $enroll will contain only the last email returned by the query.

Code: Select all

while ($row = mysql_fetch_array($query)) {
    $enroll = $row['enroll_email']."\n";
}
Before you concatenate strings onto $enroll, $enroll needs to be defined. Here, it is initialized as an empty string.

Code: Select all

$enroll = '';
while ($row = mysql_fetch_array($query)) {
    $enroll .= $row['enroll_email']."\n";
}
Follow your code line-by-line. If you don't completely understand what a function does, read the manual page for that function.
marnieg wrote:Since I'm fetching multiple rows of data and placing them in the variable $enroll do I need to do an implode?
No. You have already imploded them with \n in the while loop. implode() operates on arrays. $enroll is a string.

See also: fputcsv()

Re: Opening CSV file

Posted: Mon Jun 21, 2010 11:05 am
by marnieg
fixed the unexpected end error, but now get in my file "invalid arguments passed for the implode function". Am I going down the wrong path with this implode thing. Just thought I needed to use it since I'm loading my data into an array. Maybe I'm loading the array wrong in the $enroll variable?

Here's my latest code.

$file = 'export';
$enroll = "";
while($row = mysql_fetch_array($query))
{
$enroll .= $row['enroll_email'];
}
$filename = $file."_".date("Y-m-d_H-i",time()).".csv";
header('Content-type: text/csv');
header('Content-disposition: attachment; filename="'.$filename.'"');
file_put_contents($filename,implode('',$enroll));

Re: Opening CSV file

Posted: Mon Jun 21, 2010 11:14 am
by marnieg
finally got it working and I didn't even need the "file_put_contents" function just used echo

$file = 'export';
$enroll = '';
while($row = mysql_fetch_array($query))
{
$enroll .= $row['enroll_email']."\n";
}
$filename = $file."_".date("Y-m-d_H-i",time()).".csv";
header('Content-type: text/csv');
header('Content-disposition: attachment; filename="'.$filename.'"');
echo "$enroll";