Page 1 of 1

write query result in csv file

Posted: Wed Jul 21, 2004 5:21 am
by juline
hi all,
does anybody have an idea how i can write an query result into a .csv file ?

cheers
juine

Posted: Wed Jul 21, 2004 5:33 am
by JayBird
To downlaod results as CSV, i do this

export_csv.php

Code: Select all

Header ("Content-type: text/csv"); 

//This is my code, change your queries and formatting

$query = "SELECT * FROM contacts ORDER BY company";

$result = mysql_query($query) or die(mysql_error());

while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
	echo "".$lineї'id'].",".$lineї'company'].",".$lineї'name'].",".$lineї'position'].",".$lineї'address'].",".$lineї'notes'].",".$lineї'tel'].",".$lineї'direct_line'].",".$lineї'fax'].",".$lineї'mobile'].",".$lineї'email'].",".$lineї'web']."\n";
}

?>
Now here is the trick, when linking to the reults, you link will look like this

http://192.168.1.2/ocdaintranet/contact ... export.csv

notice after export_csv.php i have added export.csv, this fool the broswer into downloading a CSV and not the PHP file

Mark


feyd | flipped to

Code: Select all

tags on 2005.02.25[/color]

Posted: Wed Jul 21, 2004 5:33 am
by qads
what kind of query?

writeing a csv file is same as writeing anyother file, you just have to separate the fields with a comma.

i.e

Code: Select all

<?php
$query = mysql_query("select username,email from users where ID > 10");
while($row = mysql_fetch_array($query))
&#123;
$data .= "$username,$email\n";
&#125;
$filename = "somefile.csv";
$handle = fopen($filename, "a");//will add to end of the file..
chmod($filename, 777);
fwrite($handle, $data);
fclose($filename);
?>
few things you may wanna make sure...
- chmod the folder to 777 which will hold the csv file.
- replace any commas in the fields, so they dont mess up the csv..

if you just need to download the file right away, without creating the file then you just need some headers to force download it, i.e.

Code: Select all

<?php
$query = mysql_query("select username,email from users where ID > 10");
while($row = mysql_fetch_array($query))
&#123;
$data .= "$username,$email\n";
&#125;
$filename = "somefile.csv";
header ('Content-type: text/csv'); 
header ('Content-Disposition: attachment; filename='.$filename); 
echo $data;
?>

feyd | flipped to

Code: Select all

tags on 2005.02.25[/color]

Posted: Wed Jul 21, 2004 5:34 am
by qads
lol, at the same time mark :P

Posted: Wed Jul 21, 2004 5:34 am
by feyd
look into SELECT Syntax with OUTFILE.