Unrecognized keyword in phpmyadmin

Question

I am using phpmyadmin version 5.6.33. Prepared a query to download a table by referring

http://www.mysqltutorial.org/mysql-export-table-to-csv/

(SELECT 'Order Number','Order Date','Status')
UNION 
(SELECT orderNumber,orderDate, status
FROM orders
INTO OUTFILE 'C:/tmp/orders.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n');

I getting error as

Unrecognized Keyword FIELDS
Unrecognized Keyword ENCLOSED and so on...

Reference certain links

phpMyAdmin SQL Error Unrecognized keyword near FIELDS

Unrecognized key word Near Fields, Terminated When Using INTO OUTFILE in mysql query

But it doesn't work for me.

(SELECT 'Order Number','Order Date','Status') UNION (SELECT orderNumber,orderDate, status) FROM orders INTO OUTFILE 'C:/tmp/orders.csv' FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'; — Alive to die - Anant, Dec 10 '16 at 07:37
5.6.33 might be your MySQL version, but isn't a valid phpMyAdmin version; could you look on the main page and let us know what phpMyAdmin version you have? — Isaac Bennetch, Dec 19 '16 at 23:00
I done a mistake that i execute this query in sql query execution portion instead of creating Stored procedure. Once i create stored procedure with same code it works fine.. — Deepak, Dec 21 '16 at 09:55

Shadow · Answer 1 · 2016-12-10T08:05:09.027

0

You need to place the union as a subquery into the from clause of the select into outfile, since you want to export the entire redultset:

SELECT * 
INTO OUTFILE 'C:/tmp/orders.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
FROM
    ((SELECT 'Order Number','Order Date','Status')
      UNION 
     (SELECT orderNumber,orderDate, status
      FROM orders));

edited Dec 10 '16 at 08:05

answered Dec 10 '16 at 07:56

Shadow

33,525
10
51
64

I tryed your solution still getting same errors. And also without union just tryed simple without field enclosed, it export correctly. – Deepak Dec 10 '16 at 08:02
Oops, placed the from clause at a wrong place. Updated the answer. – Shadow Dec 10 '16 at 08:05

TimBrownlaw · Answer 2 · 2016-12-10T08:32:30.620

0

Just expanding on Shadows answer, I found the following would work in phpmyadmin's SQL editor if...

You alias your subquery - cause it complained about that.

#1248 - Every derived table must have its own alias

The location of the file needs to be as defined in your my.ini file as defined below

To avoid warning messages secure_file_priv="c:/wamp64/tmp"

Note: You need to alter this in your query to suit what you are using.

Else It reports:

#1290 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

SELECT * 
INTO OUTFILE 'C:/wamp64/tmp/orders.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
FROM
    ((SELECT 'Order Number','Order Date','Status')
      UNION 
     (SELECT orderNumber,orderDate, status
      FROM orders)) as orders_alias;

And that generated the csv file. So see if that helps.

edited Dec 10 '16 at 08:32

answered Dec 10 '16 at 08:26

TimBrownlaw

5,457
3
24
28

(SELECT 'Order Number','Order Date','Status') UNION (SELECT orderNumber,orderDate, status FROM orders INTO OUTFILE 'C:/tmp/orders.csv'); This query export the table. But if i use FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n' It shows error as unrecognized keyword. – Deepak Dec 10 '16 at 10:22
And what does the very last error state where it shows the "mysql said" message? – TimBrownlaw Dec 10 '16 at 12:08
Do you see either the #1248 or #1290 or some other error and message? – TimBrownlaw Dec 10 '16 at 13:48
It doesn't shows any error code. It shows error message as Unrecognized Keyword (near FIELDS), Unrecognized Keyword (near ENCLOSED).. like that, if i use those lines – Deepak Dec 12 '16 at 04:37

Unrecognized keyword in phpmyadmin

2 Answers2

To avoid warning messages secure_file_priv="c:/wamp64/tmp"