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
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
';
精彩评论