Posted by Richard Lynch on 02/11/05 19:17

> i have a script that exports the result of a mysql query to a csv file
> suitable for downloading
>
> the code looks like
> $result = mysql_query("select * from user,mjp1,mjp2,mjp3,mjp4");
> while($row = mysql_fetch_array($result)) {
> $csv_output .= "$row[userid] $row[firstname] $row[lastname]\n" }
>
> header("Content-type: application/vnd.ms-excel");
> header("Content-disposition: csv" . date("Y-m-d") . ".xls"); print
> $csv_output;
>
> this works fine, except that when i expand the line $csv_output
> .="$row[userid] $row[firstname] $row[lastname] $row[anotherfield]
> $row[anotherfield] ...\n"} -to include an increasing number of fields, it
> tends to bog down the speed at which the $csv_output file can be printed.
> when i have this line output 30+ fields for each row, wait time for output
> file generation is almost 4-5 minutes.
>
> is this the most efficient way to do this? i'd like to be able to
> generate
> the file as quickly as possible.

Since you don't really care about the individual fields, and you are just
dumping them all out anyway, this might be faster:

$cvs_output .= implode(',', $row);

And, actually, there's no real reason to build up this HUGE string in PHP.

Just spew as you go:

$result = mysql_query("select * from user,mjp1,mjp2,mjp3,mjp4");

header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".xls");

while($row = mysql_fetch_array($result)) {
echo implode(',', $row);
}

Note that if you ever embed a comma in your data, then you will be
generating invalid CSV...

--
Like Music?
http://l-i-e.com/artists.htm

[Back to original message]