Developing an eMail validation system using PHP and MySQL

Well, its great that we do create websites that require sign up from users. To prevent random sign ups and span, developers have developed an email verification system. This system can be seen in almost every website throughout the WWW. In this article, I am going to show you how you can develop a simple script that validates email address.
Firstly, let me explain the database structure:

The name of the database is test, and the name of the table is email. The field email is considered unique. The activatecode contains the corresponding activation code for email generated while the user was signing up. The field activatestatus is self explanatory. It contains one character either Y or N to denote the activation status.

Now, recipe that we are going to use to cook this script is the HTTP GET. PHP can read the GET instruction from URL and convert it into an associative array. The same thing we are going to use in this tutorial.
Let's Code!

//Initializing database connection starts
$host = 'localhost';
$user = 'test';
$password = 'test';
$database = 'test';
$con = mysqli_connect($host, $user, $password, $database);
if(!$con)
    {
    echo mysqli_error ($con);
    }
//connected to the database
//you can check if URL is in the correct format or not before whatever we are going to do here
$key = $_GET['key']; //key will be the URL parameter
$email = $_GET['email'];//for security, we will also consider email as the paramaeter
$query = "Select * from email where email='$email'";
$result = mysqli_query($con, $query);
$assoc = mysqli_fetch_assoc($result);//an associative array is fetched for better understanding
//as email is a unique field it will only fetch one 1D array
if($key = $assoc['activatecode']){
    $query = "update email set activatestatus='Y'";
    $update = mysqli_query($con, $query);
    if(!$update) echo mysqli_error ($con);
    else echo 'activation successful';
}
else echo 'Either the Email is wrong or the associated key is not with the email';
mysqli_close($con);

Now that we have coded, here's how to change the activatestatus of a given email. Just point your URL to
http://---path-to-your-activation-script--.php?email=john@example.com&key=564186451684865

You email will be activated.
Download this script!
More coding at Let's Code
Warning: This is for testing purposes only. I am giving you this tutorial as an advanced example on how to use several functions in PHP and running appropriate querys in MySQL using PHP. Do not publish!

How to run a MySQL query using PHP

Simple tutorial!
Prerequisite Knowledge: Basic PHP, and MySQL
Prerequisite Stuff: A server that can run both PHP and MySQL(here is a simple way to set up the same on your system)

Step 1: Create a MySQL conenction!

1. Define 3 string variables to define, the database host name, username and password
• $dbhost = 'localhost'; //enter your hostname under quotes;
• $dbuser='myuser'; //enter your database user name in the quotes;
• $dbpass='mypassword; //enter your database user password;
2. Create a MySQL connection using mysql_connect() function
• $connection = mysqli_connect($dbhost,$dbuser,$dbpass);
• if(!$connection) echo 'cannot connect to database'; //Checks whether connection is made

Step 2: Select the required database

1. Define a string variable with database name:
• $dbname='mydatabse' // Enter your name of database under quotes
2. Select the databse using mysql_select_db() function
• $select = mysqli_select_db($connection,$dbname) or die('cannot select database');
• The die function checks whether the database could be selected or not

Step 3: Lets run the query

1. Define a string variable for query.
• $query = 'SELECT * FROM table'; //put your MySQL query in quotes
2. Run the query using mysql_query() function.
• $query = mysql_query($query)
• We define the variable to get the result from MySQL.

Enjoy! Happy coding. Practice the code by understanding it. Yup, I did not include a sum up, understand and code it yourself! You can do it, trust me!

More coding at Let's Code!

Edit: Modified with mysqli extension.