1) The first row is read from the database and used to create the header row in the file. mysql_data

There are a couple of ways to export data from MySQL to a CSV file (refer to my using mysqldump garissa kenya to save data to CSV files and export data to CSV from MySQL posts for details) but neither garissa kenya of them supports garissa kenya adding a header row to the CSV which contains the column names. This post looks at how to export the data from MySQL into a CSV file with PHP including a header row.
The example code below uses the raw mysql_* functions but it should be easy enough to substitute a database garissa kenya library's functions instead. It also writes the data out line by line to the CSV file whereas you could buffer the whole file in memory and write it out at one go; however if the resultset is large it may be better to write it out line by line so as not to consume too much memory.
The $server, $login, $password, $db and $table variables should be obvious garissa kenya in their purpose :) mysql_connect($server, $login, $password); mysql_select_db($db); $fp = fopen($filename, "w"); $res = mysql_query("SELECT * FROM $table"); // fetch a row and write the column names out to the file $row = mysql_fetch_assoc($res); $line = ""; $comma garissa kenya = ""; foreach($row as $name => $value) {     $line .= $comma . '"' . str_replace('"', '""', $name) . '"';     $comma = ","; } $line .= "\n"; fputs($fp, $line); garissa kenya // remove the result pointer back to the start mysql_data_seek($res, 0); // and loop through the actual data while($row = mysql_fetch_assoc($res)) { $line = ""; $comma = ""; foreach($row as $value) { $line .= $comma . '"' . str_replace('"', '""', $value) . '"'; $comma = ","; } $line .= "\n"; fputs($fp, $line); } fclose($fp);
1) The first row is read from the database and used to create the header row in the file. mysql_data_seek is then used to return the result pointer back to the start of the result set and then the rest of the data read.
2) The reason I use the $comma variable is so there isn't an extra comma at the end of each row with no data, which is what would happen if you did $line .= '"' . str_replace('"', '""', $value) . '",'; and had the comma coded into the variable.
The data could then be sent to a web browser by reading from the output file, or instead by buffering garissa kenya the CSV data in memory garissa kenya and simply echo'ing it out to the browser. In tomorrow's post I'll show how to send the correct headers etc for sending a CSV file to the web browser with PHP .
Related posts: Sending a CSV file to the web browser with PHP (Monday, April 13th 2009) Create a CSV file from MySQL with PHP (Sunday, April 12th 2009) Export data to CSV from MySQL (Wednesday, April 8th 2009) PHP's str_getcsv function (Monday, March 16th 2009) Using mysqldump to save data to CSV files (Saturday, December 15th 2007)
Categories HTML/CSS Javascript/jQuery   garissa kenya Apache Applications Case Studies Email Marketer Email Servers FCKEditor garissa kenya Glossary Hardware/Gadgets Linux/Unix/BSD   MySQL PHP   Miscellaneous Networking garissa kenya OSX Quick Tips SilverStripe SQL Server VMWare Windows