Re: Ho to write output of sql query to txt file?

Original question: > [snip] > I am trying to get only records out of a table mathing a query. This should > be piped into a textfile. My problem is that I have to transmit this records > to a new machine. So the output should be a kind of sql commands which I > could run with phpadmin or similar to import them to the other db on the > other machine. > > Is this possible? > [/snip] Thank you for your help. I did read on mysql.com and your article. Than I tryed to transfer the datasets. Export worked ok. The textfile contains the records. Import message: Query OK, 35 rows affected (0.04 sec) Records: 35 Deleted: 0 Skipped: 0 Warnings: 630 The warning already tells it. the datasets are totally empty. Only default of the columns is set. do u have an idea why? Thanx, Andy ----- Original Message ----- From: "Jay Blanchard" <jay.blanchard@niicommunications.com> To: "'andy'" <news.letters@gmx.de>; <php-db@lists.php.net>; <php-general@lists.php.net> Sent: Tuesday, June 04, 2002 4:07 PM Subject: RE: [PHP] Ho to write output of sql query to txt file? > [snip] > I am trying to get only records out of a table mathing a query. This should > be piped into a textfile. My problem is that I have to transmit this records > to a new machine. So the output should be a kind of sql commands which I > could run with phpadmin or similar to import them to the other db on the > other machine. > > Is this possible? > [/snip] > > from: http://www.mysql.com/doc/S/E/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 > >