exporting data to csv

webosb · 01-15-2008, 09:08 PM

currently i have a script that exports to csv file from mysql but it seems as there are more rows of data, the script kind of stops working...

current i'm using this function:

PHP Code:


			
function generatecsv($start, $end){

$delim = ",";

$query = "SELECT * FROM users WHERE userid >= '$start' AND userid <= '$end'";



$d = db();

$result = mysql_query($query, $d);

mysql_close($d);





$csv_output = "userid,email,firstname,lastname";

$csv_output .= "\n";



while($row = mysql_fetch_array($result)) { 

$uid = $row['userid'];

$em = $row['username'];

$fn = $row['firstname'];

$ln = $row['lastname'];



$csv_output .= $uid . $delim;

$csv_output .= $em . $delim;

$csv_output .= $fn . $delim;

$csv_output .= $ln . $delim;

$csv_output .= "\n";

}





header("Content-Type: application/force-download\n");

header("Cache-Control: cache, must-revalidate");

header("Pragma: public");

header("Content-Disposition: attachment; filename=data_" . date("dmY") . ".csv");



print $csv_output;

exit;

}

is there a better way of doing this when there's like 50,000 rows of data?

RobertK · 01-15-2008, 09:11 PM

Firstly, there's a time limit on execution. Maybe one of the others remember what to set to a long time period.

Also, you should do an unbuffered query, which will speed things up. This way you just grab results until a null/false is returned and echo out the row. It'll be a good bit faster with 50k rows.

Kalle · 01-15-2008, 09:15 PM

If you're using PHP 5.1+, then you might find the function fputcsv() useful for generating csv files =)