delete whole row after x mins in mysql db and PHP

I'm trying to setup a sql db where users must confirm their email address. I'm wondering if there is a way to auto delete the users in the sql db that attempted to register (submit their info) but not confirm their email address after x amount of mins.

the script that i have written inserts their info straight into the db and then updates a column on the db to "ACTIVE" once the users has confirmed, so i don't insert the data once they have activated, but before with a status of "NON_ACTIVE". Then when they confirm their email address, that status turns to "ACTIVE".

so if a users submits their info, but fails to confirm their email address, their details are still in the DB. I need something that will auto delete a row with the status "NON_ACTIVE" after x minutes to when they subm开发者_Go百科itted their info. I have another column in the DB with their exact submit time and date.

any ideas?

---

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

You can just run this command via a cronjob on a linux system with cron -e someUser and entering the following line

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

This will run the command every minute. Please note, that your someUser has to be able to access the mysql-database without a password, e.g. by putting the password into $HOME/.my.cnf

---

I would write a statement similar to @Lars:

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

but you don't need to have a cron job or a trigger for this. Just put it in one of the PHP pages your users access (perhaps the Login or Register page?). It may even be set not to run every time but say, once for every hundred Logins if you don't mind some records get deleted a few seconds or minutes later.

---

Store the create time for the row in a new column.

Have another script (called by cron?) or part of your other site code periodically delete any/all rows in the table with Status="NON_ACTIVE", which were created more than X minutes ago.

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

Two jobs attempting to delete the same record is a possible race condition, but not really bad.

---

No need to use a database at all. Just ask a user for their email, send a verification link and start registration only after getting this link clicked.

---

How about using a 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 it here