homepage Welcome to WebmasterWorld Guest from 54.198.157.6
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
mysql to csv
defanjos




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

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




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

try $row['0']

Readie




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

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




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

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved