Update query that needs to find the 'newest' row

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

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 = (SELET 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.

Answer 2

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';