PHP CookBook

Recipe 8.27 Program: Website Account (De)activator

When users sign up for your web site, it's helpful to know that they've provided you with a correct email address. To validate the email address they provide, send an email to the address they supply when they sign up. If they don't visit a special URL included in the email after a few days, deactivate their account.

This system has three parts. The first is the notify-user.php program that sends an email to a new user and asks them to visit a verification URL, shown in Example 8-4. The second, shown in Example 8-5, is the verify-user.php page that handles the verification URL and marks users as valid. The third is the delete-user.php program that deactivates accounts of users who don't visit the verification URL after a certain amount of time. This program is shown in Example 8-6.

Here's the SQL to create the table that user information is stored in:

CREATE TABLE users (
 email VARCHAR(255) NOT NULL,
 created_on DATETIME NOT NULL,
 verify_string VARCHAR(16) NOT NULL,
 verified TINYINT UNSIGNED
);

You probably want to store more information than this about your users, but this is all that's needed to verify them. When creating a user's account, save information to the users table, and send the user an email telling them how to verify their account. The code in Example 8-4 assumes that user's email address is stored in the variable $email.

Example 8-4. notify-user.php

// generate verify_string
$verify_string = '';
for ($i = 0; $i < 16; $i++) {
    $verify_string .= chr(mt_rand(32,126));
}

// insert user into database
if (! mysql_query("INSERT INTO users (email,created_on,verify_string,verified)
    VALUES ('".addslashes($email)."',NOW(),'".addslashes($verify_string)."',0)")) {
    error_log("Can't insert user: ".mysql_error());
    exit;
}

$verify_string = urlencode($verify_string);
$safe_email = urlencode($email);

$verify_url = "http://www.example.com/verify.php";

$mail_body=<<<_MAIL_
To $email:

Please click on the following link to verify your account creation:

$verify_url?email=$safe_email&verify_string=$verify_string

If you do not verify your account in the next seven days, it will be
deleted.
_MAIL_;

mail($email,"User Verification",$mail_body);

The verification page users go to when they follow the link in the email message updates the users table if the proper information has been provided, as shown in Example 8-5.

Example 8-5. verify-user.php

$safe_email = addslashes($_REQUEST['email']);
$safe_verify_string = addslashes($_REQUEST['verify_string']);

if ($r = mysql_query("UPDATE users SET verified = 1 WHERE email 
    LIKE '$safe_email' AND 
    verify_string = '$safe_verify_string' AND verified = 0")) {
    if (mysql_affected_rows() == 1) {
        print "Thank you, your account is verified.";
    } else {
        print "Sorry, you could not be verified.";
    }
} else {
    print "Please try again later due to a database error.";
}

The user's verification status is updated only if the email address and verify string provided match a row in the database that has not already been verified. The last step is the short program that deletes unverified users after the appropriate interval, as shown in Example 8-6.

Example 8-6. delete-user.php

$window = 7; // in days 

if ($r = mysql_query("DELETE FROM users WHERE verified = 0 AND 
    created_on < DATE_SUB(NOW(),INTERVAL $window DAY)")) {
    if ($deleted_users = mysql_affected_rows()) {
        print "Deactivated $deleted_users users.\n";
    }
} else {
    print "Can't delete users: ".mysql_error();
}

Run this program once a day to scrub the users table of users that haven't been verified. If you want to change how long users have to verify themselves, adjust the value of $window, and update the text of the email message sent to users to reflect the new value.