MySQL to CSV

jackie_patil

I am interested in exporting data from a MySQL database to my computer in CSV file format.

Does anyone have ideas on how to do this?

bogu

PhpMyAdmin can export database as csv ....

jackie_patil

But I want that feature through coding

bogu

jackie_patil wrote:
But I want that feature through coding

So, what's stoping u?

jackie_patil

But I want some standard class or code which is availble for use.

pete_bisby

Have a look at the fputcsv function, as long as you're using PHP 5.1.0 and above.

http://uk2.php.net/manual/en/function.fputcsv.php

jackie_patil

Sorry frnd, I am using php4.4.1 version.

Weedpacket

Have a look at how PHPMyAdmin is doing it -- it's written in PHP, after all (hence the name).

Or search the web for something. I'm sure there's something out there.

pete_bisby

How about using the SELECT .... INTO OUTFILE within MySQL. Use the following link:

http://dev.mysql.com/doc/refman/4.1/en/select.html

It utilises additional keywords: FIELDS TERMINATED BY, ENCLOSED BY, LINES TERMINATED BY, etc. You should be able to use this to export in CSV format.

jackie_patil

I have use that query

$qry= " SELECT * INTO OUTFILE 'result.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table ";
but it gives

Parse error: parse error, unexpected '\''

due to special characters like ' \n' , ',' , ' " '

bogu

jackie_patil wrote:
I have use that query

$qry= " SELECT * INTO OUTFILE 'result.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table ";
but it gives

Parse error: parse error, unexpected '\''

due to special characters like ' \n' , ',' , ' " '

U put your query string between double quotes, and all your double quotes from within the query need to be deviated with backslash ( \ ), look at the query ...

$qry= " SELECT * INTO OUTFILE 'result.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' FROM test_table ";

jackie_patil

Thanks.the query is executed but it showing new error

Error in queryCan't create/write to file '/csv/result1.txt' (Errcode: 2)

Do you know how to fix it?

kakki

chmod that dir to 777

Weedpacket

It may be possible to specify where the file should be saved and under what name (perhaps by using something other than 'result.txt' as the OUTFILE parameter) - check the MySQL documentation for that.

jackie_patil

I have set chmod of that dir to 777 but Still it's giving problem.Give me other solution

Weedpacket

leatherback

else, if you really give up because of a parse error..

"select * from table"
=>
while $row=mysql_fetch_array($result)
{
$line = implode($row, ",");
$fileout .= "\"".$line."\"\n\r";
}

and write that to file.

kakki

maybe there is already a file '/csv/result1.txt' with different rights, delete that
( maybe you upload that file )