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!)
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.
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.
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.
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.
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. :)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With