Delete whole line after x mins in mysql db and php

Question

I am trying to configure sql db where users must verify their email address. I am wondering if there is a way to automatically delete users in sql db who tried to register (send their information) but did not confirm their email address after x minutes.

The script I wrote inserts its information directly into db, and then updates the column on db to "ACTIVE" after confirmation by users, so I do not insert data after they are activated, but earlier with the status of "NON_ACTIVE". Then, when they confirm their email address, this status goes to ACTIVE.

therefore, if users send their information but do not confirm their email address, their data is still in the database. I need something that will automatically delete the line with the status "NON_ACTIVE" after x minutes when they send their information. I have another column in the database with their exact time and date of dispatch.

any ideas?

Answer 1

I would write an instruction similar to @Lars:

DELETE FROM users WHERE (mailsent < NOW() - INTERVAL @x MINUTE) AND (state = 'NON_ACTIVE') 

but you do not need to have a cron job or trigger for this. Just place it on one of the PHP pages that you can access (for example, on the login or registration page?). It may even be configured not to run every time, but say once for every hundreds of logins, if you don't mind, some entries are deleted after a few seconds or minutes later.

Answer 2

SQL will be DELETE FROM users WHERE (now() - mailsent) > (120*60) AND state = "NON_ACTIVE" , where users are the table name, mailsent is the mailing timestamp and 120 is the number of minutes you want as timeout.

You can simply run this command via cronjob on a linux system using cron -e someUser and enter the following line

 * * * * * mysql myDatabase < 'DELETE FROM users WHERE (now() - mailsent) > (120*60) AND state = "NON_ACTIVE"' 

This will be done every minute. Note that your someUser should have access to the mysql database without a password, for example. by putting the password in $ HOME / .my.cnf

Answer 3

Save the row creation time in a new column.

Is there another script (called cron?) Or part of your other site code, periodically delete all / all rows in the table with Status = "NON_ACTIVE" that were created more than X minutes ago.

Psudeo-sql: Remove from UserTable Where (status = "NON_ACTIVE" AND (Minutes (now () - createdate))> 30);

Two tasks trying to delete the same record are a possible race condition, but not very bad.

Answer 4

No need to use the database at all. Just ask the user about their email, send a confirmation link and start registration only after this link has been clicked.

Answer 5

How about using mysql event?

https://dev.mysql.com/doc/refman/5.1/en/events.html

 CREATE EVENT delete_event ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY ON COMPLETION PRESERVE DO BEGIN DELETE messages WHERE date < DATE_SUB(NOW(), INTERVAL 7 DAY); END; 

I found here