PHP & MySQL, Tutorials

This tutorial will cover creating a login system with registration upon registering an activation link will be emailed containing a link to activate the account. Once active the user can login, a reset option is available to reset the password.

Also ensure your php version is at least 5.3.17 but ideally should be 5.6 or 7

The file structure will be setup as follows:

The database will require a table to store the members, create a table called members:

CREATE TABLE `members` (
  `memberID` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `active` varchar(255) NOT NULL,
  `resetToken` varchar(255) DEFAULT NULL,
  `resetComplete` varchar(3) DEFAULT 'No',
  PRIMARY KEY (`memberID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

In the classes folder their are two files: password.php and user.php. password.php is used to provide the same hashing capability that exists within php 5.5 it uses the same function names so versions 5.3 - 5.5 can use the same functions.<

Note on password for php versions less than 5.5

This library requires PHP >= 5.3.7 OR a version that has the $2y fix backported into it (such as RedHat provides). Note that Debian's 5.3.3 version is NOT supported.

user.php is a class that contains methods to return the users hash (hashed password) as well as logging in, checking if a logged in session already exists and logging the user out.

I'll be going through the user.php methods as they are put to use.

Config.php

Config.php will be included into all pages enable sessions and turn on output buffering this way headers can be used anywhere in the project.

Set the timezone and define the credentials for the database, next attempt to make a new PDO connection if the connection fails display the error and kill the page.

Next include the user class and make an instance of it, pass in the database object to the class to make use of the database.

<?php
ob_start();
session_start();

//set timezone
date_default_timezone_set('Europe/London');

//database credentials
define('DBHOST','localhost');
define('DBUSER','database username');
define('DBPASS','password');
define('DBNAME','database name');

//application address
define('DIR','http://domain.com/');
define('SITEEMAIL','[email protected]');

try {

    //create PDO connection 
    $db = new PDO("mysql:host=".DBHOST.";port=8889;dbname=".DBNAME, DBUSER, DBPASS);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

} catch(PDOException $e) {
    //show error
    echo '<p class="bg-danger">'.$e->getMessage().'</p>';
    exit;
}

//include the user class, pass in the database connection
include('classes/user.php');
include('classes/phpmailer/mail.php');
$user = new User($db); 
?>

Next I have a folder called layout in there is a header.php and footer.php these will contain any layout code that will be used on every page, this saves having to include the stylesheet each time.

header.php is a typical header file, notice the title expects a $title variable, this will be created in the pages and made available to this file, also making use of Bootstrap this is optional and is not required.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title><?php if(isset($title)){ echo $title; }?></title>
    <link href="//netdna.bootstrapcdn.com/bootstrap/3.1.0/css/bootstrap.min.css" rel="stylesheet">
    <link rel="stylesheet" href="style/main.css">
</head>
<body>

Next footer.php this simply closed the body and html, that would be a good place for placing tracking code or any javascript includes.

</body>
</html>

index.php

This is the root page the system loads by default, on this page their is a form for users to register to the site, along with links to the login page, if they are already a member. Also if the user is already logged in they will be redirect to the members page.

How these pages start is by including the config file then checking if the user should be redirected or not.

A call is made to the user object $user->is_logged_in() this will return true or false if the user is logged in.

<?php
//include config
require_once('includes/config.php');

//check if already logged in move to home page
//if logged in redirect to members page
if( $user->is_logged_in() ){ header('Location: memberpage.php'); } 

The title and header.php file is also included on every page.

//define page title
$title = 'Demo';

//include header template
require('layout/header.php'); 

For new registrations display a form consisting of username, email, password and confirm password.

<form role="form" method="post" action="" autocomplete="off">

                <div class="form-group">
                    <input type="text" name="username" id="username" class="form-control input-lg" placeholder="User Name" value="<?php if(isset($error)){ echo $_POST['username']; } ?>" tabindex="1">
                </div>
                <div class="form-group">
                    <input type="email" name="email" id="email" class="form-control input-lg" placeholder="Email Address" value="<?php if(isset($error)){ echo $_POST['email']; } ?>" tabindex="2">
                </div>
                <div class="row">
                    <div class="col-xs-6 col-sm-6 col-md-6">
                        <div class="form-group">
                            <input type="password" name="password" id="password" class="form-control input-lg" placeholder="Password" tabindex="3">
                        </div>
                    </div>
                    <div class="col-xs-6 col-sm-6 col-md-6">
                        <div class="form-group">
                            <input type="password" name="passwordConfirm" id="passwordConfirm" class="form-control input-lg" placeholder="Confirm Password" tabindex="4">
                        </div>
                    </div>
                </div>
                
                <div class="row">
                    <div class="col-xs-6 col-md-6"><input type="submit" name="submit" value="Register" class="btn btn-primary btn-block btn-lg" tabindex="5"></div>
                </div>
            </form>

This is a standard form, one thing to note I make use of sticky forms which means if their has been a validation error the fields that have been filled out will be populated again with the supplied data, except for passwords. Username and email would be restored.

This is done by doing an if statement, if the array $error is set meaning it exists then retrain the $_POST

value="<?php if(isset($error)){ echo $_POST['email']; } ?>"

If an error has been created it will be stored in an error array to display them loop through the array:

//check for any errors
if(isset($error)){
  foreach($error as $error){
    echo '<p class="bg-danger">'.$error.'</p>';
  }
}

Once the new registration has been saved the form will post back to the same page appending a $_GET key on the end of the URL the key will be called action it will have a value of joined

(this technique is used through the project)

if(isset($_GET['action']) && $_GET['action'] == 'joined'){
  echo "<h2 class='bg-success'>Registration successful, please check your email to activate your account.</h2>";
}

The form should only be processed if it has been submitted this can be checked by an if statement:

//if form has been submitted process it
if(isset($_POST['submit'])){

This way only if the form has been submitted does the validation start and database interactions commence.

Validation

The validation used is fairly basic and can be improved upon

This example checks the length of the username if it's less then 3 characters an error is created, if the first check passes the username is looked up to see if it already exists by passing the username to the database if a record is found an error is created.

if(strlen($_POST['username']) < 3){
    $error[] = 'Username is too short.';
} else {
    $stmt = $db->prepare('SELECT username FROM members WHERE username = :username');
    $stmt->execute(array(':username' => $_POST['username']));
    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    if(!empty($row['username'])){
        $error[] = 'Username provided is already in use.';
    }
        
}

These check the password to make sure the email has not been used, it's important the email address is only used once, in the event the user wants to reset their password a link will be emailed to that user.

if(strlen($_POST['password']) < 3){
    $error[] = 'Password is too short.';
}

if(strlen($_POST['passwordConfirm']) < 3){
    $error[] = 'Confirm password is too short.';
}

if($_POST['password'] != $_POST['passwordConfirm']){
    $error[] = 'Passwords do not match.';
}

//email validation
if(!filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)){
    $error[] = 'Please enter a valid email address';
} else {
    $stmt = $db->prepare('SELECT email FROM members WHERE email = :email');
    $stmt->execute(array(':email' => $_POST['email']));
    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    if(!empty($row['email'])){
        $error[] = 'Email provided is already in use.';
    }
        
}

After the validation if no errors have been created then carry on.

The password provided cannot be stored as it is, that would be a huge security concern instead it's hashed by passing it to the user object inside a password_hash call this returns a hashed password which can then be stored in the database, this way no one can know what the password was apart from the user who entered it.

If your wondering how can the system login a user in without knowing the password; what happens when the user fills in the login form the password they enter is again hashed and then compared with the hash to see if its a match.

We also want to send an activation link to the user when they register to ensure their email address is active, for this we generate an activation code it will be sent in the emails and will form part of a url to validate the email address.

//if no errors have been created carry on
    if(!isset($error)){

        //hash the password
        $hashedpassword = $user->password_hash($_POST['password'], PASSWORD_BCRYPT);

        //create the activation code
        $activasion = md5(uniqid(rand(),true));

Next the user's details are saved to the database using a prepared statement, the first page of the query tells MySQL what action to perform in this case to add a new row and the table,columns to insert into.

Where their are columns starting with : like :username these are place holders that will be used to bind the username value to $stmt->execute call. This is done to avoid passing user provided data to the query directly and avoid chances of MySQL Injection.

Calling lastInsertId followed by the primary key will return the id of the record just saved, this is needed for the next step.

$stmt = $db->prepare('INSERT INTO members (username,password,email,active) VALUES (:username, :password, :email, :active)');
$stmt->execute(array(
    ':username' => $_POST['username'],
    ':password' => $hashedpassword,
    ':email' => $_POST['email'],
    ':active' => $activasion
));
$id = $db->lastInsertId('memberID');

Next send an email to the newly created user. Two constants defined in config.php will be used here.

DIR - contains the full website address
SITEEMAIL - the email address used for emails

In the body of the email is a link activate.php?x=$id&y=$activasion this link is passing the id of the user $id and also the activation code when the user received this email, clicking the link will activate their account.

$to = $_POST['email'];
$subject = "Registration Confirmation";
$body = "<p>Thank you for registering at demo site.</p>
<p>To activate your account, please click on this link: <a href='".DIR."activate.php?x=$id&y=$activasion'>".DIR."activate.php?x=$id&y=$activasion</a></p>
<p>Regards Site Admin</p>";

$mail = new Mail();
$mail->setFrom(SITEEMAIL);
$mail->addAddress($to);
$mail->subject($subject);
$mail->body($body);
$mail->send();

The last step is to redirect the page back to itself and adding an action with the value of joined so the page know if to show a success message.

header('Location: index.php?action=joined');
exit;

activate.php

This page checks for the id and activation code being passed from the url (this happens when the user clicks the link from their email).

Once the data has been verified the users record is updated, the column active is changed from the token to hold 'Yes' to say they are active, this will only happen if the id and token passed match what's stored against that user.

<?php
require('includes/config.php');

//collect values from the url
$memberID = trim($_GET['x']);
$active = trim($_GET['y']);

//if id is number and the active token is not empty carry on
if(is_numeric($memberID) && !empty($active)){

    //update users record set the active column to Yes where the memberID and active value match the ones provided in the array
    $stmt = $db->prepare("UPDATE members SET active = 'Yes' WHERE memberID = :memberID AND active = :active");
    $stmt->execute(array(
        ':memberID' => $memberID,
        ':active' => $active
    ));

    //if the row was updated redirect the user
    if($stmt->rowCount() == 1){

        //redirect to login page
        header('Location: login.php?action=active');
        exit;

    } else {
        echo "Your account could not be activated."; 
    }
    
}
?>

Login.php

Now users can register they need a way to login, start off with a form that expects their username and password.

<form role="form" method="post" action="" autocomplete="off">

    <div class="form-group">
        <input type="text" name="username" id="username" class="form-control input-lg" placeholder="User Name" value="<?php if(isset($error)){ echo $_POST['username']; } ?>" tabindex="1">
    </div>

    <div class="form-group">
        <input type="password" name="password" id="password" class="form-control input-lg" placeholder="Password" tabindex="3">
    </div>
    
    <div class="row">
        <div class="col-xs-9 col-sm-9 col-md-9">
             <a href='reset.php'>Forgot your Password?</a>
        </div>
    </div>
    
    <hr>
    <div class="row">
        <div class="col-xs-6 col-md-6"><input type="submit" name="submit" value="Login" class="btn btn-primary btn-block btn-lg" tabindex="5"></div>
    </div>
</form>

The login page will be used to show messages if the users account has been activated or password has been changed, the page will know which message to show based on the value contained inside $_GET['action']

if(isset($_GET['action'])){

    //check the action
    switch ($_GET['action']) {
        case 'active':
            echo "<h2 class='bg-success'>Your account is now active you may now log in.</h2>";
            break;
        case 'reset':
            echo "<h2 class='bg-success'>Please check your inbox for a reset link.</h2>";
            break;
        case 'resetAccount':
            echo "<h2 class='bg-success'>Password changed, you may now login.</h2>";
            break;
    }

}

Next attempt to log the user in. Collect the username and password from the form pass them to the users object in the login method this internally will fetch the users hash by looking for the username in the database once the hash is returned it's then passed to password_verify if the hash and user's hash match it returns true which in turns sets a session $_SESSION['loggedin'] to true otherwise false is returned.

public function login($username,$password){

    $row = $this->get_user_hash($username);

    if($this->password_verify($password,$row['password']) == 1){

        $_SESSION['loggedin'] = true;
        $_SESSION['username'] = $row['username'];
        $_SESSION['memberID'] = $row['memberID'];
        return true;
    }
}

//process login form if submitted
if(isset($_POST['submit'])){

    $username = $_POST['username'];
    $password = $_POST['password'];
    
    if($user->login($username,$password)){ 

        header('Location: memberpage.php');
        exit;
    
    } else {
        $error[] = 'Wrong username or password or your account has not been activated.';
    }

}//end if submit

Logout.php

To log a user out its very easy: 

//logout
$user->logout(); 

Once the user is logged out redirect them.

memberpage.php

Once the user is logged in redirect them to the members only page (optional).
To ensure a user can only access the page if logged in do a check:

//if not logged in redirect to login page
if(!$user->is_logged_in()){ header('Location: login.php'); } 

In this example their is not a lot to the members page namely:

<h2>Member only page</h2>
<p><a href='logout.php'>Logout</a></p>

reset.php

Every system need the ability to reset a password in case it's forgotten, how this will work is a user enters their email address, a check is made to make sure its belongs to a user.

Next a token is created and saved to the users record, an email is sent to them containing a link to when clicked the token from the link is verified, if it passed the user is provided with a form to enter their new password, its then saved to the database.

This may seem like a long winded approach but it does prevent the password being sent by email which is not recommended.

To start with the form:

<form role="form" method="post" action="" autocomplete="off">
    <div class="form-group">
        <input type="email" name="email" id="email" class="form-control input-lg" placeholder="Email" value="" tabindex="1">
    </div>
    
    <hr>
    <div class="row">
        <div class="col-xs-6 col-md-6"><input type="submit" name="submit" value="Sent Reset Link" class="btn btn-primary btn-block btn-lg" tabindex="2"></div>
    </div>
</form>

If their is an $_GET['action'] show the correct message.

<?php
if(isset($_GET['action'])){

    //check the action
    switch ($_GET['action']) {
        case 'active':
            echo "<h2 class='bg-success'>Your account is now active you may now log in.</h2>";
            break;
        case 'reset':
            echo "<h2 class='bg-success'>Please check your inbox for a reset link.</h2>";
            break;
    }
}
?>

Next process the form ensure the email matches a user:

//email validation
if(!filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)){
    $error[] = 'Please enter a valid email address';
} else {
    $stmt = $db->prepare('SELECT email FROM members WHERE email = :email');
    $stmt->execute(array(':email' => $_POST['email']));
    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    if(empty($row['email'])){
        $error[] = 'Email provided is not on recognised.';
    }
        
}

Create the token

//create the activation code
$token = md5(uniqid(rand(),true));

Next update the users record and set resetToken to the value of the token and resetComplete to No that will be needed if the link is clicked and password has been changed. Send an email to the user containing a link that points to resetPassword.php?key=$token passing the token.

$stmt = $db->prepare("UPDATE members SET resetToken = :token, resetComplete='No' WHERE email = :email");
$stmt->execute(array(
    ':email' => $row['email'],
    ':token' => $token
));

//send email
$to = $row['email'];
$subject = "Password Reset";
$body = "<p>Someone requested that the password be reset.</p>
<p>If this was a mistake, just ignore this email and nothing will happen.</p>
<p>To reset your password, visit the following address: <a href='".DIR."resetPassword.php?key=$token'>".DIR."resetPassword.php?key=$token</a></p>";

$mail = new Mail();
$mail->setFrom(SITEEMAIL);
$mail->addAddress($to);
$mail->subject($subject);
$mail->body($body);
$mail->send();

//redirect to index page
header('Location: login.php?action=reset');
exit;

resetPassword.php

First check the token been passed to the page matches a user

$stmt = $db->prepare('SELECT resetToken, resetComplete FROM members WHERE resetToken = :token');
$stmt->execute(array(':token' => $_GET['key']));
$row = $stmt->fetch(PDO::FETCH_ASSOC);

//if no token from db then kill the page
if(empty($row['resetToken'])){
    $stop = 'Invalid token provided, please use the link provided in the reset email.';
} elseif($row['resetComplete'] == 'Yes') {
    $stop = 'Your password has already been changed!';
}

If $stop has been set then display that

if(isset($stop)){
    echo "<p class='bg-danger'>$stop</p>";
}

If no errors have been created show a form to change the password.

<form role="form" method="post" action="" autocomplete="off">
    <div class="row">
        <div class="col-xs-6 col-sm-6 col-md-6">
            <div class="form-group">
                <input type="password" name="password" id="password" class="form-control input-lg" placeholder="Password" tabindex="1">
            </div>
        </div>
        <div class="col-xs-6 col-sm-6 col-md-6">
            <div class="form-group">
                <input type="password" name="passwordConfirm" id="passwordConfirm" class="form-control input-lg" placeholder="Confirm Password" tabindex="1">
            </div>
        </div>
    </div>
    
    <hr>
    <div class="row">
        <div class="col-xs-6 col-md-6"><input type="submit" name="submit" value="Change Password" class="btn btn-primary btn-block btn-lg" tabindex="3"></div>
    </div>
</form>

Once the form has been submitted validate the data then hash the password update the users row and set resetComplete to Yes to indicate the process is finished if the reset link is clicked again from email the process will be halted.

//if form has been submitted process it
if(isset($_POST['submit'])){

    //basic validation
    if(strlen($_POST['password']) < 3){
        $error[] = 'Password is too short.';
    }

    if(strlen($_POST['passwordConfirm']) < 3){
        $error[] = 'Confirm password is too short.';
    }

    if($_POST['password'] != $_POST['passwordConfirm']){
        $error[] = 'Passwords do not match.';
    }

    //if no errors have been created carry on
    if(!isset($error)){

        //hash the password
        $hashedpassword = $user->password_hash($_POST['password'], PASSWORD_BCRYPT);

        try {

            $stmt = $db->prepare("UPDATE members SET password = :hashedpassword, resetComplete = 'Yes'  WHERE resetToken = :token");
            $stmt->execute(array(
                ':hashedpassword' => $hashedpassword,
                ':token' => $row['resetToken']
            ));

            //redirect to index page
            header('Location: login.php?action=resetAccount');
            exit;

        //else catch the exception and show the error.
        } catch(PDOException $e) {
            $error[] = $e->getMessage();
        }

    }

}

Conclusion

That covers the foundations, this can be used as a starting point to build members based sites or even a start to an admin panel.

Install from Download Github