Update multiple rows/columns in php/mysql

Question

I have the following line:

$reset = "UPDATE pidgeon SET obt='" . $hour . "' WHERE tag='" . $tag . "'";

Which updates just fine. However I need to update an additional row (kill) and I keep getting syntax errors. I've tried the following:

$reset = "UPDATE pidgeon SET obt='" . $hour . "', kill='1' WHERE tag='" . $tag . "'";

$reset = "UPDATE pidgeon SET kill='1', obt='" . $hour . "' WHERE tag='" . $tag . "'";

$reset = "UPDATE pidgeon SET obt='" . $hour . "', kill='" . $num . "' WHERE tag='" . $tag . "'";

I've even done 2 separate UPDATE queries and I get the same syntax error message. I've narrowed it down to the system having issue with the kill row but I'm not sure what's the issue. I've tried setting kill as INT, SMALLINT, BOOL, even CHAR and trying to use 't/f' as values for it. I still get a syntax error. Any suggestions?

possible duplicate of [Syntax error due to using a reserved word as a table or column name in MySQL](http://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name-in-mysql) — michaelb958--GoFundMonica, May 07 '14 at 00:16

score 1 · Accepted Answer · answered Dec 22 '13 at 18:09

1

KILL is a reserved keyword, so you'll have to enclose it in backticks, like so:

$reset = sprintf("UPDATE pidgeon SET `kill`='1', obt='%s' 
WHERE tag='%s'", $hour, $tag);

answered Dec 22 '13 at 18:09

Amal Murali

75,622
18
128
150

@AlexP: Absolutely. It's always better to use a more descriptive column name. – Amal Murali Dec 22 '13 at 18:16
You are the best! Thank you so much! Its always the silliest errors. – xv47 Dec 22 '13 at 18:17

score 0 · Answer 2 · edited Dec 23 '13 at 14:20

0

Kill is actually a syntax, you can't use them directly.

Just replace kill with another name.

edited Dec 23 '13 at 14:20

Ahmed Siouani

13,701
12
61
72

answered Dec 22 '13 at 18:12

Kirk

4,957
2
32
59

Update multiple rows/columns in php/mysql

2 Answers2