MySQL insert with multiple fields and where clause

Question

I'm trying to construct a query for a cron that will run.

mysql_query("UPDATE `stocks` SET price='$pricez', open='$openz', high='$highz', low='$lowz', change='$changez', time='$times', percent='$percentz' WHERE symbol = '$symbolz' ");

The error I get is

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 'change='-0.10', time='1406050151', percent='-0.35%' WHERE symbol = 'ALMB.CO'' at line 1

Scavenged SOF and have yet to find a solution.

Any reserved words being used? yep.. change wrap change in back ticks change — xQbert
– xQbert, Commented Jul 22, 2014 at 17:32

xQbert · Accepted Answer · 2014-07-22 19:01:43Z

2

Reserved words just bit you: Change is a reserved word thus needs to be escaped: http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

mysql_query("UPDATE `stocks` SET price='$pricez', open='$openz', high='$highz', 
low='$lowz', `change`='$changez', time='$times', percent='$percentz' WHERE symbol = '$symbolz' ");

So what is a reserved word?

They are words the engine uses to interpert specific requested commands. When these words are used as identifiers for tables or columns they must be treated in a specific manner usually escaping the words for the RDBMS involved.

edited Jul 22, 2014 at 19:01

answered Jul 22, 2014 at 17:35

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cheruvian · Accepted Answer · 2014-07-22 17:34:50Z

2

Looks like you are using some reserved words for column names: Change and Time You can escape these with backticks (`), or choose new coumn names

UPDATE `stocks` 
SET     `price`='$pricez', 
        `open`='$openz', 
        `high`='$highz', 
        `low`='$lowz', 
        `change`='$changez', 
        `time`='$times', 
        `percent`='$percentz' 
WHERE symbol = '$symbolz'

answered Jul 22, 2014 at 17:34

Cheruvian

5,9171 gold badge28 silver badges36 bronze badges

5 Comments

xQbert Over a year ago

dev.mysql.com/doc/refman/5.5/en/reserved-words.html Open, time and percent don't appear on reserved words list for MySQL. Change does however.

Cheruvian Over a year ago

Sorry was thinking T-SQL. Time is definitely on the list.

andrew Over a year ago

I like the edit with everything in backticks, that's the way to go there's no doubt then +1

Cheruvian Over a year ago

I lied Time is a keyword but can be used without backticks. When In doubt escape everything!

xQbert Over a year ago

@andrew while it does reduce the risk, it obfuscates the underlying problem to future maintenance. I'd much rather see it used only when needed; and only when it's too late to change the column name.

Collectives™ on Stack Overflow

MySQL insert with multiple fields and where clause

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related