Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MYSQL Select to file

Tags:

php

mysql

I'd like to save the output from a "Select * FROM table1" in console to a file in PHP.

What would be the most efficient way to do this in php?

Edit: I'd like the output to be like the type in the console ie.

+--------+--------+
| thing1 | thing2 |
+--------+--------+
| item1a | item2a |
| item1b | item2b |
+--------+--------+

Also, I was thinking that the php code should be "exec(mysql command which I'm looking for)". It's my understanding that mysql_fetch_array is slow, and I'm looking for a simple output...so it should be possible to somehow do this from the console. (Thanks for the responses thus far!)

like image 343
Eugene Avatar asked Oct 31 '10 00:10

Eugene


People also ask

How do I save SQL query output to a file?

Click Query, and then click Results to File. Enter and then execute the SQL statement. In the Save Results dialog box, specify the following settings: Save In: Select a directory in which to save the file.

How do you select into outfile?

You can also use SELECT ... INTO OUTFILE with a VALUES statement to write values directly into a file. An example is shown here: SELECT * FROM (VALUES ROW(1,2,3),ROW(4,5,6),ROW(7,8,9)) AS t INTO OUTFILE '/tmp/select-values.

How do I store the MySQL query results in a local csv file?

use the command insert...select to create a "result" table. The ideal scenario whould be to automatically create the fields of this result table, but this is not possible in mysql. create an ODBC connection to the database. use access or excel to extract the data and then save or process in the way you want.


2 Answers

You could use MySQL's INTO OUTFILE syntax - this would produce a comma separated value (CSV) text file:

SELECT *
  INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
  FROM YOUR_TABLE;

Permissions to write files to the location specified need to be in place.

like image 126
OMG Ponies Avatar answered Oct 31 '22 20:10

OMG Ponies


In what format? If you want them tab separated, you can use something like this:

$r   = mysql_query("SELECT * FROM table1");
$str = '';

while($row = mysql_fetch_assoc($r)) {
    $str .= implode("\t", $row)."\n";
}

file_put_contents('table1.txt', $str);

Or, in CSV:

$r  = mysql_query("SELECT * FROM table1");    
$fp = fopen('table1.csv', 'w');

while($row = mysql_fetch_assoc($r)) {
    fputcsv($fp, $row);
}

fclose($fp);

Or, as others noted, use MySQL's own OUTFILE which I was unaware of. :)

like image 44
Tatu Ulmanen Avatar answered Oct 31 '22 19:10

Tatu Ulmanen