MySQL Syntax error on update query

Question

I'm getting an error about my query, and i'm not understanding what the problem might be. 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 'range = '55', atkspeed = '0.95', m_damage = '0', p_damage = '38', mprotection = ' at line 1

While the code i'm using is this one

 $id = mysql_real_escape_string($_POST["id"]);
 $value0 = mysql_real_escape_string($_POST["value0"]);
 $value1 = mysql_real_escape_string($_POST["value1"]);
 $value2 = mysql_real_escape_string($_POST["value2"]);
 $value3 = mysql_real_escape_string($_POST["value3"]);
 $value4 = mysql_real_escape_string($_POST["value4"]);
 $value5 = mysql_real_escape_string($_POST["value5"]);
 $value6 = mysql_real_escape_string($_POST["value6"]);
 $value7 = mysql_real_escape_string($_POST["value7"]);
 $value8 = mysql_real_escape_string($_POST["value8"]);
 $value9 = mysql_real_escape_string($_POST["value9"]);
 $value10 = mysql_real_escape_string($_POST["value10"]);


 $query="UPDATE char_stats SET vita = '$value0', mana = '$value1', speed = '$value2', range = '$value3', atkspeed = '$value4', m_damage = '$value5', p_damage = '$value6', mprotection = '$value7', pprotection = '$value8', hp5 = '$value9', mp5 = '$value10' WHERE id_char_stats='$id'";

I'm using also other very similar queries so i don't get what the problem might be. I was thinking about the underscore on char_stats so i tried using

char\_stats

for escape, but it's not working anyway.

Thanks in advance

It is probably something wrong with $value2 (like an extra Quotation mark/apostrophe) - mysql tends to show the bit of code just after the syntax error. — Giles
– Giles, Commented Sep 8, 2015 at 9:49
Stop using mysql_*, use MySQLi or PDO instead with prepared statements. That being said, show us what $query looks like. — Bart Friederichs
– Bart Friederichs, Commented Sep 8, 2015 at 9:52
There is some problem with "$value2" variable's value. Try to set static value and check whether you are getting any error or not? — MilanPanchal
– MilanPanchal, Commented Sep 8, 2015 at 9:54

Drew · Accepted Answer · 2015-09-08 10:08:00Z

1

create table t11
(
    id int not null,
    `range` int not null,
    speed int not null
);

update t11 set range='11', speed=1; -- blows up
update t11 set `range`='11', speed=1; -- fine
update t11 set `range`=11, speed=1; -- fine

Moral of the store: back-tick range. Even the create table blows up without it.

see mysql keywords and reserved words here. Range is one of them.

So your query would become:

$query="UPDATE char_stats SET vita = '$value0', mana = '$value1', speed = '$value2', `range` = '$value3', atkspeed = '$value4', m_damage = '$value5', p_damage = '$value6', mprotection = '$value7', pprotection = '$value8', hp5 = '$value9', mp5 = '$value10' WHERE id_char_stats='$id'";

edited Sep 8, 2015 at 10:08

answered Sep 8, 2015 at 10:02

Drew

25k10 gold badges47 silver badges81 bronze badges

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

1 Comment

Mastarius Over a year ago

Thanks a lot for your answer, this is solving the problem.

Collectives™ on Stack Overflow

MySQL Syntax error on update query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related