Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

PHP/MySQL User Authentication

Published:
Updated:
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to use!  What is deprecated? Well in english it means "to strongly disapprove of." In software, features become deprecated because new features supersede them. The reason for deprecation instead of removal is to provide backwards compatability so programmers can update their code (which doesn't mean it happens!) although deprecated function may be removed completely in future version. So we must learn to cease and desist the use of deprecated functions.

Keeping in mind that so many tutorials and books sport deprecated functions and classes, this tutorial is aimed at the beginner and intermediate user alike. The purpose of this tutorial is to teach you to create a basic user authentication system without relying on deprecated functions, or losing security. I find it absolutely boggling how many horrible tutorials are out there, and their impact is seen on a daily basis on forums all over. The main points that I think make this tutorial better than the other is that its written procedurally (not daunting objects to learn to use), it uses the MySQLi extension (the i stands for improved), and it doesn't skip simple security that every script should use. Why learn old, discontinued functions, bad practice, and weird syntax when you can learn it right the first time!

A few notes ahead of time; code snippets are incomplete on their own, to obtain the final files (with ample commenting througout) download the attached zip. Links to all the functions used in this code are provided at the bottom of the tutorial. If you don't understand how or why I used a function, my experience has taught me that the manual is by far the best place to start, and rather intuitive to use: just type in php.net/ followed by the function name (or close) that you are looking for; For example: php.net/echo which brings you to the manual page for the echo language construct. And last but not least, this tutorial assumes enough familiarity with either phpMyAdmin, MySQL Workbench, or the MySQL console to connect to your database.

Set-Up: Step 1 -  Create The User Table

So let's start by creating a very simple user table. To do this you'll need to connect to mysql with a tool such as the MySQL Workbench, phpMyAdmin or MySQL console.
In phpMyAdmin - select the database, click the SQL tab and copy paste the query into the box then click go.
In MySQL Workbench, connect to the server, select your database (you may have to right click the db and select "Set as default schema" to get it to work), copy paste the query into main entry spot in the middle of the window.
In the console you would type use DATABASE; where DATABASE is the name of your database. Then hit enter, then type in the query and hit enter again.
CREATE TABLE IF NOT EXISTS `users` (
                         `id` INT unsigned NOT NULL AUTO_INCREMENT,
                         `username` varchar(20) NOT NULL,
                         `email` varchar(75) NOT NULL,
                         `password` char(64) NOT NULL,
                         PRIMARY KEY (`id`),
                         UNIQUE(`username`),
                         UNIQUE(`email`)
                      ) Engine=MyISAM;

Open in new window


Set-Up: Step 2 - MySQL Log In Credentials for PHP

Now that we have our table, we need to store our MySQL credentials so PHP we can connect to the server from within PHP. Here we use define to create constants because our login credentials won't change during the execution of the script, and because they will then be available in any scope. For more information about scope see Variable Scope. We also put this definition in its own file, instead of in each script so as to make it easier to update credentials if need be. We will just include this file in any file that needs to connect to MySQL.
<?php //mysql_info.php
                      
                      define(  'DBHOST',   'localhost');
                      define(  'DBUSER',   'derokorian');
                      define(  'DBPASS',   'derokorian');
                      define(  'DBNAME',   'derokorian'); ?>

Open in new window


Registration: Step 1 - Create Skeleton Script

Now we can start building the first script: the registration system; because if people can't register than they won't have an account to log in to! We'll start by thinking about what needs to happen for this to work. Well we need a form for the user to fill out, we'll need to check if the form was submitted, we'll need to verify the accuracy of what was submitted, we'll need to add the user to the database, and finally we'll need to be able to handle any errors and report back to the user. Now it may not make sense right now but the form will go at the bottom of the script along with all the other output, but we'll make it first so we have our form fields defined. So lets start by making a skeleton script with the steps we know we need as comments:
<?php //userRegister.php
                      // Check if the form was submitted
                      
                      // check that all fields are filled in
                      
                      // check the validity of the entered data
                      
                      // add user to the database
                      
                      // show errors
                      
                      // show form

Open in new window


Registration: Step 2 - Create the Form

As you can see there is no code yet, but we will add it one step at a time. First let's create our form, we will add more to this later to make it more robust but lets start with something simple.
// show form
                      ?>
                      <!DOCTYPE html>
                      <html>
                         <head>
                            <title>Derokorian User Registration</title>
                         </head>
                         <body>
                            <form action="" method="post">
                               <label for="username">Username:</label><input type="text" name="username" value=""/><br />
                               <label for="email">Email:</label><input type="text" name="email" value=""/><br />
                               <label for="pass1">Password:</label><input type="password" name="pass1" /><br />
                               <label for="pass2">Confirm Pass:</label><input type="password" name="pass2" /><br />
                               <input type="submit" value="Register" />
                            </form>
                         </body>
                      </html>

Open in new window


Registration: Step 3 - Check for Form Submission

Next lets check if the form was submitted using count on the $_POST array to check if it contains anything. We will move the comments of steps to take if the form was submitted to inside this block:
// Check if the form was submitted
                      if( count($_POST) > 0 ) {
                      	// check that all fields are filled in
                      
                      	// check the validity of the entered data
                      	
                      	// add user to the database
                      }

Open in new window


Registration: Step 4 - Checked Required Fields

Next we will check that each field is filled in. We are just trying to find the missing fields for now, we will output those errors later, so for now we will create an empty $errors array to add an error message to if a field is empty. The process is the same for every required field. For this tutorial all fields will be required. We check that the field was submitted and contains data with empty :
// check that all fields are filled in, one at a time using empty()
                      if( empty($_POST['username']) ) {
                      	$errors[] = 'You must supply a username.';
                      }
                      if( empty($_POST['email']) ) {
                      	$errors[] = 'You must supply an email.';
                      }
                      if( empty($_POST['pass1']) ) {
                      	$errors[] = 'You must supply a password.';
                      }
                      if( empty($_POST['pass2']) ) {
                      	$errors[] = 'You must confirm your password.';
                      }

Open in new window


Registration: Step 5 - Validating Input

So now the next step in our skeleton is to check the validity of what the user entered but there is no point in checking what the user entered if they forgot to enter something, so first we'll check if there are already errors using count. If there aren't then we check the fields. We will check the email using filter_var with the FILTER_VALIDATE_EMAIL option. We will check the username to begin with a letter and contain only letters and numbers with a simple regular expression and preg_match if you don't understand regular expressions don't worry. And lastly we'll make sure that both passwords provided match using the == comparison operator:
// If there are not already errors from missing fields
                      if( count($errors) == 0 ) {
                      	// check the validity of the entered data
                      	// Check the username
                      	if( !preg_match('/^[a-z][a-z0-9]+$/i',$_POST['username']) ) {
                      		$errors[] = 'Your username must begin with a letter, and only contain letters and numbers.';
                      	}
                      	// Check the email
                      	if( !filter_var($_POST['email'],FILTER_VALIDATE_EMAIL) ) {
                      		$errors[] = 'You must supply a valid email.';
                      	}
                      	// Check the passwords
                      	if( $_POST['pass1'] != $_POST['pass2'] ) {
                      		$errors[] = 'You must supply matching passwords.';
                      	}
                      	
                      	// add user to the database
                      }

Open in new window


Registration: Step 6 - Connect to DB and Prepare Data for Insertion

Next we need to prepare data for entry in the database, however once again we will check if there are errors before continuing. We will need to connect to MySQL with mysqli_connect now to be able to use mysqli_real_escape_string on the username and email to prevent SQL Injection. After trying to connect we will need to check if it connected successfully if not give a fatal error. Finally we will use one way hashing on the password make it harder for a hacker to get the user's password, should that hacker gain access to our database (and to protect the user from us knowing the password). To hash the password we will use hash with the SHA256 algorith, and we will do what is called salting by concatenating the username to the password (after we make the username all lowercase, so its not case dependant).
// Check for errors in validity
                      if( count($errors) == 0 ) {
                      	// connect to mysql
                      	include 'mysql_info.php';
                      	$conn = mysqli_connect(DBHOST,DBUSER,DBPASS,DBNAME);
                      	
                      	// Check if the connection failed
                      	if( !$conn ) {
                      		//connection failed
                      		die('Failed to connect '.mysqli_connect_error());
                      	}
                      	
                      	// prepare data for database
                      	$username = mysqli_real_escape_string($conn,$_POST['username']);
                      	$email = mysqli_real_escape_string($conn,$_POST['email']);
                      	$pass = hash('sha256',strtolower($_POST['username']).$_POST['pass1']);
                      }

Open in new window


Registration: Step 7 - Creating and Executing the Query

Now our data is ready to go into the database! Lets try to insert it now, remember we must always check if the query was successful. Here I use die on success because the user won't need the form any longer, I added a link in the output of die to the login page however. If the query failed, we return the error code and error message that MySQL gave php using mysqli_errno and mysqli_error, respectively. Note that our table will reject a username and email that have already registered; though we aren't handling this problem in our current script as its beyond the scope of the tutorial. The error from MySQL that we output will show that the username and/or email is already registered, but this should be handled more gracefully in your application.
// Create the insert query
                      $sql = sprintf("INSERT INTO `users` (`username`,`email`,`password`) VALUES ('%s','%s','%s')",
                      		$username,$email,$pass);
                      
                      // Attempt insert the new user
                      if( mysqli_query($conn,$query) ) {
                      	die('You have successfully registered as '.$_POST['username'].'<br /><a href="/userLogin.php">Click here</a> to log in.');
                      } else {
                      	// Insert failed, set error message
                      	$errors[] = 'Error adding user to database, MySQL said:<br>
                      		('.mysqli_errno($conn).') '.mysqli_error($conn).'</span>';
                      }

Open in new window


Registration: Step 8 - Update Form to Display Errors and/or Previously Submitted Input

Finally we need to update the form, to show previously submitted data if it exists, and the errors contained in our $errors array (if any exist). We will use isset to check if a value was previously submitted, then we use isset and count to check if errors exist. If any errors exist, we put them inside a span that changes the text color to red, and then implode all the errors using <br> as glue so they each appear on their own line:
//output the form
                      ?>
                      <!DOCTYPE html>
                      <html>
                         <head>
                            <title>Derokorian User Registration</title>
                         </head>
                         <body>
                            <?php echo isset($errors) && count($errors) > 0 ? '<span style="color:red">'.implode('<br>',$errors).'</span><br>' : ''; ?>
                            <form action="" method="post">
                               <label for="username">Username:</label><input type="text" name="username" value="<?php echo isset($_POST['username']) ? $_POST['username'] : ''; ?>"/><br>
                               <label for="email">Email:</label><input type="text" name="email" value="<?php echo isset($_POST['email']) ? $_POST['email'] : ''; ?>"/><br>
                               <label for="pass1">Password:</label><input type="password" name="pass1" /><br>
                               <label for="pass2">Confirm Pass:</label><input type="password" name="pass2" /><br>
                               <input type="submit" value="Register" />
                            </form>
                         </body>
                      </html>

Open in new window


That's it our user registration is complete! (Don't forget the full version, properly nested, formatted, and commented is in the zip at the end of this tutorial).

Authentication: Step 1 - Create Skeleton Script

Now we need to start working on our login script. We are going to use a  session to track if a user is logged in so and we will need to check if they are already logged in. Next we will need to create a form, check if the form was submitted, check that requried fields are filled in, check the username / password combination against the database, keep track of errors a long the way, and return feedback about successful / failed log in. We will start with a skeleton script again:
<?php //userLogin.php
                      // Start the session
                      
                      // check if the user is logged in
                      
                      // check if the form was submitted
                      
                      // verify fields are filled in
                      
                      // check the given username / password against the database
                      
                      // tell the user they are logged in, if they are
                      
                      // tell them why they aren't logged in, if they tried, and show the form

Open in new window


Authentication: Step 2 - Start Session and Check for Login

Let's start with the first 2 comment steps we just made. We will call session_start to get a session ID (Note: session_start MUST be called before any output, including (but not limited to) whitespace outside of php tags.) then we will use isset to check if the user is logged in:
// Start the session
                      session_start();
                      
                      // check if the user is logged in
                      if( isset($_SESSION['username']) ) {
                      	die('You are already logged in');
                      }

Open in new window


Authentication: Step 3 - Create the Form

Now we'll create our form. This is a very simple form with only two input fields.
// Output the form
                      ?>
                      <!DOCTYPE html>
                      <html>
                         <head>
                            <title>Derokorian User Login</title>
                         </head>
                         <body>
                            <form action="" method="post">
                               <label for="username">Username:</label><input type="text" name="username" value=""/><br>
                               <label for="password">Password:</label><input type="password" name="password" /><br>
                               <input type="submit" value="Log in" />
                            </form>
                         </body>
                      </html>

Open in new window


Authentication: Step 4 - Check for Form Submission

Again we will use count to check if the form was already submitted:
// check if the form was submitted
                      if( count($_POST) > 0 ) {
                      	// verify fields are filled in
                      
                      	// check the given username / password against the database
                      	
                      	// tell the user they are logged in, if they are
                      }

Open in new window


Authentication: Step 5 - Check Required Fields and Prepare Data for Query

Next we will check that the fields are filled in, and prepare the data for the database. We will do this again using empty to check if fields are filled in, connecting to mysql with mysqli_connect, showing connection errors with mysqli_connect_error and using mysqli_real_escape_string to prepare the username, and using hash to encode the password with lowercase username. Finally using count again to check if there are errors along the way:
// verify fields are filled in
                      $errors = array();
                      if( empty($_POST['username']) ) {
                      	$errors[] = 'You must enter a username.';
                      }
                      if( empty($_POST['password']) ) {
                      	$errors[] = 'You must enter your password.';
                      }
                      
                      if( count($errors) == 0 ) {
                      	// Connect to mysql
                      	include 'mysql_info.php';
                      	$conn = mysqli_connect(DBHOST,DBUSER,DBPASS,DBNAME);
                      	
                      	// Check if the connection failed
                      	if( !$conn ) {
                      		//connection failed
                      		die('Failed to connect '.mysqli_connect_error());
                      	}
                      	
                      	// prepare data for database
                      	$username = mysqli_real_escape_string($conn,$_POST['username']);
                      	$pass = hash('sha256',strtolower($_POST['username']).$_POST['password']);
                      }

Open in new window


Authentication: Step 6 - Create and Execute Query

Now we must build the query to check the database, if the username/password combo is found they have the right login credentials and we need to add that info to the $_SESSION array. If not we need to tell them their username/password combination is a mismatch.
// Build the query
                      $sql = sprintf("SELECT 1 FROM `users` WHERE `username` = '%s' AND `password` = '%s'",$username,$pass);
                      
                      // check the given username / password against the database
                      $res = mysqli_query($conn,$sql);
                      
                      // Check if the query was successful
                      if( !$res ) {
                      	$errors[] = 'Error selecting user from database, MySQL said:<br>
                               ('.mysqli_errno($conn).') '.mysqli_error($conn);
                      } else {
                      	// Check if the result returned a row
                      	if( mysqli_num_rows($res) > 1 ) {
                      		// Successfully logged in
                      		$_SESSION['username'] = $_POST['username'];
                      		die('You have successfully logged in.');
                      	} else {
                      		// Username/password mismatch
                      		$errors[] = 'Your username and password combination wasn\'t found. Please try again.';
                      	}
                      }

Open in new window


Authentication: Step 7 - Update Form to Display Errors and/or Previously Submitted Input

Last step in the log in script, is to create the form, output errors and give a default value to the username if the form was already posted. This is the same process as with the registration form.
// Output the form
                      ?>
                      <!DOCTYPE html>
                      <html>
                         <head>
                            <title>Derokorian User Login</title>
                         </head>
                         <body>
                            <?php echo isset($errors) && count($errors) > 0 ? '<span style="color:red">'.implode('<br>',$errors).'</span><br>' : ''; ?>
                            <form action="" method="post">
                               <label for="username">Username:</label><input type="text" name="username" value="<?php echo isset($_POST['username']) ? $_POST['username'] : ''; ?>"/><br>
                               <label for="password">Password:</label><input type="password" name="password" /><br>
                               <input type="submit" value="Log in" />
                            </form>
                         </body>
                      </html>

Open in new window



That's it for our user log in script! (Don't forget the full version, properly nested, formatted, and commented is in the zip at the end of this tutorial).

Authentication: Logging Out

Last but not least a way to log out is necessary, this is as simple call session_destroy.
<?php //userLogout.php
                      
                      session_destroy();
                      echo 'You have been logged out.';
                      
                      ?>

Open in new window


That's it! Hopefully now you understand the process of registering and authenticating users better and that you have the tools and knowledge to do it properly!

External Links

MANUAL PAGES:
http://php.net/define
http://php.net/isset
http://php.net/empty
http://php.net/count
http://php.net/implode
http://php.net/filter_var
http://php.net/operators.comparison
http://php.net/sprintf
http://php.net/hash
http://php.net/strtolower
http://php.net/session_start
http://php.net/session_destroy

MYSQLI PAGES
http://php.net/mysqli_connect
http://php.net/mysqli_connect_error
http://php.net/mysqli_real_escape_string
http://php.net/mysqli_query
http://php.net/mysqli_errno
http://php.net/mysqli_error
userLogin.php
mysql-info.php
CreateTable-Users.sql
userRegister.php
PHP-MySQL-User-Authentication.zip
7
7,744 Views

Comments (6)

Jason C. LevineDon't talk to me.
CERTIFIED EXPERT

Commented:
I agree.  This is a really good, easy-to-follow introduction to PHP Authentication.  Nice work!

Author

Commented:
Thank you both! I'm very happy to know that it's helpful to you.
Any thoughts (or tutorials) on forgotten or changing passwords? Most that I finding tend to be based on a particular login or registration script.

Author

Commented:
Any thoughts (or tutorials) on forgotten or changing passwords? Most that I finding tend to be based on a particular login or registration script.
Ah no short notes I can give, however I will be off for half of next week so I'll put a new article together about it.
Derokorian, once the user is logged in, how would I redirect them to the next page?  I'm trying to use this tutorial to update my old MySQL code with MySQLi and it looks like everything works except I can't figure out how to redirect to a page named "memberHome.php" after successful login...

View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.