[SOLVED] writing CSV file

[abdfahim]

Is there any built in function to output a SQL query directly to a CSV/tab delimited file without writing line by line?

[jaymc]

I seriously doubt it, but, others will have created classes that will do this, maybe use them?

[Xurion]

Use this.

 

<?php
//head information for a csv file
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private",false);
Header("content-type: text/html");
header("Content-Disposition: attachment; filename=\"website-data.csv\";");
header("Content-Transfer-Encoding: binary");

//echo the first line to be the titles of the columns (notice the return at the end of the line)
echo '"product ID","ref","title","description","price"
';

//query the db for the information
$result = mysql_query("SELECT * FROM products");

//echo each record (notice the return at the end of the line)
while ($row = mysql_fetch_array($result)){
echo '"'.$row[product_id].'","'.$row[ref].'","'.$row[title].'","'.$row[description].'","'.$row[price].'"
';
}
?>

[kenrbnsn]

Why go through the effort of writing out headers, you can just as easily do something like this:

<?php
$fh = fopen('test.csv','w');
$flds = array("product ID","ref","title","description","price");
fputcsv($fh,$flds);
$q = "select product_id,ref,title,description from yourtable";
$rs = mysql_query($q);
while ($rw = mysql_fetch_assoc($rs))
    fputcsv($fh,$row);
fclose($fh);
?>

 

See the fine manual about fputcsv()

 

Ken

[Xurion]

My version gives you the csv to download. Your one stores it on the server.

[abdfahim]

thanx man ... I normally used the Xurion's process .... but enjoy the function fputcsv()... greatz !!!

 

by the way, for Xurion, you can change php.ini setting so that you can also download *.csv files in kenrbnsn's process.

[mlin]

binary content transfer encoding?

 

why is this? it's a text file. I would think you'd get the desired results omitting that header...am I wrong?

[abdfahim]

if you omit those header portion in normal php installation (without changing the php.ini file), it will open the text file in your browser rather than prompting for download/open.

[nashsaint]

Hi,

 

I used Xurion's method and it works great.  I have one question though, I want the .csv file to be preformatted to specific cell width. how can I do that using php code?  thanks.

[Xurion]

I assume you are referring to a spreadsheet software like Excel? The problem is that a .csv file contains data only, and it cannot store instructions for Excel (or which ever one you use) for cell widths.

 

Only way I can see you doing this is to open the .csv file in your software, adjusting the widths, and saving the file in a different format.