Geek asks and answers

Order by SQL 1064 State Exception field

I keep getting

exception 'PDOException' with message 'SQLSTATE[42000]: 
Syntax error or access violation: 1064 
You have an error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near 
'(questionid, 'sfname' , 'slname' , 'school' , 'grade' , 'addr' , 'city' , 'state' at line 1'

from this statement:

$stmt = $db->prepare('SELECT * FROM event_responses WHERE eventid= :eventid ORDER BY userid DESC, field (questionid, \''.implode("' , '", $columns1).'\')');

I repeated the expression inside and it looks good to me:

SELECT *
FROM event_responses
WHERE eventid= :eventid
ORDER BY userid DESC,
      field (questionid, 'sfname' , 'slname' , 'school' , 'grade' , 'addr' , 'city' , 'state' , 'zip' , 'semail' , 'sphone' , 'pfname' , 'plname' , 'pemail' , 'pphone' , 'noattend' , 'regid' , 'submitDate' , 'attended' , 'regmethod')

Why is this happening?

+3
source share
1 answer

You are having a strange problem in MySQL:

Note

By default, there should be no spaces between the function name and parentheses follow it. This helps the MySQL parser distinguish between function calls and table or column references that happen to have the same name as the function. However, the spaces around the function arguments are allowed.

Remove the space after fieldso that the expression:

SELECT *
FROM event_responses
WHERE eventid = :eventid
ORDER BY userid DESC,
         field(questionid, 'sfname' , 'slname' , 'school' , 'grade' , 'addr' , 'city' , 'state' , 'zip' , 'semail' , 'sphone' , 'pfname' , 'plname' , 'pemail' , 'pphone' , 'noattend' , 'regid' , 'submitDate' , 'attended' , 'regmethod')
+11
source

More articles:


All Articles