On display PHP MYSQL INTERVAL how much time is left?

Question

Basically, I have a login system with a blocking system. Block if the user has used more than 5 attempts, and if he still tries, the number of attempts will increase.

To remove it, I do the following:

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

: time = 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'm trying to do, I need to figure out how to tell the player when he will be unlocked.

If I know the time when it will be unlocked, how can I repeat the time until it is unlocked? I don’t want to just repeat the date, I need to echo exactly how many days, hours, etc.

I never did this, I am stuck at this point.

Answer 1

You wrote: "These unsuccessful attempts will get reset in 1 day." Then it is impossible to achieve more than 21 unsuccessful attempts, since at the 21st unsuccessful attempt the account is locked for one day.

I think you should use a reset counter on a successful attempt.

Having said that, return to your current question. You are trying to show how long your account will remain locked.

I would take the opposite approach: based on the number of failed attempts, calculate the date when the account will be unlocked, and 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 unlocking depending on the number of failed attempts

Pure SQL solution:

 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 with PHP, based on the rules that you will support at your application level. But stick to this query, as you get everything in one query, and it works almost instantly if you have the appropriate indexes.

Step 3: how long before date / time unlock

Compute this in PHP (should be trivial) or directly from 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 

Answer 2

You can see the timediff function here, http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timediff