When users sign up for your website, 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 send an email to a new user. The second is the verify-user.php that handles the verification. The third is the delete-user.php program that deactivates accounts of users who don’t visit the verification URL.
The SQL Schema for user information is as following:
CREATE TABLE users ( email varchar(255) NOT NULL, created_on DATETIME NOT NULL, verify_string varchar(16) NOT NULL, verified TINYINT UNSIGNED );
First Part: notify user
//connect to the database $db = new PDO('sqlite:users.db'); $email = 'david'; //Generate verify_string $verify_string = ''; for($i = 0; $i < 16; $i++) { $verify_string .= chr(mt_rand(32,126)); } //Insert user info database $sth = $db->prepare("INSERT INTO users (email, created_on, verify_string, verified) VALUES (?, datetime('now'), ?, 0)"); $sth->execute(array($email, $verify_string)); $verify_string = urlencode($verify_string); $safe_email = urlencode($email); $verify_url = "http://www.example.com/verify-user.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 Verfication", $mail_body);
Second Part: verify user
//Connect to the database $db = new PDO('sqlite:users.db'); $sth= $db->prepare('UPDATE users SET verified = 1 WHERE email = ? AND verify_string = ? AND verified = 0'); $res = $sth->execute(array($_GET['email'], $_GET['verify_string'])); if(!$res) { print "Please try again later due to a database error."; } else { if($sth->rowCount == 1) { print "Thank you, your account is verified."; } else { print "Sorry, you could not be verified"; } }
Third Part: delete user
//connect to the database $db = new PDO('sqlite:users.db'); $window = '-7 days'; $sth = $db->prepare("DELTE FROM users WHERE verified = 0 AND created_on < datetime('now', ?)"); $res = $sth->execute(array($window)); if($res) { print "Deactivated ". $sth->rowCount() . " users.\n"; } else { print "Can't delete users.\n"; }
Run the program once a day to scrub the users table of users that haven’t been verified. You might change $window value to adjust the expiry days.