Login System using PHP and MYSQL Database

In this lesson, we're going to learn how to create a fully functional login system in PHP including user registration, login, logout, forgot password and reset functionality. We're going to use MySQL to store registered users in a database, and implement sessions for knowing when the user has been logged in and logged out.

Below is a codepen we will be using for this tutorial which is a perfect match for what we're trying to do here, a complete registration and login form with Forgot Password link

Click on the following link to download all of this form's HTML, CSS and Javascript source code, we're going to need it for this tutorial: Download the Login System source code Make sure you download this file, and not the actual codepen, because I've included many extra files, images and code which you'll need to follow this lesson.

See the Pen Sign-Up/Login Form by Eric (@ehermanson) on CodePen.

Below is a chart showing how all the PHP files in the system are interacting with each other and what important action and display message each one does. I've colored most PHP files yellow, mailed PHP files are in blue, and form action file which is being called other than the file itself, which is the only one - reset_password.php is in red. All the display messages are colored green. Finally, the most important action a PHP file is responsible for is in gray.

There are three actions a user can take on the index.php page - login.php, register.php or forgot.php, each leading to other PHP pages with important actions and functions performed on each page. Hopefully you can see how all the pages are connected and it gives you a good picture of how it all works, I honestly was getting confused with the number of files myself while writing this and that's how the chart was born.

When you download the login-system.zip file, all the source code will be included, but if you prefer to type the code yourself, I've also included a folder called "new" which only contains HTML and CSS code, so you can finish typing PHP yourself by following this article. You can always refer back to the completed version to check your code and also glance back at the chart, also included in the folder called "img" to understand the big picture better.

The SQL code to create the database and accounts which we'll be working with is also included in a folder called "sql". Here is what it looks like:

CREATE DATABASE accounts; CREATE TABLE `accounts`.`users` ( `id` INT NOT NULL AUTO_INCREMENT, `first_name` VARCHAR(50) NOT NULL, `last_name` VARCHAR(50) NOT NULL, `email` VARCHAR(100) NOT NULL, `password` VARCHAR(100) NOT NULL, `hash` VARCHAR(32) NOT NULL, `active` BOOL NOT NULL DEFAULT 0, PRIMARY KEY (`id`) );

Also included is a sql_import.php, which is a PHP script to execute above SQL code, make sure you set your own $user and $password variables to connect to MySQL database:

//connection variables $host = 'localhost'; $user = ''; $password = ''; //create mysql connection $mysqli = new mysqli($host,$user,$password); if ($mysqli->connect_errno) { printf("Connection failed: %s\n", $mysqli->connect_error); die(); } //create the database if ( !$mysqli->query('CREATE DATABASE accounts') ) { printf("Errormessage: %s\n", $mysqli->error); } //create users table with all the fields $mysqli->query(' CREATE TABLE `accounts`.`users` ( `id` INT NOT NULL AUTO_INCREMENT, `first_name` VARCHAR(50) NOT NULL, `last_name` VARCHAR(50) NOT NULL, `email` VARCHAR(100) NOT NULL, `password` VARCHAR(100) NOT NULL, `hash` VARCHAR(32) NOT NULL, `active` BOOL NOT NULL DEFAULT 0, PRIMARY KEY (`id`) );') or die($mysqli->error);

With the SQL database and table created, we're now ready to start coding the login system. Let's begin with db.php file

/* Database connection settings */ $host = 'localhost'; $user = 'root'; $pass = 'mypass123'; $db = 'accounts'; $mysqli = new mysqli( $host, $user, $pass,$db ) or die($mysqli->error);

This file will be included, using PHP "require" construct on most pages, and it simply connects us to the 'accounts' MySQL database so we can work with it.

Before we jump to the main functionality of index.php, I just wanted to go over error.php and success.php very quickly. If you glance back at the chart, you can see that they're simply there for displaying success and error messages respectively. Let's look at error.php:

<?php /* Displays all error messages */ session_start(); ?> <!DOCTYPE html> <html> <head> <title>Error</title> <?php include 'css/css.html'; ?> </head> <body> <div class="form"> <h1>Error</h1> <p> <?php if( isset($_SESSION['message']) AND !empty($_SESSION['message']) ): echo $_SESSION['message']; else: header( "location: index.php" ); endif; ?> </p> <a href="index.php"><button class="button button-block"/>Home</button></a> </div> </body> </html>

As you can see, the only thing it does it prints out the message from the $_SESSION['message'] variable, which will be set on the previous page. We must first start the session by calling "session_start()" function so we have access to $_SESSION global variable. We then make sure that the variable is set with "isset()" and not empty "!empty()" functions before attempting to print it out. If the variable is not set, we redirect the user back to the "index.php" page with header() function.

The success.php contains exactly the same code with the exception of different title and header, so instead of error it's called success. Let's now move on to the main file: "index.php". Note that I've excluded all the HTML below <body> tag for brevity:

<?php /* Main page with two forms: sign up and log in */ require 'db.php'; session_start(); ?> <!DOCTYPE html> <html> <head> <title>Sign-Up/Login Form</title> <?php include 'css/css.html'; ?> </head> <?php if ($_SERVER['REQUEST_METHOD'] == 'POST') { if (isset($_POST['login'])) { //user logging in require 'login.php'; } elseif (isset($_POST['register'])) { //user registering require 'register.php'; } } ?> <body> <!-- register and login form code here -->

We check if the form is being submitted with method="post" by making sure the request_method of $_SERVER variable is equal to POST. We then check if the $_POST['login'] is set which is a variable of the login form, in that case we proceed to login.php by including the code with "require" keyword. Else if....$_POST['register'] variable is set, which is a variable of the register form, we proceed to register.php by including it's code

Here is the HTML part where $_POST['login'] or $_POST['register'] come from:

<button class="button button-block" name="login" />Log In</button> <button type="submit" class="button button-block" name="register" />Register</button>

Also, note the require 'db.php' and session_start() at the top this page. Since we're also including login.php and register.php from the same page, the inclusion of db.php and session_start() will also apply to any page which is being included from index.php, in this case login.php and register.php, so we won't have to repeat database inclusion and session start function on either pages.

Let's now look at register.php, below is the full code:

/* Registration process, inserts user info into the database and sends account confirmation email message */ // Set session variables to be used on profile.php page $_SESSION['email'] = $_POST['email']; $_SESSION['first_name'] = $_POST['firstname']; $_SESSION['last_name'] = $_POST['lastname']; // Escape all $_POST variables to protect against SQL injections $first_name = $mysqli->escape_string($_POST['firstname']); $last_name = $mysqli->escape_string($_POST['lastname']); $email = $mysqli->escape_string($_POST['email']); $password = $mysqli->escape_string( password_hash($_POST['password'], PASSWORD_BCRYPT) ); $hash = $mysqli->escape_string( md5( rand(0,1000) ) ); // Check if user with that email already exists $result = $mysqli->query("SELECT * FROM users WHERE email='$email'") or die($mysqli->error); // We know user email exists if the rows returned are more than 0 if ( $result->num_rows > 0 ) { $_SESSION['message'] = 'User with this email already exists!'; header("location: error.php"); } else { // Email doesn't already exist in a database, proceed... // active is 0 by DEFAULT (no need to include it here) $sql = "INSERT INTO users (first_name, last_name, email, password, hash) " . "VALUES ('$first_name','$last_name','$email','$password', '$hash')"; // Add user to the database if ( $mysqli->query($sql) ){ $_SESSION['active'] = 0; //0 until user activates their account with verify.php $_SESSION['logged_in'] = true; // So we know the user has logged in $_SESSION['message'] = "Confirmation link has been sent to $email, please verify your account by clicking on the link in the message!"; // Send registration confirmation link (verify.php) $to = $email; $subject = 'Account Verification ( clevertechie.com )'; $message_body = ' Hello '.$first_name.', Thank you for signing up! Please click this link to activate your account: http://localhost/login-system/verify.php?email='.$email.'&hash='.$hash; mail( $to, $subject, $message_body ); header("location: profile.php"); } else { $_SESSION['message'] = 'Registration failed!'; header("location: error.php"); } }

We set some session variables which we'll use to welcome the user on the profile.php which is where register.php will redirect on a successful register. We then prepare all the $_POST variables by applying $mysqli->escape_string() function to protect again SQL injections. Let's take a closer look at the following two lines which create secure password hash and generate a unique hash string:

$password = $mysqli->escape_string( password_hash($_POST['password'], PASSWORD_BCRYPT) ); $hash = $mysqli->escape_string( md5( rand(0,1000) ) );

For the $password I have used the built-in PHP function password_hash() which takes in two parameters, the first is the raw password provided by the user and the second is the encryption algorithm constant, in this case - PASSWORD_BCRYPT.

To generate unique hash string, we simply use the rand() function which will generate a random number from 0 to 1000, and then we use md5() function to generate a unique hash from the random number. If we printed out $password and $hash at this point, they would look something like this:

$password = $mysqli->escape_string( password_hash($_POST['password'], PASSWORD_BCRYPT) ); echo $password; //output: $2y$10$PXzvWecpHeXEW.CremYvreh2/4rDdCIlGFsNtxQbigAcCC4HgtbuW $hash = $mysqli->escape_string( md5( rand(0,1000) ) ); echo $hash; //output: 847cc55b7032108eee6dd897f3bca8a5 die;

We then check, if the user with the entered email already exists in the database before proceeding, if they do, we redirect to error.php page. If you recall, whenever we run "SELECT" statement in a PHP SQL query, we get the result object returned, so it makes sense to call the variable $result. Here is what the object would look like if we used var_dump() function on it:

var_dump( $result ); //output: object(mysqli_result)#2 (5) { ["current_field"]=> int(0) ["field_count"]=> int(7) ["lengths"]=> NULL ["num_rows"]=> int(0) ["type"]=> int(0) }

As you can see there is "num_rows" property, which would be equal to 1 if the user with the email already existed in the database, that's how we find out if the user exists by running the following if statement:

// We know user email exists if the rows returned are more than 0 if ( $result->num_rows > 0 ) { $_SESSION['message'] = 'User with this email already exists!'; header("location: error.php"); }

Else...if the user doesn't exist, we proceed by first preparing the SQL insert statement with all our previously set variables:

// active is 0 by DEFAULT (no need to include it here) $sql = "INSERT INTO users (first_name, last_name, email, password, hash) " . "VALUES ('$first_name','$last_name','$email','$password', '$hash')";

We then check if the mysql->query() is successful, if it is, we know the user has been added to the database. Next we set some session variables to be used on the profile.php page

$_SESSION['active'] = 0; //0 until user activates their account with verify.php $_SESSION['logged_in'] = true; // So we know the user has logged in $_SESSION['message'] = "Confirmation link has been sent to $email, please verify your account by clicking on the link in the message!";

We know the account won't be activated when a user first registers, so we can safely set $_SESSION['active'] to zero. We set the session logged_in to true, so we know the user has logged in and finally the message to display that the account activation link has been sent.

The final step, is to send the user an email with the account activation link:

// Send registration confirmation link (verify.php) $to = $email; $subject = 'Account Verification ( clevertechie.com )'; $message_body = ' Hello '.$first_name.', Thank you for signing up! Please click this link to activate your account: http://localhost/login-system/verify.php?email='.$email.'&hash='.$hash; mail( $to, $subject, $message_body ); //redirect to profile.php page header("location: profile.php");

The PHP mail() function takes in three parameters - $to (user email where to send the message), $subject (email subject) and $message_body (the main body of the email message).

The most important part of the verification message if the following URL which sends a user to verify.php with email and hash variables: http://localhost/login-system/verify.php?email='.$email.'&hash='.$hash;

By passing email=$email&hash=$hash variables in this way, we'll be able to access them from verify.php from the $_GET global PHP varible and match the user email with their unique hash, so we can verify their account. Let's take a look at verify.php next:

/* Verifies registered user email, the link to this page is included in the register.php email message */ require 'db.php'; session_start(); // Make sure email and hash variables aren't empty if(isset($_GET['email']) && !empty($_GET['email']) AND isset($_GET['hash']) && !empty($_GET['hash'])) { $email = $mysqli->escape_string($_GET['email']); $hash = $mysqli->escape_string($_GET['hash']); // Select user with matching email and hash, who hasn't verified their account yet (active = 0) $result = $mysqli->query("SELECT * FROM users WHERE email='$email' AND hash='$hash' AND active='0'"); if ( $result->num_rows == 0 ) { $_SESSION['message'] = "Account has already been activated or the URL is invalid!"; header("location: error.php"); } else { $_SESSION['message'] = "Your account has been activated!"; // Set the user status to active (active = 1) $mysqli->query("UPDATE users SET active='1' WHERE email='$email'") or die($mysqli->error); $_SESSION['active'] = 1; header("location: success.php"); } } else { $_SESSION['message'] = "Invalid parameters provided for account verification!"; header("location: error.php"); }

We first make sure the variables that we passed in the verify.php URL (email and hash) aren't empty and have values:

// Make sure email and hash variables aren't empty if(isset($_GET['email']) && !empty($_GET['email']) AND isset($_GET['hash']) && !empty($_GET['hash']))

We then escape $email and $hash variables as usual, to protect again SQL injections. Then we run a mysqli query which selects user email and hash with active status zero:

// Select user with matching email and hash, who hasn't verified their account yet (active = 0) $result = $mysqli->query("SELECT * FROM users WHERE email='$email' AND hash='$hash' AND active='0'");

By selecting user email and their uniquely generated hash, we know the appropriate user is verifying their own account. Next, we check if the num_rows property is equal to zero, if it is we redirect to error.php page with an error message, else we continue verifying the user

if ( $result->num_rows == 0 ) { $_SESSION['message'] = "Account has already been activated or the URL is invalid!"; header("location: error.php"); } else { $_SESSION['message'] = "Your account has been activated!";

If everything went well and the user has been found with matching email and hash, we proceed to next MySQL statement which sets the value of user to 1 (active = 1) WHERE email=$email. We also set the session 'active' variable to 1, so that the "unverified account message" is removed from user's profile right away.

// Set the user status to active (active = 1) $mysqli->query("UPDATE users SET active='1' WHERE email='$email'") or die($mysqli->error); $_SESSION['active'] = 1; //show successful verification message header("location: success.php");

This completes account registration and verification process, let's now move on to the login.php part

/* User login process, checks if user exists and password is correct */ // Escape email to protect against SQL injections $email = $mysqli->escape_string($_POST['email']); $result = $mysqli->query("SELECT * FROM users WHERE email='$email'"); if ( $result->num_rows == 0 ){ // User doesn't exist $_SESSION['message'] = "User with that email doesn't exist!"; header("location: error.php"); } else { // User exists $user = $result->fetch_assoc(); if ( password_verify($_POST['password'], $user['password']) ) { $_SESSION['email'] = $user['email']; $_SESSION['first_name'] = $user['first_name']; $_SESSION['last_name'] = $user['last_name']; $_SESSION['active'] = $user['active']; // This is how we'll know the user is logged in $_SESSION['logged_in'] = true; header("location: profile.php"); } else { $_SESSION['message'] = "You have entered wrong password, try again!"; header("location: error.php"); } }

The first step is to check if the user exists in the database with that email, so we run a simple SQL query and select the user based on the email provided:

$result = $mysqli->query("SELECT * FROM users WHERE email='$email'"); if ( $result->num_rows == 0 ){ // User doesn't exist $_SESSION['message'] = "User with that email doesn't exist!"; header("location: error.php"); }

If the user with provided email is found, we store the result in the $user array, if we used print_r() function on $user array at this point, it would look something like the following:

$user = $result->fetch_assoc(); print_r($user); die; /*output Array ( [id] => 1 [first_name] => Clever [last_name] => Techie [email] => shustikov@gmail.com [password] => $2y$10$4UOoPPUJbqx.eK83UQTXY.KNrm1xepeBq0.Q4WbBlyPuDF8DdYwOa [hash] => 54a367d629152b720749e187b3eaa11b [active] => 1 ) */

As you can see our user data is neatly organized and we can now access all of the current user's data from the $user array

We then use PHP built-in function password_verify() to make sure the password entered and the current user password which exists in the database match each other:

if ( password_verify($_POST['password'], $user['password']) )

If the two passwords match, we log the user in, by setting session variables which we'll need on the next page and redirect the user to profile.php welcome page

$_SESSION['email'] = $user['email']; $_SESSION['first_name'] = $user['first_name']; $_SESSION['last_name'] = $user['last_name']; $_SESSION['active'] = $user['active']; // This is how we'll know the user is logged in $_SESSION['logged_in'] = true; header("location: profile.php");

We have used all of our values from the $user array which we looked at previously to set a lot of our session variables to display on the profile page. Also, $_SESSION['logged_in'] is set to true so that we know the user is in fact logged in. The login process is now complete. Finally, let's take a look at forgot.php to see how password reset process works:

/* Reset your password form, sends reset.php password link */ require 'db.php'; session_start(); // Check if form submitted with method="post" if ( $_SERVER['REQUEST_METHOD'] == 'POST' ) { $email = $mysqli->escape_string($_POST['email']); $result = $mysqli->query("SELECT * FROM users WHERE email='$email'"); if ( $result->num_rows == 0 ) // User doesn't exist { $_SESSION['message'] = "User with that email doesn't exist!"; header("location: error.php"); } else { // User exists (num_rows != 0) $user = $result->fetch_assoc(); // $user becomes array with user data $email = $user['email']; $hash = $user['hash']; $first_name = $user['first_name']; // Session message to display on success.php $_SESSION['message'] = "

Please check your email $email" . " for a confirmation link to complete your password reset!

"; // Send registration confirmation link (reset.php) $to = $email; $subject = 'Password Reset Link ( clevertechie.com )'; $message_body = ' Hello '.$first_name.', You have requested password reset! Please click this link to reset your password: http://localhost/login-system/reset.php?email='.$email.'&hash='.$hash; mail($to, $subject, $message_body); header("location: success.php"); } }

The code should look familiar at this point. We first check if the form is being submitted with POST method. We then check if the user exists by running a simple SQL statement which selects user email, based on the email entered in the form. If the user exists, we store data in the $user array. We also set the $_SESSION['message'] which will display on success.php page. Finally, we send the user a message which contains reset.php link with email and hash variables:

// Session message to display on success.php $_SESSION['message'] = "

Please check your email $email" . " for a confirmation link to complete your password reset!

"; // Send registration confirmation link (reset.php) $to = $email; $subject = 'Password Reset Link ( clevertechie.com )'; $message_body = ' Hello '.$first_name.', You have requested password reset! Please click this link to reset your password: http://localhost/login-system/reset.php?email='.$email.'&hash='.$hash; mail($to, $subject, $message_body); header("location: success.php");

When a user clicks on the URL with reset.php, they land on reset.php page with their email and hash variables set, we're doing this in exactly the same way we have verified account, so this should be a review. Let's now look at reset.php code:

/* The password reset form, the link to this page is included from the forgot.php email message */ require 'db.php'; session_start(); // Make sure email and hash variables aren't empty if( isset($_GET['email']) && !empty($_GET['email']) AND isset($_GET['hash']) && !empty($_GET['hash']) ) { $email = $mysqli->escape_string($_GET['email']); $hash = $mysqli->escape_string($_GET['hash']); // Make sure user email with matching hash exist $result = $mysqli->query("SELECT * FROM users WHERE email='$email' AND hash='$hash'"); if ( $result->num_rows == 0 ) { $_SESSION['message'] = "You have entered invalid URL for password reset!"; header("location: error.php"); } } else { $_SESSION['message'] = "Sorry, verification failed, try again!"; header("location: error.php"); }

There is nothing new in this code, so I'm not going to go over the details. However, let's look at a couple of HTML lines of reset.php page, because there are a few important things going on

<form action="reset_password.php" method="post">

As you can see we're calling another page called "reset_password.php" which will complete the reset process. We need to do this because the current page already checks for $_GET variables which are transferred differently then $_POST variables which we'll need on the next page.

Also, the following two hidden input fields have been included, so we can access user email and hash on the reset_password.php page. This let's us know which user is resetting their password.

<!-- This input field is needed, to get the email of the user --> <input type="hidden" name="email" value="<?= $email ?>"> <input type="hidden" name="hash" value="<?= $hash ?>">

Finally, let's examine "reset_password.php" which will conclude the password reset process

/* Password reset process, updates database with new user password */ require 'db.php'; session_start(); // Make sure the form is being submitted with method="post" if ($_SERVER['REQUEST_METHOD'] == 'POST') { // Make sure the two passwords match if ( $_POST['newpassword'] == $_POST['confirmpassword'] ) { $new_password = password_hash($_POST['newpassword'], PASSWORD_BCRYPT); // We get $_POST['email'] from the hidden input field of reset.php form $email = $mysqli->escape_string($_POST['email']); $hash = $mysqli->escape_string($_POST['hash']); $sql = "UPDATE users SET password='$new_password' WHERE email='$email' AND" . "hash='$hash'"; if ( $mysqli->query($sql) ) { $_SESSION['message'] = "Your password has been reset successfully!"; header("location: success.php"); } } else { $_SESSION['message'] = "Two passwords you entered don't match, try again!"; header("location: error.php"); } }

If the form is submitted with the post, we check to make sure the new password and confirm password match, we then access the hidden input fields so we know what email and hash to select

// We get $_POST['email'] and $_POST['hash'] from the hidden input field of reset.php form $email = $mysqli->escape_string($_POST['email']); $hash = $mysqli->escape_string($_POST['hash']);

Lastly, we run the SQL query and update user password to new one and redirect them to success.php page with the appropriate message

$sql = "UPDATE users SET password='$new_password' WHERE email='$email' AND" . "hash='$hash'"; if ( $mysqli->query($sql) ) { $_SESSION['message'] = "Your password has been reset successfully!"; header("location: success.php"); }

You did it! This was a long tutorial and I hope you learned a lot from it, if at any point you get confused, go back and look at the chart so you can see the big picture of how everything is put together and where important things are happening. Please post any questions, comments and concerns below.