I'm attempting to create an anti-bruteforcer for the login page on a website. Unfortunately, my query is not working as expected. I would like to test how many times an IP address has attempted to login, and also return the ID of the user for my next step in the login process. However, I'm having a problem with the query... for one thing, this would only return rows if it was the same user as they had been trying to login to before. I need it to be any user. Secondly, regardless of whether I use LEFT JOIN, RIGHT JOIN, INNER JOIN or JOIN, it will not return the user's ID unless there is a row for the user in login_attempts.

SELECT COUNT(`la`.`id`), `u`.`id`
FROM `users` AS `u` LEFT JOIN `login_attempts` AS `la`
ON `u`.`id` = `la`.`user_id`
WHERE `u`.`username` = 'admin' AND `la`.`ip_address` = '127.0.0.1' AND `la`.`timestamp` >= '1'