SQL to PHP to CSV most efficient method

Question

I just want to double check the most efficient method of writing to a CSV file with PHP using data retrieved from a database.

Currently after executing my sql query to retrieve the data I am using a while loop to assign the data to varaibles, then writing to the file within that while loop. Like so

$fp = fopen("../" . $this->portal . "/" . $this->folder . "/" . $this->fileName . ".csv", 'a+');
$data = array();

while ($row = sqlsrv_fetch_array($getList,  SQLSRV_FETCH_ASSOC)) { 
     $data['id'] = $row['id'];
     $data['empId'] = $row['empId'];
     $data['fullname'] = $row['fullname'];
     $data['title'] = $row['title'];
     $data['department'] = $row['department'];

    fputcsv($fp, array_values($data));
}       

fclose($fp);

I'm wondering if it would be quicker to assign the data from each iteration to a string variable in csv format, then concatenate that throughout the loop. Then once the loop is completed, write that variable to a file? Would that be quicker in any way? Or is there another way entirely to write to a csv file which is more efficient?

What you're doing with that `$data` array is completely superfluous. `fputcsv($fp, $row)` will probably do the same thing, or at least `fputcsv($fp, array($row['id'], ...))` if you need to reorder the columns. And performance wise this should already be fine as far as PHP involvement goes. — deceze, Jun 03 '14 at 10:49

vogomatix · Answer 1 · 2014-06-03T10:55:04.857

2

From your example, it seems the PHP is not doing anything with the data, so why not avoid almost all PHP interaction? Most databases have methods to avoid PHP altogether and write a CSV file directly

e.g. MySQL SELECT ... INTO

From the example:

SELECT a,b,a+b INTO OUTFILE '/tmp/result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

edited Jun 03 '14 at 10:55

answered Jun 03 '14 at 10:49

vogomatix

4,856
2
23
46

never thought about this - nice solution. works best, if the database has access to the same disk space as process that needs to follow up on the csv file – Quicker Jun 03 '14 at 11:00

score -1 · Answer 2 · answered Jun 03 '14 at 10:58

if you can afford having the whole file in memory I would do it like this:

$outarr = array();
while ($row = sqlsrv_fetch_array($getList,  SQLSRV_FETCH_NUMERIC)) { 
   array_push($outarr,implode(";",$row));
}
file_put_contents("../" . $this->portal . "/" . $this->folder . "/" . $this->fileName . ".csv", implode($outarr,"\n"));

There are some trade-offs:

your sql statement should only return columns which are needed in the csv
your cell delimiter is fixed => ;
your line delimter is ixed => line-break (UNIX coding)
proper csv coding would wrap values that include the cell or line delimter into quotes -> your field names led to the assumption, that this is not needed in your case

SQL to PHP to CSV most efficient method

2 Answers2