SOLVED - How to use MySQL UPDATE WHERE?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
matth2004
Forum Commoner
Posts: 40
Joined: Wed Sep 06, 2006 3:26 am

SOLVED - How to use MySQL UPDATE WHERE?

Post 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
Last edited by matth2004 on Sat Feb 17, 2007 5:58 pm, edited 1 time in total.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

No no, your syntax is correct. There's a problem elsewhere. What error are you getting?
matth2004
Forum Commoner
Posts: 40
Joined: Wed Sep 06, 2006 3:26 am

Post 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
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

"index" is a field that takes integer values, correct?

Maybe try "index='".$id."'";
matth2004
Forum Commoner
Posts: 40
Joined: Wed Sep 06, 2006 3:26 am

Post by matth2004 »

Nope, doesn't work. Still get same error.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Do you have queries elsewhere in your code that work correctly?
matth2004
Forum Commoner
Posts: 40
Joined: Wed Sep 06, 2006 3:26 am

Post 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
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
matth2004
Forum Commoner
Posts: 40
Joined: Wed Sep 06, 2006 3:26 am

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

"index" is a reserved word. Do not use reserved words for field, table or database names.
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Post 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'
matth2004
Forum Commoner
Posts: 40
Joined: Wed Sep 06, 2006 3:26 am

Post by matth2004 »

Thanks for that now all working well and good.
Post Reply