Search code examples
phpmysqlcron-taskforgot-passwordemail-verification

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

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.

Solution

  • 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(), ....