[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
RE: [PHP] MySQL to csv
[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 Im talking to myself please dont 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
Re: [PHP] MySQL to csv
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:
? // untested
// get each row of the table into a a var,
// separated by commas, ended with \n
$csv_contents = '';
$sql = SELECT id,name,pass FROM tablename;
$result = mysql_query($sql);
while($myrow = mysql_fetch_array($result))
{
// you have a row
$csv_contents .= {$myrow['id']},{$myrow['name']},{$myrow['pass']}\n;
}
function write_to_file($filename, $stringtowrite, $writetype)
{
// lifted from the manual
$filesession = fopen($filename,$writetype);
fwrite($filesession,$stringtowrite);
fclose($filesession);
}
write_to_file('nameofmyfile.csv',$csv_contents, 'w');
?
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
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
[PHP] mysql to .csv file
Hi Folks, I m trying to export data from mysql using 'SELECT * INTO OUTFILE...' query into a '.csv' file. It does happen successfully but I don't get the field values (having commas or enter characters)enclosed in double/single quotes. Is there a way out to accomplish this? If yes, please help. Thanx. Reagds, Amit _ Click below to visit monsterindia.com and review jobs in India or Abroad http://monsterindia.rediff.com/jobs -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
