Email Verification Script using PHP and MySQLi

In last article we have learned How to Create Registration Form with PHP and MySQLi. Now you might be looking for that How to send a email confirmation link or email to newly registered user in order to prevent from spamming. So here I am going to show you that how we can send confirmation email to newly registered user. Here we need to table one for ‘users’ and the other one if for ‘confirmation’. So here are the structure for table tables.

Live DemoDownload

For table ‘users’

CREATE TABLE `users` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `name` varchar(50) NOT NULL,
  `status` bit(1) NOT NULL,
  PRIMARY KEY (`uid`),
  UNIQUE KEY `username` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

This is same table that we have made in our previous article in which we learn How to Create Registration Form with PHP and MySQLi. We have just added a new field ‘status’ to determine whether the email is activated or not.

For table ‘confrim‘

DROP TABLE IF EXISTS `confirm`;
CREATE TABLE `confirm` (
`cid` int(10) NOT NULL AUTO_INCREMENT,
`email` varchar(50) NOT NULL,
`confirm_key` varchar(300) NOT NULL,
PRIMARY KEY (`cid`),
UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

Logic Behind Them

Now lets have a look on logic behind all process. First when user input his email, password and username. We insert these data into table ‘users‘ and the field status by default will be 0 (zero). Now we generate a unique key and add this key to table ‘confirm‘ along with user input email. After this process we send an email to user’s entered email with confirmation link. When user open that link it redirects to new page where the key is being checked. If the key matched with any of row in database, it fetch email field of that matched row and went to table ‘users‘ and searched for respective email record and UPDATE status of that record to 1. This is simple logic behind this process.

Let’s move towards code.

config.php this file contain basic database connection detail.

define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_DATABASE', 'eggslab');
$db = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);
	
$url = "http://yourwebsite.com";

$url is the basic variable in which I have stored website address.

index.php this file contain basic HTML Form and some PHP Code

Basic HTML form. In my case it includes only an email and a password field.

<form method="post">
<table width="300" border="0" cellpadding="10" cellspacing="10">
<tr>
<td><div align="right" class="label">Email</div></td>
<td><input type="text" name="email" class="input" /></td>
</tr>
<tr>
<td><div align="right" class="label">Password</div></td>
<td><input type="password" name="password" class="input" /></td>
</tr>
<tr>
<td colspan="2"><div align="center"><?php echo $msg;?></div></td>
</tr>
<tr>
<td>&nbsp;</td>
<td><div align="right">
<input type="submit" name="submit" class="button" value="Submit" />
</div></td>
</tr>
</table>
</form>

Now let’s see PHP Code. Obviously we have to perform action when user hit Submit button, so in PHP we can do it as:

if(isset($_POST["submit"]))
{
	// Code to execute
}

Now lets check if user enter a valid format of email. So,

if (!filter_var($email, FILTER_VALIDATE_EMAIL))
{
  $msg = "Invalid email format"; 
}else
{
		//Code to execute.
}

If user has not entered valid format email address it will show him an error “Invalid email format”. Otherwise it will move to next portion of code. Here, we will first check that whether the entered email is already existing in database or not. If it exist it show error “Sorry…This email already exist.” Otherwise will move ahead. Here is the code to check existence of email in database:

$sql="SELECT email FROM users WHERE email='$email'";
$result=mysqli_query($db,$sql);
if(mysqli_num_rows($result) == 1)
{
	$msg = "Sorry...This email already exist.";
}
else
{
	//Code to execute.
}

Live DemoDownload