User account management, roles, permissions, authentication PHP and MySQL - Part 2


This is the second part of a series on user accounts management system, authentication, roles, permissions. You can find the first part here.

Database Configuration

Create a MySQL database called user-accounts. Then in the root folder of your project (user-accounts folder), create a file and call it config.php. This file will be used to configure database variables and then connect our application to the MySQL database we just created.

config.php:

<?php
	session_start(); // start session
	// connect to database
	$conn = new mysqli("localhost", "root", "", "user-accounts");
	// Check connection
	if ($conn->connect_error) {
	    die("Connection failed: " . $conn->connect_error);
	}
  // define global constants
	define ('ROOT_PATH', realpath(dirname(__FILE__))); // path to the root folder
	define ('INCLUDE_PATH', realpath(dirname(__FILE__) . '/includes' )); // Path to includes folder
	define('BASE_URL', 'http://localhost/user-accounts/'); // the home url of the website
?>

We have also started the session because we will need to use it later on to store logged in user information such as username. At the end of the file, we are defining constants which will help us better handle file includes. 

Our application is now connected to the MySQL database. Let's create a form that allows a user to enter their details and register their account. Create a signup.php file in the root folder of the project:

signup.php:

<?php include('config.php'); ?>
<?php include(INCLUDE_PATH . '/logic/userSignup.php'); ?>
<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <title>UserAccounts - Sign up</title>
  <!-- Bootstrap CSS -->
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.7/css/bootstrap.min.css" />
  <!-- Custom styles -->
  <link rel="stylesheet" href="assets/css/style.css">
</head>
<body>
  <?php include(INCLUDE_PATH . "/layouts/navbar.php") ?>

  <div class="container">
    <div class="row">
      <div class="col-md-4 col-md-offset-4">
        <form class="form" action="signup.php" method="post" enctype="multipart/form-data">
          <h2 class="text-center">Sign up</h2>
          <hr>
          <div class="form-group">
            <label class="control-label">Username</label>
            <input type="text" name="username" class="form-control">
          </div>
          <div class="form-group">
            <label class="control-label">Email Address</label>
            <input type="email" name="email" class="form-control">
          </div>
          <div class="form-group">
            <label class="control-label">Password</label>
            <input type="password" name="password" class="form-control">
          </div>
          <div class="form-group">
            <label class="control-label">Password confirmation</label>
            <input type="password" name="passwordConf" class="form-control">
          </div>
          <div class="form-group" style="text-align: center;">
            <img src="http://via.placeholder.com/150x150" id="profile_img" style="height: 100px; border-radius: 50%" alt="">
            <!-- hidden file input to trigger with JQuery  -->
            <input type="file" name="profile_picture" id="profile_input" value="" style="display: none;">
          </div>
          <div class="form-group">
            <button type="submit" name="signup_btn" class="btn btn-success btn-block">Sign up</button>
          </div>
          <p>Aready have an account? <a href="login.php">Sign in</a></p>
        </form>
      </div>
    </div>
  </div>
<?php include(INCLUDE_PATH . "/layouts/footer.php") ?>
<script type="text/javascript" src="assets/js/display_profile_image.js"></script>

On the very first line in this file, we are including the config.php file we created earlier because we will need to use the INCLUDE_PATH constant that config.php provides inside our signup.php file. Using this INCLUDE_PATH constant, we are also including navbar.php, footer.php, and userSignup.php which holds the logic for registering a user in a database. We will create these files very soon.

Near the end of the file, there is a round field where the user can click to upload a profile image. When the user clicks on this area and selects a profile image from their computer, a preview of this image is first displayed.

This image preview is achieved with jquery. When the user clicks on the upload image button, we will programmatically trigger the file input field using JQuery and this brings up the user's computer files for them to browse their computer and choose their profile image. When they select the image, we use Jquery still to display the image temporarily. The code that does this is found in our display_profile_image.php file which we will create soon.

Don't view on the browser just yet. Let's first give this file what we owe it. For now, inside assets/css folder, let's create the style.css file we linked in the head section.

style.css:

@import url('https://fonts.googleapis.com/css?family=Lora');
* { font-family: 'Lora', serif; font-size: 1.04em; }
span.help-block { font-size: .7em; }
form label { font-weight: normal; }
.success_msg { color: '#218823'; }
.form { border-radius: 5px; border: 1px solid #d1d1d1; padding: 0px 10px 0px 10px; margin-bottom: 50px; }
#image_display { height: 90px; width: 80px; float: right; margin-right: 10px; }

On the first line of this file, we are importing a Google font named 'Lora' to make our app to have a more beautiful font.

The next file we need in this signup.php is the navbar.php and footer.php files. Create these two files inside includes/layouts folder:

navbar.php:

<div class="container"> <!-- The closing container div is found in the footer -->
  <nav class="navbar navbar-default">
    <div class="container-fluid">
      <div class="navbar-header">
        <a class="navbar-brand" href="#">UserAccounts</a>
      </div>
      <ul class="nav navbar-nav navbar-right">
          <li><a href="<?php echo BASE_URL . 'signup.php' ?>"><span class="glyphicon glyphicon-user"></span> Sign Up</a></li>
          <li><a href="<?php echo BASE_URL . 'login.php' ?>"><span class="glyphicon glyphicon-log-in"></span> Login</a></li>
      </ul>
    </div>
  </nav>

footer.php:

    <!-- JQuery -->
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    <!-- Bootstrap JS -->
    <script src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.7/js/bootstrap.min.js"></script>
  </div> <!-- closing container div -->
</body>
</html>

The very last line of the signup.php file links to a JQuery script named display_profile_image.js and it does just what its name says. Create this file inside assets/js folder and paste this code inside it:

display_profile_image.js:

$(document).ready(function(){
  // when user clicks on the upload profile image button ...
  $(document).on('click', '#profile_img', function(){
    // ...use Jquery to click on the hidden file input field
    $('#profile_input').click();
    // a 'change' event occurs when user selects image from the system.
    // when that happens, grab the image and display it
    $(document).on('change', '#profile_input', function(){
      // grab the file
      var file = $('#profile_input')[0].files[0];
      if (file) {
          var reader = new FileReader();
          reader.onload = function (e) {
              // set the value of the input for profile picture
              $('#profile_input').attr('value', file.name);
              // display the image
              $('#profile_img').attr('src', e.target.result);
          };
          reader.readAsDataURL(file);
      }
    });
  });
});

And lastly, the userSignup.php file. This file is where the signup form data is submitted to for processing and saving in the database. Create userSignup.php inside includes/logic folder and paste this code inside it:

userSignup.php:

<?php include(INCLUDE_PATH . "/logic/common_functions.php"); ?>
<?php
// variable declaration
$username = "";
$email  = "";
$errors  = [];
// SIGN UP USER
if (isset($_POST['signup_btn'])) {
	// validate form values
	$errors = validateUser($_POST, ['signup_btn']);

	// receive all input values from the form. No need to escape... bind_param takes care of escaping
	$username = $_POST['username'];
	$email = $_POST['email'];
	$password = password_hash($_POST['password'], PASSWORD_DEFAULT); //encrypt the password before saving in the database
	$profile_picture = uploadProfilePicture();
	$created_at = date('Y-m-d H:i:s');

	// if no errors, proceed with signup
	if (count($errors) === 0) {
		// insert user into database
		$query = "INSERT INTO users SET username=?, email=?, password=?, profile_picture=?, created_at=?";
		$stmt = $conn->prepare($query);
		$stmt->bind_param('sssss', $username, $email, $password, $profile_picture, $created_at);
		$result = $stmt->execute();
		if ($result) {
		  $user_id = $stmt->insert_id;
			$stmt->close();
			loginById($user_id); // log user in
		 } else {
			 $_SESSION['error_msg'] = "Database error: Could not register user";
		}
	 }
}

I saved this file for last because it had more work to it. First thing is that we are including yet another file named common_functions.php at the top of this file. We are including this file because we are using two methods that come from it namely: validateUser() and loginById() which we will create shortly.

Create this common_functions.php file in your include/logic folder:

common_functions.php:

<?php
  // Accept a user ID and returns true if user is admin and false if otherwise
  function isAdmin($user_id) {
    global $conn;
    $sql = "SELECT * FROM users WHERE id=? AND role_id IS NOT NULL LIMIT 1";
    $user = getSingleRecord($sql, 'i', [$user_id]); // get single user from database
    if (!empty($user)) {
      return true;
    } else {
      return false;
    }
  }
  function loginById($user_id) {
    global $conn;
    $sql = "SELECT u.id, u.role_id, u.username, r.name as role FROM users u LEFT JOIN roles r ON u.role_id=r.id WHERE u.id=? LIMIT 1";
    $user = getSingleRecord($sql, 'i', [$user_id]);

    if (!empty($user)) {
      // put logged in user into session array
      $_SESSION['user'] = $user;
      $_SESSION['success_msg'] = "You are now logged in";
      // if user is admin, redirect to dashboard, otherwise to homepage
      if (isAdmin($user_id)) {
        $permissionsSql = "SELECT p.name as permission_name FROM permissions as p
                            JOIN permission_role as pr ON p.id=pr.permission_id
                            WHERE pr.role_id=?";
        $userPermissions = getMultipleRecords($permissionsSql, "i", [$user['role_id']]);
        $_SESSION['userPermissions'] = $userPermissions;
        header('location: ' . BASE_URL . 'admin/dashboard.php');
      } else {
        header('location: ' . BASE_URL . 'index.php');
      }
      exit(0);
    }
  }

// Accept a user object, validates user and return an array with the error messages
  function validateUser($user, $ignoreFields) {
  		global $conn;
      $errors = [];
      // password confirmation
      if (isset($user['passwordConf']) && ($user['password'] !== $user['passwordConf'])) {
        $errors['passwordConf'] = "The two passwords do not match";
      }
      // if passwordOld was sent, then verify old password
      if (isset($user['passwordOld']) && isset($user['user_id'])) {
        $sql = "SELECT * FROM users WHERE id=? LIMIT 1";
        $oldUser = getSingleRecord($sql, 'i', [$user['user_id']]);
        $prevPasswordHash = $oldUser['password'];
        if (!password_verify($user['passwordOld'], $prevPasswordHash)) {
          $errors['passwordOld'] = "The old password does not match";
        }
      }
      // the email should be unique for each user for cases where we are saving admin user or signing up new user
      if (in_array('save_user', $ignoreFields) || in_array('signup_btn', $ignoreFields)) {
        $sql = "SELECT * FROM users WHERE email=? OR username=? LIMIT 1";
        $oldUser = getSingleRecord($sql, 'ss', [$user['email'], $user['username']]);
        if (!empty($oldUser['email']) && $oldUser['email'] === $user['email']) { // if user exists
          $errors['email'] = "Email already exists";
        }
        if (!empty($oldUser['username']) && $oldUser['username'] === $user['username']) { // if user exists
          $errors['username'] = "Username already exists";
        }
      }

      // required validation
  	  foreach ($user as $key => $value) {
        if (in_array($key, $ignoreFields)) {
          continue;
        }
  			if (empty($user[$key])) {
  				$errors[$key] = "This field is required";
  			}
  	  }
  		return $errors;
  }
  // upload's user profile profile picture and returns the name of the file
  function uploadProfilePicture()
  {
    // if file was sent from signup form ...
    if (!empty($_FILES) && !empty($_FILES['profile_picture']['name'])) {
        // Get image name
        $profile_picture = date("Y.m.d") . $_FILES['profile_picture']['name'];
        // define Where image will be stored
        $target = ROOT_PATH . "/assets/images/" . $profile_picture;
        // upload image to folder
        if (move_uploaded_file($_FILES['profile_picture']['tmp_name'], $target)) {
          return $profile_picture;
          exit();
        }else{
          echo "Failed to upload image";
        }
    }
  }

Let me draw your attention to 2 important functions in this file. They are: getSingleRecord() and getMultipleRecords(). These functions are very important because anywhere in our whole application, when we want to select a record from the database, we will just call the getSingleRecord() function and pass the SQL query to it. If we want to select multiple records, you guessed it, we will simply just call the getMultipleRecords() function too with passing the appropriate SQL query. 

These two functions take 3 parameters namely the SQL query, the variable types (for instance, 's' means string, 'si' meaning string and integer, and so on) and lastly a third parameter which is an array of all the values that the query needs in order to execute.

For instance, If I want to select from the users table where username is 'John' and age 24, I will just write my query like this:

$sql = SELECT * FROM users WHERE username=John AND age=20; // this is the query

$userĀ = getSingleRecord($sql, 'si', ['John', 20]); // perform database query

In the function call, 's' represents string type (since username 'John' is a string) and 'i' means integer (age 20 is an integer). This function makes our work immensely easy because if we want to perform a database query in a hundred different places in our application, we will not have to only these two lines. The functions themselves each have about 8 - 10 lines of code so we are spared from repeting code. Let's implement these methods at once.

config.php file will be included in every file where database queries are performed since it holds database configuration. So it is the perfect place to define these methods. Open config.php once again and just add these methods to the end of the file:

config.php:

// ...More code here ...

function getMultipleRecords($sql, $types = null, $params = []) {
  global $conn;
  $stmt = $conn->prepare($sql);
  if (!empty($params) && !empty($params)) { // parameters must exist before you call bind_param() method
    $stmt->bind_param($types, ...$params);
  }
  $stmt->execute();
  $result = $stmt->get_result();
  $user = $result->fetch_all(MYSQLI_ASSOC);
  $stmt->close();
  return $user;
}
function getSingleRecord($sql, $types, $params) {
  global $conn;
  $stmt = $conn->prepare($sql);
  $stmt->bind_param($types, ...$params);
  $stmt->execute();
  $result = $stmt->get_result();
  $user = $result->fetch_assoc();
  $stmt->close();
  return $user;
}
function modifyRecord($sql, $types, $params) {
  global $conn;
  $stmt = $conn->prepare($sql);
  $stmt->bind_param($types, ...$params);
  $result = $stmt->execute();
  $stmt->close();
  return $result;
}

We are using prepared statements and this is important for security reasons.

Now back to our common_functions.php file again. This file holds 4 important functions that will be used later on by many other files. 

When the user registers, we want to make sure they provided the right data, so we call the validateUser() function, which this file provides. If a profile image was selected, we upload it by calling the uploadProfilePicture() function, which this file provides.

If we successfully save the user in the database we want to log them in immediately, so we call the loginById() function, which this file provides. When a user logs in, we want to know whether they are admin or normal, so we call the isAdmin() function, which this file provides. If we find that they are admin (if isAdmin() returns true), we redirect them to the dashboard. If normal users, we redirect to the homepage.

So you can see our common_functions.php file is very important. We will use all these functions when we will be working on our admin section which greatly reduces our work and avoids repetition of code.

To enable the user to signup, let's create the users table. But since the users table is related to the roles table, We will create the roles table first.

roles table:

CREATE TABLE `roles` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `description` text NOT NULL,
  PRIMARY KEY (`id`)
)

users table:

CREATE TABLE `users`(
    `id` INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    `role_id` INT(11) DEFAULT NULL,
    `username` VARCHAR(255) UNIQUE NOT NULL,
    `email` VARCHAR(255) UNIQUE NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `profile_picture` VARCHAR(255) DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
    CONSTRAINT `users_ibfk_1` FOREIGN KEY(`role_id`) REFERENCES `roles`(`id`) ON DELETE SET NULL ON UPDATE NO ACTION
)

The users table is related to the roles table in a Many-to-One relationship. When a role is deleted from the roles table, we want all users previously having that role_id as their attribute to have its value set to NULL. This means that the user will no longer be admin.

If you are creating the table manually, do well to add this constraint. If you are using PHPMyAdmin, you can do that by clicking on the structure tab on users table, then relation view table, and then finally filling this form like this:

At this point, our system allows a user to register and then after registering, they are automatically logged in. But after logging in, as shown in the loginById() function, they are redirected to the home page (index.php). Let's create that page. In the root of the application, create a file named index.php.

index.php:

<?php include("config.php") ?>
<?php include(INCLUDE_PATH . "/logic/common_functions.php"); ?>
<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <title>UserAccounts - Home</title>
  <!-- Bootstrap CSS -->
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.7/css/bootstrap.min.css" />
  <!-- Custome styles -->
  <link rel="stylesheet" href="static/css/style.css">
</head>
<body>
    <?php include(INCLUDE_PATH . "/layouts/navbar.php") ?>
    <?php include(INCLUDE_PATH . "/layouts/messages.php") ?>
    <h1>Home page</h1>
    <?php include(INCLUDE_PATH . "/layouts/footer.php") ?>

Now open your browser, go to http://localhost/user-accounts/signup.php, fill the form with some test information (and do well to remember them since we will use the user later on to log in), then click the signup button. If all went well, the user will be saved in the database and our application will redirect to the Home page.

On the homepage, you will see an error which arises because we are including messages.php file which we have not yet created. Let's create it at once.

In the includes/layouts directory, create a file named messages.php:

messages.php

<?php if (isset($_SESSION['success_msg'])): ?>
  <div class="alert <?php echo 'alert-success'; ?> alert-dismissible" role="alert">
    <button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">&times;</span></button>
    <?php
      echo $_SESSION['success_msg'];
      unset($_SESSION['success_msg']);
    ?>
  </div>
<?php endif; ?>

<?php if (isset($_SESSION['error_msg'])): ?>
  <div class="alert alert-danger alert-dismissible" role="alert">
    <button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">&times;</span></button>
    <?php
      echo $_SESSION['error_msg'];
      unset($_SESSION['error_msg']);
    ?>
  </div>
<?php endif; ?>

Now refresh the homepage and the error is gone.

And that's it for this part. In the next part we will continue with validating the signup form, user login/logout and begin work on the admin section. This sounds like too much work but trust me, it is straightforward especially as we have already written some code that eases our work on the Admin section. 

Thanks for following. Hope you are coming along. If you have any thoughts, drop them in the comments below. If you encountered any errors or didn't understand something, let me know in the comments section so I can try and help you out.

See you in the next part.

Awa Melvine

Share Google Plus Linkedin


Comments