PHP MYSQL INTERVAL display how many time left?

Question

Basically, I have a login system with blocking system. Block if user have used more than 5 attempts, and if he will still attempt, the attempts count will grow.

To delete it, I do this:

"DELETE FROM login_attempts WHERE date < DATE_SUB(NOW(), INTERVAL :time) AND ip_address = :ip"

:time = the interval date

Example:

            if ($fetch['attempts'] < 6)
            {
                $time = "10 MINUTE";
            } 
            else if ($fetch['attempts'] < 10) 
            {
                $time = "1 HOUR";
            }
            else if ($fetch['attempts'] < 21)
            {
                $time = "1 DAY";
            }
            else if ($fetch['attempts'] > 21)
            {
                $time = "14 DAY";
            }

Basically what I am trying to do, I need to find out, how to tell the player when will he get unblocked.

If I know the amount of time when he will be unblocked, how can I echo the time till he gets unblocked? I don't want just to echo the date, I need to echo exactly how many days, hours, etc.

I've never done this, I am stuck at this point.

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timediff — Marc B, Jul 04 '13 at 14:26
What's the window for resetting the count? IE if there are > 5 fails per day or hour or ever? — ethrbunny, Jul 04 '13 at 14:48
@ethrbunny If you have attempts count up, and is less than 5, then it will reset in 5 minutes. — Jony Kale, Jul 04 '13 at 15:21
@ethrbunny Wait, no. If the user did not hit 5 fails, his count will get reset after 1 day (row just gets deleted). else, he will get blocked depending on the attempts count. — Jony Kale, Jul 04 '13 at 15:29
Say you have 3 fails in one hour. Then 4 fails in the next hour and subsequent hour. Is this 11 fails? Or does the window reset after the first 10 minutes or hour or so on? — ethrbunny, Jul 04 '13 at 15:40
If you have 3 fails, and on the 4th attempt you logged in, fails reset. If you had 3 fails in hour, and then 3 more fails later, you will get blocked for 10 minutes, then get unblocked with 6 fails (these fails will get reset after 1 day). — Jony Kale, Jul 04 '13 at 15:51
Just, as a starting point: http://stackoverflow.com/questions/17182001/get-relative-date-from-the-now-function/17182060#17182060 — BlitZ, Jul 08 '13 at 01:51

score 2 · Accepted Answer · answered Jul 08 '13 at 10:53

You wrote: "these fails will get reset after 1 day". Then it is not possible to reach more than 21 failed attempts, because on the 21st failed attempt, the account is locked for one day.

I think you should only reset the counter on a successful attempt.

That being said, let's get back to your actual question. You are trying to show how long the account will remain locked.

I would take the reverse approach: based on the number of failed attempts, calculate the date when the account will be unlocked, then calculate the remaining time until this date.

Step 1: how many failed attempts

SELECT COUNT(*) AS failed_count
FROM login_attempts WHERE account_id = :account AND ip_address = :ip

Step 2: date/time of unlock, based on the number of failed attempts

Solution in pure SQL:

SELECT
    CASE
        WHEN COUNT(*) <= 5 THEN DATE_ADD(MAX(date_of_attempt), INTERVAL 10 MINUTE)
        WHEN COUNT(*) <= 10 THEN DATE_ADD(MAX(date_of_attempt), INTERVAL 1 HOUR)
        WHEN COUNT(*) <= 20 THEN DATE_ADD(MAX(date_of_attempt), INTERVAL 1 DAY)
        ELSE DATE_ADD(MAX(date_of_attempt), INTERVAL 14 DAY)
    END AS unlock_date
FROM login_attempts WHERE account_id = :account AND ip_address = :ip

Note: I would build the above query dynamically from PHP, based on rules you will maintain in your application layer. But stick to this query since you get everything in one single query, and it runs almost instantly if you have proper indexes.

Step 3: how long until the unlock date/time

Either calculate this in PHP (should be trivial), or directly from within MySQL :

SELECT
    TIMEDIFF(
        NOW(),
        CASE
            WHEN COUNT(*) <= 5 THEN DATE_ADD(MAX(date_of_attempt), INTERVAL 10 MINUTE)
            WHEN COUNT(*) <= 10 THEN DATE_ADD(MAX(date_of_attempt), INTERVAL 1 HOUR)
            WHEN COUNT(*) <= 20 THEN DATE_ADD(MAX(date_of_attempt), INTERVAL 1 DAY)
            ELSE DATE_ADD(MAX(date_of_attempt), INTERVAL 14 DAY)
        END          
    ) AS time_remaining_in_hours
FROM login_attempts WHERE account_id = :account AND ip_address = :ip

score -2 · Answer 2 · answered Jul 04 '13 at 17:05

-2

You might wanna check out timediff function here, http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timediff

answered Jul 04 '13 at 17:05

Leon

79
2
6

2

This is more of a comment rather than a answer mate, already been told, I've took a look and thinking of something, but thanks anyway! – Jony Kale Jul 04 '13 at 17:19

PHP MYSQL INTERVAL display how many time left?

2 Answers2

Step 1: how many failed attempts

Step 2: date/time of unlock, based on the number of failed attempts

Step 3: how long until the unlock date/time