Need help with a MySQL query

Question

My query needs to do this

• get user details
• get last login out of a table of logins that are logged
• get number of failed logins, but only from the last 3 logins of that user

My problem seems to be in the subquery. I want the WHERE part of that to reference the user_id that was matched earlier in the outer query. Is that even possible? Have I made a huge mess of this query?

$query = 'SELECT users.id, 
                         users.email, 
                         users.password, 
                         users.first_name, 
                         users.last_name,
                         max(user_logins.datetime) as last_login,
                         count(user_logins.failed) as failed_logins,

            FROM users, user_logins
            WHERE users.email = ' . Database::instance()->escape($email) . '
                AND users.id = user_logins.user_id
                AND user_logins.datetime IN (SELECT datetime FROM user_logins WHERE user_id = users.id  )
            LIMIT 1';

The last_login seems to be working great. The reason I want the last 3 is so that I can check if the last 3 are failed, then set a timeout for this user to log in.

I can not go back and do an UPDATE users SET failed = 1 WHERE failed = 0 because then I won't have an accurate log of user logins. I'd like to see any that fail.

What am I doing wrong? Thanks

Answer 1

I would try the following. It should give you the three latest logins in a sub-query, SUM up the number of failed logins and get the time of the last login.

MySQL allows not to list every column selected in the GROUP BY, with other databases you would have to add all columns of users that you need to the GROUP BY.

$query = '
SELECT u.id, 
       u.email, 
       u.password, 
       u.first_name, 
       u.last_name,
       MAX( ul.datetime ) as last_login,
       SUM( ul.failed ) as failed_logins,
FROM users u
JOIN ( SELECT user_id, datetime, failed
       FROM user_logins
       ORDER BY datetime DESC
       LIMIT 3 ) ul
  ON ( u.id = ul.user_id )
WHERE u.email = ' . Database::instance()->escape($email) . '
GROUP BY u.id
';