MySQL Syntax Error - unsure of issue

Question

I am trying to use the following syntax to update my database, but it is not working, and when I use Instant SQL Formatter, it says:

Syntax Error: =(1,76) expected token:(

Here is the MySQL code: (that is being run by PHP)

UPDATE parts SET name='How do you use it?', part_order='0', top='334', left='44', width='150', height='26', value='', script='on mouseup\nanswer \"To use jsCard, all you need is an account! From there you can create stacks, then cards, then you can start building on those cards. To build on cards, simply double click on an object in the tools palette, assign a name to your new object, then, if it is a button, add a script to make it do wonderful things!\"\nend mouseup', visible='1', enabled='1', style='5', family='0', locktext='0', hilite='0', autohililte='1' WHERE stacks_id=1 AND cards_id=1 AND part_id=19

Can anyone tell me what is wrong with my syntax?

@Dagon I thought so too http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html but in MySQL's docs shows it isn't, even though both `VALUES` and `VALUE` are acceptable to be used in a query, which I don't get. But the word in this case is `left` — Funk Forty Niner, Jul 01 '14 at 21:11
thanks @Fred-ii- as the smarter ones said it was `left`, at least i was in the hockey rink — , Jul 01 '14 at 21:13
I've never figured out the logic of which keywords are reserved. `LEFT` is reserved, but `COUNT` isn't. — Barmar, Jul 01 '14 at 21:14
@Dagon When looking at the OP's errors, I had a feeling a reserved word was used, but couldn't pinpoint it. But it's still strange that `value` can be used even though it can be use in a query; very bizarre. — Funk Forty Niner, Jul 01 '14 at 21:15
almost as bizarre as the php function naming conventions (and argument order conventions) — , Jul 01 '14 at 21:16
The "(1,76)" is hinting you at position (Byte) 76 in your statement, where left is. — hellcode, Jul 01 '14 at 21:16
Sometimes MySQl can use the context to determine whether a word is used as a keyword or an identifier. `VALUE` can't be used legitimately in a list so MySQL guesses it's an identifier. `LEFT` however, can be used sometimes so it's reected. It's not a perfect system. My vote would be for strict syntax and less 'help' — , Jul 01 '14 at 21:20

score 2 · Accepted Answer · answered Jul 01 '14 at 21:11

That's probably because your INSERT statement contain the below line where left is string function and so it's expecting () around it

left='44'

You should escape it using backtique like

UPDATE parts SET 
name='How do you use it?', 
part_order='0', 
top='334', 
`left`='44', <-- Here
width='150', 
height='26', 
value='', 
script='on mouseup\nanswer \"To use jsCard, all you need ...', 
visible='1', 
enabled='1', 
style='5', 
family='0', 
locktext='0', 
hilite='0', 
autohililte='1' 
WHERE stacks_id=1 
AND cards_id=1 
AND part_id=19

score 2 · Answer 2 · answered Jul 01 '14 at 21:11

2

left is a MySQL keyword. If you want to use it as an identifier (such as a column name) wrap it in backticks. It's a good idea to wrap all your identifiers in backticks anyway for clarity.

UPDATE parts SET 
  name='How do you use it?', 
  part_order='0', 
  top='334', 
  `left`='44', 
  width='150', 
  height='26', 
  value='', 
  script='on mouseup\nanswer \"To use jsCard, all you need is an account! From there you can create stacks, then cards, then you can start building on those cards. To build on cards, simply double click on an object in the tools palette, assign a name to your new object, then, if it is a button, add a script to make it do wonderful things!\"\nend mouseup',
  visible='1', 
  enabled='1', 
  style='5', 
  family='0', 
  locktext='0', 
  hilite='0', 
  autohililte='1' 
  WHERE stacks_id=1 AND cards_id=1 AND part_id=19

answered Jul 01 '14 at 21:11

I knew there was "one" of those that was a reserved word lol – Funk Forty Niner Jul 01 '14 at 21:12
Thanks, that fixed that issue. However, now I am receiving the error (using echo mysql_error(); ): "Unknown column 'autohililte' in 'field list'". Can you help me understand this? – user2370460 Jul 01 '14 at 21:14
1

You've probably mis-spelt the column name. You should read the messages properly before posting questions here. – Jul 01 '14 at 21:15
@MikeW Thanks, I didn't think! Now it is working! Just wondering, what is 'field list'? It is not the name of the database, or the table, so I am not sure what is means. – user2370460 Jul 01 '14 at 21:18
@user2370460, it means the list of fields/columns in your UPDATE query. – Rahul Jul 01 '14 at 21:20
`name` is a field, `part_order` is a field, all together is a list of fields – Jul 01 '14 at 21:20
all that interaction with mike and you voted on the other (also correct) answer - weird – Jul 01 '14 at 21:22

MySQL Syntax Error - unsure of issue

2 Answers2