PHP & MySQL Login System Tutorial

A login system cannot go without a MySQL Database with a table containing all users. Therefore we'll first setup the MySQL Database Table which we'll call "users" and which will be holding all user accounts that can be logged in into. The MySQL table can be created in any database you like, however in this tutorial we assume the database is called 'Webcodez'.

SQL Code

CREATE TABLE IF NOT EXISTS `users` (
`id` int(250) NOT NULL AUTO_INCREMENT,
`username` varchar(25) NOT NULL,
`password` varchar(250) NOT NULL,
`email` varchar(250) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO `users` (`id`, `username`, `password`, `email`) VALUES
(1, 'admin', '21232f297a57a5a743894a0e4a801fc3', 'admin@admin.com');

The SQL already inserts one user ( for testing purpose ) into the database table. The password (= 'admin' ) is encrypted in md5 format for security reasons ( otherwise the real password can be retrieved from the database ). To encrypt a string to md5 format we can simply use the PHP built-in function md5($str), easy as that is. So for our login system we do not need to know the real password as we can simply encrypt the given password by the user into md5 format as well and then check it with our database.

Now that we've created our mysql database table, we can start creating the script. First we'll have to create a login form, where the user can fill in his username and password in order to login.

login.php

<form method="POST" action="login.php">
<b>Username:</b> <br /> <input type="text" name="username"> <p>
<b>Password:</b> <br /> <input type="password" name="password"> <p>
<input type="submit" name="submitLogin" value="Login!">
</form>

Note: We use the POST method to submit the form data. If you do not know about the POST method, it's recommended that you first read this tutorial about POST variables in PHP.

Now that we have created the form, we need to handle the user's data input upon submission of the form. We can check whether the form was submitted by checking whether the POST array was created, or more precisely: whether $_POST['submitLogin'] is set. If so: then the form was submitted through the POST method and we'll want to verify the login. Otherwise: the form hasn't been submitted yet and we'll want to display the form.

<?php

if(isset($_POST['submitLogin'])) { //form submitted?

//verify login from user input

}else{

//show login form
?>

<form method="POST" action="login.php">
<b>Username:</b> <br /> <input type="text" name="username"> <p>
<b>Password:</b> <br /> <input type="password" name="password"> <p>
<input type="submit" name="submitLogin" value="Login!">
</form>

<?php

}

?>

To handle login attempt, we'll first catch the username and password provided (=value of input fields 'username' and 'password' => stored in $_POST['username'] and $_POST['password']).

$username = mysql_real_escape_string($_POST['username']);
$password = md5(mysql_real_escape_string($_POST['password']));

We store them into the variable $username and $password. We do apply the function mysql_real_escape_string on them to make sure all possible SQL injections are removed/escaped out of the string. Do always secure user input because if you do not, you risk your database and website of being harmed. Also we use the md5 function to encrypt the password, as we also encrypted them inside of the database (and that's what we're going to compare this password against, so it needs to be encrypted the same way to be able to compare).

Now we need to create a mysql_query that attempts to select the user from the database ( with username = $username and password = $password , as given/input by the user ). Then we need to count the amount of rows found by this query ( if any ) and this way we can verify whether there is a user with the given username and password and thus whether it's a valid login. We do this within the mysql query using the COUNT statement and we call the amount of results found 'amount'.

$select_user = mysql_query("SELECT COUNT(id) AS amount FROM users WHERE username = '$username' AND password = '$password' ");
$user = mysql_fetch_assoc($select_user);
$amount_found = (int)$user['amount']; //amount of users found by the query

We use mysql_fetch_assoc to catch the results found through the mysql query ran inside of $select_user.

All we need to do next is check whether $amount_found is greater than 0. Or in other words: whether any users were found that matched the given username and password as provided by the user. If so: then it's a valid login, otherwise it's an invalid login ( no user matched the login username and password combination provided ). For a valid login we create a session variable $_SESSION['loggedin'] indicating that the user is logged in, and a session variable $_SESSION['username'] holding the username of the user logged in.

if($amount_found > 0) {
echo "Successfully logged in!";
$_SESSION['loggedin'] = TRUE;
$_SESSION['username'] = $username;
}else{
echo "Invalid login! Click <a href='login.php'>here</a> to try again.";
}

Now let's put it all together:

<?php

session_start(); //we're using sessions so this is required!

if(isset($_POST['submitLogin'])) { //form submitted?

//verify login from user input

$username = mysql_real_escape_string($_POST['username']);
$password = md5(mysql_real_escape_string($_POST['password']));

$select_user = mysql_query("SELECT COUNT(id) AS amount FROM users WHERE username = '$username' AND password = '$password' ");
$user = mysql_fetch_assoc($select_user);
$amount_found = (int)$user['amount']; //amount of users found by the query

if($amount_found > 0) {
echo "Successfully logged in! Click <a href='login.php'>here</a> to continue.";
$_SESSION['loggedin'] = TRUE;
$_SESSION['username'] = $username;
}else{
echo "Invalid login! Click <a href='login.php'>here</a> to try again.";
}

}else{

//show login form
?>

<form method="POST" action="login.php">
<b>Username:</b> <br /> <input type="text" name="username"> <p>
<b>Password:</b> <br /> <input type="password" name="password"> <p>
<input type="submit" name="submitLogin" value="Login!">
</form>

<?php

}

?>

Note: The session_start function is added on top of the page as we're using sessions now in the script and therefore we need to call this function in order to be able to use them (enables the use of sessions).

Now there's one more thing we'll want to add: an if loop that checks if the user is already logged in ( by checking whether $_SESSION['loggedin'] is set to TRUE ). Because in that case we'll want to display a members page or something:

<?php

session_start(); //we're using sessions so this is required!

if($_SESSION['loggedin'] == TRUE) {
echo "Welcome back, ".htmlspecialchars($_SESSION['username'])."!";
}else{
if(isset($_POST['submitLogin'])) { //form submitted?

//verify login from user input

$username = mysql_real_escape_string($_POST['username']);
$password = md5(mysql_real_escape_string($_POST['password']));

$select_user = mysql_query("SELECT COUNT(id) AS amount FROM users WHERE username = '$username' AND password = '$password' ");
$user = mysql_fetch_assoc($select_user);
$amount_found = (int)$user['amount']; //amount of users found by the query

if($amount_found > 0) {
echo "Successfully logged in! Click <a href='login.php'>here</a> to continue.";
$_SESSION['loggedin'] = TRUE;
$_SESSION['username'] = $username;
}else{
echo "Invalid login! Click <a href='login.php'>here</a> to try again.";
}

}else{

//show login form
?>

<form method="POST" action="login.php">
<b>Username:</b> <br /> <input type="text" name="username"> <p>
<b>Password:</b> <br /> <input type="password" name="password"> <p>
<input type="submit" name="submitLogin" value="Login!">
</form>

<?php

}

}

?>

Note: htmlspecialchars is a function that can be used to strip html tags out of a string ( user input most likely ).

The script is pretty much ready to be used imediately. However you do need to ensure that you create a connection to the mysql database at the top of the script (or webpage in which the script is included), like:

mysql_connect("localhost", "root", "password");
mysql_select_db("webcodez");

But then of course with your mysql connection details.

If you'd like to include the login system into another page, you'll want to delete the session_start(); line at the top of the login.php page and put that at the top of your actual webpage. E.g.:

your_webpage.php

<?php
session_start();

include_once("login.php");
?>

Also you may then alter the login script a bit to fit your webpage.

To create a members area page for our login system, we can simply use the sessions $_SESSION['loggedin'] to verify whether a user is logged in, and the session $_SESSION['username'] to get the username of the logged in person. Do make sure, again, that we call session_start() at the top of the page first:

members.php

<?php
session_start();

if($_SESSION['loggedin'] == TRUE) { //loggedin already

echo "Welcome back, ".htmlspecialchars($_SESSION['username']);

}else{ //not logged in yet

include_once('login.php'); //show login page

}
?>

A thing that could be improved for the login system would be for example: to make the login process into one function, to include or automaticly go to a members page upon successful login or already being logged in, and to display the login form also when an invalid login occured. And without just copying the form twice into the code (which is inefficient), but for example:

<?php

session_start(); //we're using sessions so this is required!

if($_SESSION['loggedin'] == TRUE) {
header('location: members.php'); //members area
}else{

if($_POST['submitLogin']) {
//verify login from user input

$username = mysql_real_escape_string($_POST['username']);
$password = md5(mysql_real_escape_string($_POST['password']));

$select_user = mysql_query("SELECT COUNT(id) AS amount FROM users WHERE username = '$username' AND password = '$password' ");
$user = mysql_fetch_assoc($select_user);
$amount_found = (int)$user['amount']; //amount of users found by the query

if($amount_found > 0) {
$login_attempt = 1; //successful login attempt
$_SESSION['loggedin'] = TRUE;
$_SESSION['username'] = $username;
header('location: members.php'); //members area
}else{
$login_attempt = 0; //invalid login attempt
}

}

if( ($_POST['submitLogin'] AND isset($login_attempt) AND $login_attempt = 0) OR !$_POST['submitLogin'] ) {

//show login form

if($_POST['submitLogin']) { //attempted to login? (-> invalid login)
echo "<p>Invalid login. </p>";
}

?>

<form method="POST" action="login.php">
<b>Username:</b> <br /> <input type="text" name="username"> <p>
<b>Password:</b> <br /> <input type="password" name="password"> <p>
<input type="submit" name="submitLogin" value="Login!">
</form>

<?php

}

}

?>

Logout
To logout the user simply unset the sessions that indicate a logged in user ($_SESSION['loggedin'] and $_SESSION['username']):

unset($_SESSION['loggedin']);
unset($_SESSION['username']);

We could for example add this to the top of the login page ( or any page that you want to be able to logout through ):

if($_GET['do'] == "logout") {
unset($_SESSION['loggedin']);
unset($_SESSION['username']);
}

If we'd add this code to the top of the login page ( well, under the session_start() line ) the user could logout by going to login.php?do=logout. However it could be integrated into any webpage.