CODEWITHSUNDEEP
phpmysqlcodeigniter
arthur27
arthur27

Reputation: 49

Auto delete from Mysql database

I am currently trying to create a 'reset password' token. A token that gets generated when someone on my site wants to reset their password. Currently, once the token is created I save it in my database. I want to make the token exist in the database for only 24 hours and then automatically delete itself but I have no idea how to do this. Any help would be appreciated.

I generate the token pretty simply, by creating a string of random characters. Then I save the token and the email of the forgotten password in the DB as follows

'INSERT INTO reset_token (token, email) VALUES ("'.token.'", "'.email.'")'

Upvotes: 0

Views: 1049

Answers (1)

Xpleria
Xpleria

Reputation: 5863

Edit

This can be done using the Event Scheduler. You need to create an event using the CREATE EVENT syntax.

To delete an entry you can do the following:

public function schedule_delete($table, $column, $value, $hours = 24) {
    $sql = "CREATE EVENT `delete_{$table}_{$column}_$value` 
                ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL $hours HOUR
                DO DELETE FROM `$table` WHERE `$column` = '$value'";
    $db->query($sql);
}

Note that the name has to be unique. Which means delete_{$table}_{$column}_$value has to be unique, otherwise you will get an error.

For MySQL Events to work, the Event Scheduler has to be ON. You can turn it on using the following query:

SET GLOBAL event_scheduler = ON;

When you say "Auto Delete", YOU need to "Auto Delete" it.

In cases where you cannot turn on the scheduler because you're on a shared hosting server and you do not have enough privileges, you can manually delete the entries by checking their timestamps.

So somewhere in the code there needs to be a query performing the delete operation. To delete expired entries, you need to know if they are expired. For this you need to store the timestamp along with the token.

Now where you put this, depends on how often you want to delete expired entries. In OP's case, it can be done just before the lines of code where tokens are checked for validity. i.e., if there is a function validate_token that would see if the token exists in the database and is valid, you can put it at the beginning of that function.

function validate_token($token) {
    $expiry = time() - 60*60*24;
    // perform query to delete all tokens that have a timestamp less than $expiry

    // Now run the query to check if the token exists in the database. 
    // You have only valid tokens left!
}

Upvotes: 2

Related Questions