MySQL join not returning rows

Question

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'

Here's the output from DESC login_attempts

Field       Type                Null    Key Default Extra
id          int(10) unsigned    NO      PRI NULL    auto_increment
user_id     int(10) unsigned    NO      MUL NULL     
ip_address  varchar(15)         NO      MUL NULL     
timestamp   int(10)             NO          NULL     

This query does the same thing, except does not even select the ID if there is a row in login_attempts corresponding to it:

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

Answer 1

Try moving the ip_address and timestamp into the join condition. You need an outer join to login_attempts.

Answer 2

Don't use a join, and separate the queries. Do the counting of number of attempts in one, and returning the user id in the other:

sql 1:

select count(id) from login_attempts where ip_address="<ipaddress>"

sql 2:

select id from users where username="<username>"

If you really insist in using joins, for which i think it really is unnecessary:

select count(login_attempts.id), users.id
from users
cross join login_attempts
where users.username="<username>"
and login_attempts.ip_address="<ipaddress>"

Which i also think would be inefficient.