mysql to csv

[defanjos]

I have the following code that works:

$sql_res=mysql_query("SELECT * FROM xyz ");


$list = array();
while ($row = mysql_fetch_array($sql_res)) {
 $list[] = array(
 'column_1' => $row['1'],
 'column_2' => $row['2'],
 'column_3' => $row['3'],
 'column_4' => $row['4']
 );

}


$fp = fopen('file.csv', 'w'); 
foreach ($list as $fields) {
 fputcsv($fp, $fields);
}

fclose($fp);

It creates a csv file with the values of $row['1'], $row['2'], etc..
How can I have the column names at the top of the csv?

right now it writes something like:

a, b, c, d
aa, bb, cc, dd
aaa, bbb, ccc, ddd

how can I have?:

column_1, column_2, column_3, column_4
a, b, c, d
aa, bb, cc, dd
aaa, bbb, ccc, ddd

Thanks in advance

[phranque]

try $row['0']

[Readie]

This should do you:

$sql_res = mysql_query('select * from example');
$fp = fopen('/path/to/file.csv', 'w');

$first = true;
while($row = mysql_fetch_assoc($sql_res)) {
  if($first === true) {
    $first = false;
    fputcsv($fp, array_keys($row));
  }
  fputcsv($fp, $row);
}

fclose($fp);

Changed fetch_array to fetch_assoc to get rid of all the numeric keys from the returned row, leaving us with just the contextual keys from the database.

Then an array_keys() on the first iteration of the while loop puts those contextual keys as the header row.

The whole double-loop that you had is unnecessary and could actually lead to out-of-memory issues depending on the size of your table. If you want to limit your CSV to specific columns, do it in the query:

'select a, b, c from example'

[defanjos]

That works great, but with the column names written the way they are in the db.

Had to use "select db1 as column_1, db2 as column_2, etc.." to make it work as desired.

Thanks