Users registration in php: how to delete users that have not completed activation

Question

I am going to write user registration in php and don't want to use any framework. Usually it looks like this:

1. User fills a registration form.
2. My app send an email to user's email address.
3. User complete registration by clicking link in email.

In first step i add user information into a special database table that stores inactivated users. In third step i move user from that table into the table for activated users.

I have a few question:

1. Is this logic good? If not what is the common practice?
2. How and when i can to delete user from the first table if hi have not passed activation?
3. Are there lightweight libraries (not frameworks) for this purpose?

Thanks!

Answer 1

There is no need of using php session mechanism to delete the data from db,"as you said in comment"

You can delete the data when you want. May be in 24 hours or 7 days or 1 month. May be it is depend upon traffic you will obtain.

But as soon as user registers store the time in reg_time column. By which during deletion the comparison will done with reg_time.

if you want to delete after 24 hours then

delete from ACCOUNT_TBL_DETAIL
where usrActivated=0 AND regTime <= UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 1 DAY));

After 7 days

delete from ACCOUNT_TBL_DETAIL
where usrActivated=0 AND regTime <= UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 7 DAY));

To schedule SQL script

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;

this is a simple cron script