MYSQL Select to file

Question

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!)

Answer 1

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. :)

Answer 2

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.