How do I write a cron job script for verified act emails & lost passwords timer?

Question

How do I write a cron for verified act emails & lost passwords timer?

I'm using apache on Linux server with mysql database and php code. In the database, besides the usual fields for an account, I have verified (a boolean that is 1 for yes and 0 for no), act_key (varchar(50) for sending an email to users with link to special pages for verifying their email or if they forgot their password), time(datetime) and temp(boolean that is 1 for forgot password or 0 for not).

When a user creates an account, I set the verified in the new act to 0, time=current date & time, temp=0 and generate act_key that I send in email to them. If they try to login and there act is not verified then they can't get in. If they click the link and go to a certain page within an hour I set verified to one and they can login. If they clink on the link after the one hour then I tell them that the key is invalid and there account was deleted and to make a new one.

If a user forgets their password, they go to this page where they enter their email and if the email matches a record in the database I set temp=1 and time=current date & time. I tell user that I sent out email regardless. If they clink this new link it goes to a page and if its less then an hour I make them reset there password and temp is set back to zero. If they click on the link after the hour, I tell them the key is invalid. After one hour temp is set back to zero.

The verification process was working fine until I added the time variable into the equation. I never really got the forgot password part working.

I know I need to setup a cron job to do two things every hour:

1. delete accounts were verified=0 and it's been more than an hour since created that act.
2. update accounts where temp=1 and it's been more than an hour since sent out forgot password email

My main questions are:

1. Am I using the correct datatype in my database for the time field? And if not which should I use?

2. How do I set the time variable?$time = getdate(); maybe, the default is null? $time is the variable i'm using to insert into dB record for time field.

3. How do I make the cron script? I'm using godaddy, do I just point the cron control to this php file and if so is this the code I need?

   //connect to cron
   
   /web/cgi-bin/php5 -f "$HOME/html/hourly.php";
   
   //connect to dB
   
   include 'db_connect.php'; 
   
   //delete any unverified email act records that are more than 1 hour old
   
   DELETE from members WHERE verified == 0 & time + 1 > NOW()
   
   //make temp=0 on any record where temp is 1 for more than an hour
   
   UPDATE members SET temp=0 WHERE temp == 1 & time + 1 > NOW()
   
   mysqli_close($mysqli);
   

4. Does any of my overall method sound fundamentally flawed?

Please thank you in advance.

Answer 1

As far as I can see, temp is useless. It means the same thing as unverified.

This SQL is broken:

DELETE from members WHERE verified == 0 & time + 1 > NOW();

• & is a bitwise operator. You want AND
• SQL use a single = not ==
• You will delete accounts less than an hour old, not more than an hour old

Try:

DELETE FROM members WHERE verified = 0 AND time + 1 < NOW();

Datetime is the correct field type for time, but I'd name the field creation_time or something so you have a clue what it means. You can set it's value by passing NOW() to SQL, e.g. INSERT INTO members SET time=NOW(), ....