Delete user from database if email not confirmed

Question

I am currently working on a registration based website, and I have the server sending an activation email to the user upon registration. This is all done in PHP so, as you can imagine, I am using the mail() function.

This is all fine and dandy, once the user gets the email and clicks the activation link, the 'active' field that is in the 'Users' table is set to true. Here's the problem though, in the case that a user does not confirm their email address, what am I to do?

I have thought of holding details like the date and time the user registers but I don't know how to proceed with this data. How do I have the server automatically delete the user from the database after a set amount of time?

That's what I think I should be asking, but in all honesty I don't know the usual protocol...

Conclusion: Since Cron is for Unix based servers I've had to pass on it, but I found it very interesting that I could just use the Windows Task Scheduler that is built into Windows. This at least means I can test it on my PC before any server hosting. Thank you all

You might find this solution interesting: Don't put unconfirmed emails in the database in the first place http://stackoverflow.com/a/3795010/398242 — Wesley Murch, Jan 28 '12 at 22:31

score 6 · Accepted Answer · answered Jan 28 '12 at 22:28

You should definitely store the date and time that the activation link was sent.

There isn't really a way to tell the server to automatically delete stale user data, but it's easy enough to code up yourself. Assuming you have access to cron on your server, you can set up a cronjob to run (for example) every night at 2am and execute a PHP script that searches the database for users who were sent a link more than X days ago but never confirmed it

score 3 · Answer 2 · answered Jan 28 '12 at 22:29

3

i think the solution would be Storing the timestamp while sending the mail. now run a cron every 15minutes which would check that which values are having timestamp more than 24hrs or any timelimit you want and then delete it from db

answered Jan 28 '12 at 22:29

saun jean

739
3
12
25

score 2 · Answer 3 · answered Jan 28 '12 at 22:31

Just call in your index.php file the following code. (Why index.php ? - because it is requested every time and can "act" as a cronjob.)

(Just Pseudo Code - might need some tweaks!) mysql_query("DELETE FROM user WHERE active = 'false' AND registerTime < (NOW-60*60*24*7)")

This will delete all Users which have not been activated within 7 Days.

It's just a concept idea i think you can build on.

score 1 · Answer 4 · answered Jan 28 '12 at 22:28

1

You should look into cronjobs that you run daily. Simply put in a field in your database with the time your user registered.

answered Jan 28 '12 at 22:28

moffepoffe

372
1
8

Delete user from database if email not confirmed

4 Answers4