Page 1 of 1

SOLVED - How to use MySQL UPDATE WHERE?

Posted: Sat Feb 17, 2007 6:29 am
by matth2004
Hi,

I'm trying to update the database but it must find an entry to update where two variables match the columns. I'm currently trying this:

Code: Select all

mysql_query("UPDATE tickets SET status='Closed' WHERE username='$tempuser2' AND index='$id'") or die (mysql_error());
But as you might see this doesn't work. Would anybody be able to tell me what to use instead of AND?

Regards,
Matt

Posted: Sat Feb 17, 2007 6:40 am
by superdezign
No no, your syntax is correct. There's a problem elsewhere. What error are you getting?

Posted: Sat Feb 17, 2007 6:44 am
by matth2004
I'm getting: 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 'index='5'' at line 1

Regards,
Matt

Posted: Sat Feb 17, 2007 6:52 am
by superdezign
"index" is a field that takes integer values, correct?

Maybe try "index='".$id."'";

Posted: Sat Feb 17, 2007 6:55 am
by matth2004
Nope, doesn't work. Still get same error.

Posted: Sat Feb 17, 2007 6:58 am
by superdezign
Do you have queries elsewhere in your code that work correctly?

Posted: Sat Feb 17, 2007 7:01 am
by matth2004
Yeh I've got lots of other queries working perfectly but none that require two variables to match two columns in an update.

Regards,
Matt

Posted: Sat Feb 17, 2007 7:06 am
by superdezign
That doesn't mean that its the problem. Do any of the other queries use "index?" That's seeming to be the problem.

Posted: Sat Feb 17, 2007 7:10 am
by matth2004
I think you're right, I swapped it around putting the index first to make it:

Code: Select all

mysql_query("UPDATE tickets SET status='Open' WHERE index='$id' AND username='$usertemp2'") or die (mysql_error());
And now I'm getting: 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 'index='4' AND username='matty'' at line 1

I used another identifies instead of index and it works. Cheers.

Regards,
Matt

Posted: Sat Feb 17, 2007 8:50 am
by feyd
"index" is a reserved word. Do not use reserved words for field, table or database names.

Posted: Sat Feb 17, 2007 8:55 am
by Mordred
index is a reserved word for sql, use backticks around it. Actually, it is good practice to put backticks around ALL names in your query:

Code: Select all

UPDATE tickets SET status='Open' WHERE index='$id' AND username='$usertemp2'
(notice how INDEX is capitalised, as it is considered a reserved word)
write it like this:

Code: Select all

UPDATE `tickets` SET `status`='Open' WHERE `index`='$id' AND `username`='$usertemp2'

Posted: Sat Feb 17, 2007 5:57 pm
by matth2004
Thanks for that now all working well and good.