get id after update

[Destramic]

is it possible to get the value of user_id when updating a row?..this query works but im struggling to get the user_id (doesnt return a result)

UPDATE users 
SET status = 'Active',
             token = NULL 
WHERE token = 'test' 
AND status = 'Blocked' 
AND (SELECT user_id as `user_id`)

if i'm not able to do it that way then i could use the following, but would be great if i was able to do this all in one query.

SELECT LAST_INSERT_ID();

thank you

[Barand]

If there is a way I am not aware of it. LAST_INSERT_ID (as it says in the tin) only works for insert statement, returning the value of the auto_incremented id.

 

You may have to do this first to see which ones will be updated

SELECT user_Id 
FROM users
WHERE token = 'test'
AND status = 'Blocked'

but it depends on what you want to do with the user_id once you know it. It may be that a trigger function is required if, say, you want to log the id.

[Destramic]

well the user will go to the url which will have a token in it...if token exists then it will unblock account.

 

now i want the user_id so i can send the user to the change password action...I'm passing the user_id as a parameter so if the  $user_id parameter is not null in the change password action he/she can change password without logging in.

 

else if null and not authenticated then redirected to log in...if that sounds right

 

i couldnt think of another way of letting the user change password without being authenticated.

[mac_gyver]

actually, if your UPDATE query contained another SET term of - user_id = LAST_INSERT_ID(user_id), your second query of SELECT LAST_INSERT_ID() would return the user_id value.

 

edit: and you don't actually need to explicitly run the second query, you can just call the lastInsertId.. function/method of whatever php database library you are using.

Edited June 14, 2015 by mac_gyver

[Barand]

Neat!

[Destramic]

actually, if your UPDATE query contained another SET term of - user_id = LAST_INSERT_ID(user_id), your second query of SELECT LAST_INSERT_ID() would return the user_id value.

 

edit: and you don't actually need to explicitly run the second query, you can just call the lastInsertId.. function/method of whatever php database library you are using.

 

worked like a dream thank you...its always fun to find easier ways of doing things