write query result in csv file

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
juline
Forum Commoner
Posts: 37
Joined: Thu Jul 15, 2004 9:05 am

write query result in csv file

Post by juline »

hi all,
does anybody have an idea how i can write an query result into a .csv file ?

cheers
juine
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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]
qads
DevNet Resident
Posts: 1199
Joined: Tue Apr 23, 2002 10:02 am
Location: Brisbane

Post 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]
Last edited by qads on Wed Jul 21, 2004 5:35 am, edited 1 time in total.
qads
DevNet Resident
Posts: 1199
Joined: Tue Apr 23, 2002 10:02 am
Location: Brisbane

Post by qads »

lol, at the same time mark :P
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

look into SELECT Syntax with OUTFILE.
Post Reply