hi all,
How to convert MYSQL data result to csv file format.
Thanks,
Anu
If you're looking for a way to do it in SQL, try comp.databases.mysql.
If you're looking for a way to do it in PHP, what have you tried?
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================
The high-performance solution is to run a SELECT INTO OUTFILE query:
http://dev.mysql.com/doc/refman/4.1/en/select.html
The drawback of this approach is that the CSV file can only be created
on the machine where MySQL server is running, which may or may not be
feasible in your particular case.
The alternative is to simply dump the data into a file:
// Assume that the DB connection has already been established...
$result = mysql_query('SELECT * FROM myTable');
$fp = fopen('data.csv', 'w');
if ($fp == false) {
die("Could not open data.csv for writing");
}
while ($record = mysql_fetch_row($result)) {
fputcsv($fp, $record);
}
fclose($fp);
Note that fputcsv() is available only since PHP 5.1, so you may have
to write your own CSV formatter if you have an earlier version of
PHP...
Cheers,
NC
i tried the script and it works great!
i have a question. i added a link to download the file created with
the url of the file. when i run it on my wamp server i click on the
link and it prompts me to save the file, when i run it on my web
server the csv opens in the browser instead of prompting to save the
file.
any idea of how to solve this?
does it have to do with the php.ini settings?
thank you,
NN
<?php
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: attachment; filename="filename.csv");
header("Cache-Control: cache, must-revalidate");
header("Pragma: public");
include("filename.csv");
?>
and run that file instead of the csv
you could also remove the include and build the csv from scratch there
header("bla bla")
echo
"\"fieldname1\";\"fieldname2\";\"fieldname3\";\"fieldname4\";\"etc\"\n";
database extraction
select * from etc
while($row = mysql_fetch_object($resultaat))
{
some if then filtering;
echo
"\"$row->content1\";\"$row->content2\";\"$row->content3\";\"$row->content4\";\"etc\"\n";
}
and if the file gets big dont forget to set
set_time_limit(0);
at the top of the file to reset the standard php ini settings
works great for me and i am able to create filters to give the csv i
want with the values i want
greets
pacal
NN
No, it has to do with HTTP headers Web servers send out before serving
actual data.
How do you solve this? By writing a script that would send the
appropriate headers and then dump the data. Let's assume that, in
line with the previous example, you have created a file called
data.csv. Now you can write a script that will serve this data and
suggest to the browser that the data is to be saved as data.csv rather
than viewed in the browser:
header('Content-type: application/vnd.ms-excel');
header('Content-disposition: attachment; filename=data.csv');
readfile('data.csv');
Note, however, the word "suggest" above; the browser may choose to
ignore the "Content-disposition:" header and still display data in
browser of save it under the script's name rather than as data.csv...
Cheers,
NC
thanks,
NN