How to convert MYSQL data result to csv file format.

hi all,

How to convert MYSQL data result to csv file format.



Thanks,
Anu

Re: How to convert MYSQL data result to csv file format.

anu wrote:
>
> 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.
jstucklex@attglobal.net
==================

Re: How to convert MYSQL data result to csv file format.

On Dec 28, 2:15 am, anu wrote:
>
> How to convert MYSQL data result to csv file format.

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

Re: How to convert MYSQL data result to csv file format.

On Fri, 28 Dec 2007 09:41:11 -0800 (PST), NC wrote:

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


>On Dec 28, 2:15 am, anu wrote:
>>
>> How to convert MYSQL data result to csv file format.
>
>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

Re: How to convert MYSQL data result to csv file format.

NN schreef:
> On Fri, 28 Dec 2007 09:41:11 -0800 (PST), NC wrote:
>
> 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
>
create a new php witch includes the csv
and start the headers of the file with

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
>
>> On Dec 28, 2:15 am, anu wrote:
>>> How to convert MYSQL data result to csv file format.
>> 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

Re: How to convert MYSQL data result to csv file format.

thank you very much!
i'll try that.

NN

Re: How to convert MYSQL data result to csv file format.

On Dec 29, 7:22 am, NN wrote:
>
> 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?

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

Re: How to convert MYSQL data result to csv file format.

thank you!. i'll try it and see if it works on my server.

thanks,
NN

>On Dec 29, 7:22 am, NN wrote:
>>
>> 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?
>
>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