mysql/php: update one record where 2 conditions match

[unwashed potato!]

having problem getting my database to only update one record based on 2 conditions. i was saying if the user is logged in update table latestThreadView set time = $time where userId = $userId and threadId = $threadId

this works when logged in but when i then say if not logged in everytihng the same but i say where userId = 0 and threadId = $threadId, this ends up updating every record that threadId matches.

is it possible to update based on 2 conditions?

i have tried putting 'and' between the conditions, a comma, '&', and using WHERE before both conditions but none seem to fix the problem, infact sometimes this results in the record being updated that was set by someone who was logged in rather then the record created by the current unlogged in user which is really odd

 

[marc2003]

i guess you're using either cookies or sessions to determine if a user is logged in? if they're not logged in, this query shouldn't even be running at all??

 

[unwashed potato!]

i guess you're using either cookies or sessions to determine if a user is logged in? if they're not logged in, this query shouldn't even be running at all??

if they are not logged in it will still run however it will store the userId as 0, which i will take as guest. this will then allow me to have a list of latest viewed thread but also at the same time the latest thread the user has viewed if they are logged in.

if not logged in i use sessions to record latest viewed thread which obviously will only last for the session so not as good as having an account which will store the information until the thread is deleted or the user removes it manually

 

[marc2003]

set the user & threadid to be the primary key. then use a query something like this

INSERT INTO latesthreadview(user, threadid, time) VALUES('$user', '$threadid', NOW()) ON DUPLICATE KEY UPDATE time = NOW())

if the combination of userid and threadid does not exist already a new record will be created. however, if they do exist, then the existing record will be updated with the new time.

 

[unwashed potato!]

wahoo that got it working. think part of the reason it wasn't working as well was because when i logged out to view a thread as a guest i hadn't set php to unset the userId session so it was some reason using this but it thought i was logged out so it was updating the record with userId = my id, and also userId = guest id.

thanks for the help

 

[unwashed potato!]

ok new problem, related to the same thing kind of.

i have the first 20 records for latest viewed thread displayed for the user if logged. what i have done is allowed the user to remove each record from the list, however rather then allow them to remove the whole thing i thought it would be better to have it so the user only hides it from the list and resets the views amount back to 0 but still keeping the real views amount hidden from them.

this works however when they re view the thread again i want it added back to the list but this is not happening and i can't for the life of me work out why.

i have field for the table is called hide. when hide is set to 1 it is hidden, else shown. when the user views the thread mysql should change hide back to 0 but it never does. here is the code i use for when a user views the thread.

$insertViewQuery = ("INSERT INTO threadview (userId, threadId, threadTitle, hide) VALUES('$uId', '$adId', '$adTitle', '0') ON DUPLICATE KEY UPDATE views = views+1");

any reason why hide would not be set to 0? i have tried putting 0 as '0' 0 and '$unHide' and setting that to 0 but nothing.