Question

My logout function needs to update the latest row of a list of logins.

This is what I have came up with, however it doesn t even pass syntax validation.

$query =
     UPDATE user_logins
     SET active = 0
     WHERE user_id =   . Database::instance()->escape($this->getCurrentUserId()) .  
     AND datetime = MAX(datetime) LIMIT 1 ;

Essentially there may be 30 or so with the same user_id value. I d only like to update the last login. This will obviously be the most recent datetime value.

What am I doing wrong?

Answer 1

This should work, provided your datetime column is a timestamp or a datetime type field.

$query =
     UPDATE user_logins
     SET active = 0
     WHERE user_id =   . Database::instance()->escape($this->getCurrentUserId()) .  
     ORDER BY datetime DESC LIMIT 0,1 ;

Answer 2

~~You would have to use a subquery for this.~~

$query =
     UPDATE user_logins
     SET active = 0
     WHERE user_id =   . Database::instance()->escape($this->getCurrentUserId()) .  
     AND datetime = (SELECT MAX(datetime) FROM user_logins WHERE user_id =    . Database::instance()->escape($this->getCurrentUserId()) .  ) LIMIT 1 ;

Or something like this.

Edit: As the comment states, this will not work because of lack of support in MySQL. I ll leave this answer for the reference.

友情链接