Welcome to WebmasterWorld Guest from 54.221.30.139

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

mysql to csv

     

defanjos

5:00 am on Jan 25, 2014 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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

8:17 am on Jan 25, 2014 (gmt 0)

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



try $row['0']

Readie

11:15 am on Jan 25, 2014 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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

3:45 pm on Jan 25, 2014 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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
 

Featured Threads

Hot Threads This Week

Hot Threads This Month