RE: [PHP] MySQL to csv

[Brian V Bonini]

Using SQL or PHP?

> -Original Message-
> From: Djurovski Dejan [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, August 15, 2002 11:42 AM
> To: [EMAIL PROTECTED]
> Subject: [PHP] MySQL to csv
> 
> 
> Hi
> 
> Can someone show me how to export MySQL table to a csv file?
> 
> Thanks!
> 
> 
> 
> -- 
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 
> 

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] MySQL to csv

[Justin French]

Assuming that you KNOW the structure of the table, this should do it... if
you needed something more open-ended to cope with ANY table, I'd have to
think about it a little more:



This is all off the top of my head, untested, with snippets lifted from the
manual, but it should give u the theory, for which to build your own.

If your target OS's are likely to include Macs, you'll have to do some tests
on the line ending... \n or \r or perhaps \n\r will work best, with some
testing.


Justin French


Justin French



on 16/08/02 1:41 AM, Djurovski Dejan ([EMAIL PROTECTED]) wrote:

> Hi
> 
> Can someone show me how to export MySQL table to a csv file?
> 
> Thanks!
> 
> 


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP] MySQL to csv

[Jay Blanchard]

[snip]
Can someone show me how to export MySQL table to a csv file?
[/snip]

>From http://www.mysql.com/doc/en/SELECT.html

The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected
rows to a file. The file is created on the server host and cannot already
exist (among other things, this prevents database tables and files such as
`/etc/passwd' from being destroyed). You must have the FILE privilege on the
server host to use this form of SELECT. SELECT ... INTO OUTFILE is mainly
intended to let you very quickly dump a table on the server machine. If you
want to create the resulting file on some other host than the server host
you can't use SELECT ... INTO OUTFILE. In this case you should instead use
some client program like mysqldump --tab or mysql -e "SELECT ..." > outfile
to generate the file. SELECT ... INTO OUTFILE is the complement of LOAD DATA
INFILE; the syntax for the export_options part of the statement consists of
the same FIELDS and LINES clauses that are used with the LOAD DATA INFILE
statement. See section 6.4.9 LOAD DATA INFILE Syntax. In the resulting text
file, only the following characters are escaped by the ESCAPED BY character:
The ESCAPED BY character
The first character in FIELDS TERMINATED BY
The first character in LINES TERMINATED BY
Additionally, ASCII 0 is converted to ESCAPED BY followed by 0 (ASCII 48).
The reason for the above is that you must escape any FIELDS TERMINATED BY,
ESCAPED BY, or LINES TERMINATED BY characters to reliably be able to read
the file back. ASCII 0 is escaped to make it easier to view with some
pagers. As the resulting file doesn't have to conform to the SQL syntax,
nothing else need be escaped. Here follows an example of getting a file in
the format used by many old programs.
SELECT a,b,a+b INTO OUTFILE "/tmp/result.text"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n"
FROM test_table;

HTH!

Jay

I’m talking to myself…please don’t eavesdrop!

***
* Texas PHP Developers Conf  Spring 2003  *
* T Bar M Resort & Conference Center  *
* New Braunfels, Texas*
* San Antonio Area PHP Developers Group   *
* Interested? Contact [EMAIL PROTECTED] *
***



-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php