0

MySQL: I want to write a timestamp stored in a variable to a CSV file and I only know how to store a query.

Diogo Crava
  • 21
  • 5
  • Can you put the variable in a SELECT statement and then write that to the csv the way you know how to? – JNevill Mar 28 '19 at 21:05
  • Im kinda new to MySQL so Im not sure how to do that. Would that be something like "Select VARIABLE Into File 'hello.csv' "? Not sure if I can ignore the FROM keyword. – Diogo Crava Mar 28 '19 at 21:08
  • 1
    You can totally do a `SELECT` without a `FROM`. [Check out an example here](http://sqlfiddle.com/#!9/433061/5). – JNevill Mar 28 '19 at 21:12
  • Thank you! Thats exactly the example I needed. I couldnt find any. – Diogo Crava Mar 28 '19 at 21:27
  • *"Not sure if I can ignore the FROM keyword."* Well it's indeed not allowed by SQL standards, so tableless are not portable.. The SQL standard defines using `SELECT t.column1 , t.column2 FROM ( VALUES(1, 'one') ) AS t (column1, column2)` instead off `SELECT 1 AS column1, 'one' AS column2` for example.. Only problem not all databases support the `VALUES()` for tableless selects also some database implement it with a dummy table like Oracle database `DUAL` table, which are works in MySQL and MariaDB. – Raymond Nijland Mar 28 '19 at 21:36
  • .. If you want a cross database vender query you are looking for [this](https://dbfiddle.uk/?rdbms=db2_11.1&fiddle=c64e84390a22728d0019cd8e7c3ef098) .. Fews problems is your dummy table much have enough records to match your "tableless" select.. Also this method will cost (some) disk space.. And most likely will do a full table scan on the dummy table which will cost disk I/O time when quering – Raymond Nijland Mar 28 '19 at 21:42
  • @JNevill [little trick](http://sqlfiddle.com/#!9/340e01/1) for next time.. Sqlfiddle doesn't require a schema it requires a valid query using `select 1` (as "schema") will work just fine. – Raymond Nijland Mar 28 '19 at 21:47
  • Possible duplicate of [How to output MySQL query results in CSV format?](https://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format) – Jason Mar 29 '19 at 00:04
  • @Raymond Nijland Thank you too! – Diogo Crava Mar 29 '19 at 05:08
  • @CR241 I literally explained in a single quote that I was specifically asking for a variable and not a query. – Diogo Crava Mar 29 '19 at 05:08

1 Answers1

0

Here's how I do it (using my own SQL() function to retrieve the result set via fetch_all(MYSQLI_ASSOC), but you're clearly ok on that bit):

$out = fopen($file, 'w');
$rows = SQL($sqlstatement);
fputcsv($out, array_keys($rows[0]));
foreach($rows as $row) { fputcsv($out, $row); }
fclose($out);

The key is the built-in PHP fputcsv function. The first use of fputcsv writes the column headings (what a fantastic language PHP is...), the others (in the loop) write the rows.

MandyShaw
  • 1,088
  • 3
  • 14
  • 22