TimeStamp Help

[TapeGun007]

I have a table where a timestamp is automatically stored with the UserID if they fail to login correctly.  I want to track each failed login attempt and at what time it occurred.

 

I set $LastAttempt to their last login attempt and the format is simply this: 

2015-07-28 01:46:08

 

I also set $date to the current date/time in the same format as above.

 

If they have more than 5 login attempts, I want to set a penalty.  I have a switch that sets $penalty to the number of minutes before they can attempt to log in again.  So if their failed log in attempts exceed 5, then it penalizes them 10 minutes before they can even try again.

 

What I want is for that user to have to wait 5 minutes before they can log in again.  I just cannot figure out the best way to go about this and could use some direction.  I've tried several pieces of code, but because I'm not understand how it works, I'm probably not modifying it correctly.

 

I'm open to suggestions, but what I'm assuming I would do is this:

 

I know they have 5 failed log in attempts, so I would take the timestamp, add the penalty to it and if the result is greater than the current timestamp then they cannot log in yet.  Then I would like to post how much longer before they can make another attempt.  Does this sound right?  

[Barand]

this will give the number of minutes since their last attempt (where the datetime is their last attempt)

SELECT TIMESTAMPDIFF(MINUTE, '2015-07-28 11:25:08', NOW());

[QuickOldCar]

Store each attempted login just as a date, additionally a 0/1 (fail/success)

Get that users count of timestamps up to 10 minutes ago...if is a fail last attempt and is a count of 5 or more then it keeps preventing them (store the timestamp and fail)...otherwise allows them (store the timestamp and success).

[TapeGun007]

I found a different way, but I'm betting this is a LONG way around the issue:

 

I basically just take a new var, $penaltytime and add that time to $LastAttempt.

 

$penaltytime = new DateTime($LastAttempt);
$penaltytime->add(new DateInterval($penalty));     
$penaltytime = $penaltytime->format('Y-m-d H:i:s');

 

Then I just compare if the $penaltytime is now greater than the current time, $date.

 

 

if ($penaltytime > $date){
            echo "<p>You have a penalty</p>";
}else{
            echo "<p>You have no penalty</p>";
        }

 

This actually worked.

 

However, I read several other articles about doing this in the mySQL mentioned by Barand, I would like to know exactly how to implement that.

 

I probably didn't write the best mySQLi here, but it does work.

 

            $sql="SELECT * FROM LoginAttempts WHERE LASalesID = '$ID' AND LACleared = 'no' ORDER BY LATimeStamp ASC";
            $rs=$con->query($sql);
            $rs->data_seek(0);
            
            // Get the total number of attempts
            $attempts = $rs->num_rows;
            
            // Cycle through all the login times and get the last login date/time.
            while($row = $rs->fetch_assoc()){
                $LastAttempt = $row['LATimeStamp'];
            }

 

How would I implement your method Barand?

 

The table is simply (LA = "last attempt")

LAID

LASalesID = The Sales Persons ID

LATimeStamp (The db auto generates this by default when a LASalesID is INSERTED)

LAFailed (This is so I can keep track of the number of times a log in has failed regardless of time/date, default is 'yes')

LACleared (If the log in failed and I reset this field to "yes" then they can log in again, default is 'no')

[Barand]

this will give you a count of the attempts and the minutes since the last attempt

$sql = "SELECT 
          COUNT(*)
        , TIMESTAMPDIFF(MINUTE, MAX(LATimeStamp), NOW()) 
        FROM LoginAttempts 
        WHERE LASalesID = '$ID' 
            AND LACleared = 'no' ";
$rs = $con->query($sql);
if ($rs->num_rows > 0) {
    list($count, $mins_elapsed) = $rs->fetch_row();
}