PHP CHAPTER 5 2019-01-02T12:21:59+00:00

PHP Chapter 5

Allowing Users to Register Accounts

Now that we’ve done all the hard work of building an extensible framework, it’s time to add some new functionality to the website. We’re going to make it so that users can register accounts on the website with a view to letting them post jokes themselves. You should already have the table for authors in the database with some data you added via MySQL Workbench. If you don’t, you can execute this query to create the table:

CREATE TABLE author (

   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

   name VARCHAR(255),

   email VARCHAR(255)

) DEFAULT CHARACTER SET utf8mb4 ENGINE=InnoDB

Don’t worry about adding any records if you have none. We’ll be creating a form that lets us add authors from the website. The first step is to add another column to the database to store the password that users will use to log in. Let’s create another column, either using MySQL Workbench’s GUI or running the following query:

ALTER TABLE author ADD COLUMN password VARCHAR(255)

The column name should be password and the type VARCHAR(255). I know that 255 characters sounds like a very long password, but there’s a reason for making the column so large, which we’ll get to later. The first thing that’s needed is the controller code. Create the file Register.php in the Ijdb\Controllers directory, then create the class with the following variables, constructor and a method for loading the registration form. For registering users, the only dependency needed is the DatabaseTable object that represents the authors table:

<?php

  namespace Ijdb\Controllers;

  use \Ninja\DatabaseTable;

class Register

{

    private $authorsTable;

public function __construct(DatabaseTable $authorsTable)

{

      $this->authorsTable = $authorsTable;

}

public function registrationForm()

{

return [‘template’ => ‘register.html.php’,

‘title’ => ‘Register an account’];

}

public function success()

{

     return [‘template’ => ‘registersuccess.html.php’, ‘title’ => ‘Registration Successful’];

     }

}

We’ve added two actions: one for displaying the form, and one for displaying the Registration Successful page. Here’s the template, register.html.php, in the templates directory:

<form action=”” method=”post”>

<label for=”email”>Your email address</label>

<input name=”author[email]” id=”email” type=”text”>

<label for=”name”>Your name</label>

<input name=”author[name]” id=”name” type=”text”>

<label for=”password”>Password</label> <input name=”author[password]” id=”password”

type=”password”>

<input type=”submit” name=”submit” . value=”Register account”>

</form>

Here’s registersuccess.html.php:

<h2>Registration Successful</h2>

<p>You are now registered on the Internet Joke Database</p>

And finally, the routes in IjdbRoutes.php:

// …

$jokeController = new \Ijdb\Controllers\Joke($jokesTable, $authorsTable);

  $authorController = new \Ijdb\Controllers\Register($authorsTable);

$routes = [

    ‘author/register’ => [

          ‘GET’ => [

                 ‘controller’ => $authorController,

                ‘action’ => ‘registrationForm’

       ]

],

‘author/success’ => [

      ‘GET’ => [

            ‘controller’ => $authorController,

          ‘action’ => ‘success’

      ]

],

‘joke/edit’ => [

// …

If we visit http://192.168.10.10/author/register, we should see the form. To make the form work, we’ll need to add the POST route in IjdbRoutes and the relevant method in the controller.

‘author/register’ => [

   ‘GET’ => [

      ‘controller’ => $authorController,

      ‘action’ => ‘registrationForm’

   ],

  ‘POST’ => [

       ‘controller’ => $authorController,

      ‘action’ => ‘registerUser’

     ]

],

Register.php:

 public function registerUser() {

     $author = $_POST[‘author’];

    $this->authorsTable->save($author);

header(‘Location: /author/success’);

}

You canfind this code in Registration-FormOnce the form is submitted, the router will execute the registerUser method and save the new author information by reading from the $_POST array. Check if it works by filling out the form, submitting it and selecting all the records from the author table using MySQL Workbench. If you don’t see the data for the record you just added, double check all your code and try again. We have a basic registration form working, but there are some problems with it in its current state. We need some control over what’s allowed in the database. There are some rules we probably want to enforce on the data before allowing the record to be inserted: 

  • All fields should actually contain some data, so no blank email or name.
  • The email address should be a real email address. For example, paul@example.org is allowed, but abc123 isn’t.
  • The email address entered must not already belong to an account.

These validation rules need to be checked before the data is inserted, but also after the form is submitted. If there’s a problem with the submission, it’s good practice to show the form to the user again so they can fix their mistakes. Each one of these validation rules needs to be applied in slightly different ways, but with a similar result. We’ll use if statements for each check and set a boolean variable $valid to keep track of whether the data is valid or not. For example, to check that each field has a value inside it, we can use a series of if statements that set the $valid variable to false if one of the fields is empty:

public function registerUser() {

$author = $_POST[‘author’];

// Assume the data is valid to begin with

$valid = true;

// But if any of the fields have been left blank

// set $valid to false

if (empty($author[‘name’])) {

        $valid = false;

}

if (empty($author[’email’])) {

     $valid = false;

}

if (empty($author[‘password’])) {

       $valid = false;

}

// If $valid is still true, no fields were blank

// and the data can be added

if ($valid == true) {

        $this->authorsTable->save($author);

   header(‘Location: /author/success’);

}

else {

// If the data is not valid, show the form again

   return [‘template’ => ‘register.html.php’,

           ‘title’ => ‘Register an account’];

       }

}

Using empty() Above

I’ve used empty($author[‘name’]) instead of $author[‘name’] == ” here, because this will also catch invalid form submissions without causing an error. It’s possible for someone to submit a POST request without filling in your form! It’s likely they might not supply values (an empty string is still a value!) for some of the form fields. It’s better to avoid these kinds of errors than potentially alert malicious users to how the site works.

Try it yourself by submitting the form and leaving one or more fields blank. You should see the blank form again rather than the “Registration Successful” message. If $valid has been set to false because one of the fields is empty, then the form is shown again by returning a title and template. If you did try this for yourself, you’ll have immediately noticed that whatever you typed into the box has been removed, and there’s no indication to the user of what was wrong.To fix this, let’s first create a second array to keep a list of error messages to show to the user:

public function registerUser() {

       $author = $_POST[‘author’];

// Assume the data is valid to begin with

    $valid = true;

    $errors = [];

// But if any of the fields have been left blank

// set $valid to false

if (empty($author[‘name’])) {

      $valid = false;

     $errors[] = ‘Name cannot be blank’;

}

if (empty($author[’email’])) {

$valid = false;

$errors[] = ‘Email cannot be blank’;

}

if (empty($author[‘password’])) {

     $valid = false;

    $errors[] = ‘Password cannot be blank’;

}

// If $valid is still true, no fields were blank

// and the data can be added

if ($valid == true) {

          $this->authorsTable->save($author);

  header(‘Location: /author/success’);

}

else {

//If the data is not valid, show the form again

   return [‘template’ => ‘register.html.php’,

               ‘title’ => ‘Register an account’];

          }

}

Remember the [] = operator for arrays? This will be added to the end of the $errors array, so if the user leaves all three fields blank, all three error messages will be stored in the $errors array. These errors will need to be shown to the user by displaying them in the template. When we created the joke list page, we supplied the template some variables using the variables key in the returned array. The same thing can be done here with the errors:

// If the data is not valid, show the form again

return [‘template’ => ‘register.html.php’,

 ‘title’ => ‘Register an account’,

   ‘variables’ => [

       ‘errors’ => $errors

    ]

];

And now the $errors variable can be used in register.html.php:

<?php

      if (!empty($errors)) :

?>

<div class=”errors”>

        <p>Your account could not be created,

              please check the following:</p>

<ul>

    <?php

            foreach ($errors as $error) :

     ?>

<li><?= $error ?></li>

     <?php

             endforeach; ?>

</ul>

</div>

<?php

       endif;

?>

<form action=”” method=”post”>

<label for=”email”>Your email address</label>

<input name=”author[email]” id=”email” type=”text”>

<label for=”name”>Your name</label>

<input name=”author[name]” id=”name” type=”text”>

<label for=”password”>Password</label>

<input name=”author[password]” id=”password” type=”password”>

<input type=”submit” name=”submit”  value=”Register account”>

</form>

To make the errors look a bit nicer, add the following to jokes.css:

.errors {

     padding: 1em;

     border: 1px solid red;

     background-color: lightyellow;

     color: red;

     margin-bottom: 1em;

     overflow: auto;

}

.errors ul {

      margin-left: 1em;

}

If there are any errors now, they’ll all be printed in a list at the top of the page before the form, and the user will know what went wrong. To make things even easier, we’ll re-fill the form with the data from $_POSTFirstly, let’s supply the $author information to the template by amending the return value:

return [‘template’ => ‘register.html.php’,

‘title’ => ‘Register an account’,

‘variables’ => [

    ‘errors’ => $errors,

    ‘author’ => $author

   ]

];

Now print the values in the form fields if they’re set. This is exactly the same approach we took in editjoke.html.php to fill the form with the information in the database:

<label for=”email”>Your email address</label>

<input name=”author[email]” id=”email” type=”text”

value=”<?=$author[’email’] ?? ”?>”>

<label for=”name”>Your name</label>

<input name=”author[name]” id=”name” type=”text” . value=”<?=$author[‘name’] ?? ”?>”>

<label for=”password”>Password</label>

<input name=”author[password]” id=”password”

             type=”password”

           value=”<?=$author[‘password’] ?? ”?>”>

<input type=”submit” name=”submit” v

alue=”Register account”>

You can find this code in Registration-Validation.

Validating Email Addresses

The validation above will prevent someone from leaving the email address field blank. However, it doesn’t guarantee they’ve entered a valid email address. They can still enter “a” into the field and it will pass the validation. To ensure they enter a valid email address, we need to do some checking. We could look at each character in the string and look for an “@” symbol, ensuring it’s not the first character, and perhaps also look for a “.” after the “@” to match “x@x.x”. However, this will take quite a few lines of code and will be complex to implement. As for most common problems, PHP includes a method of validating email addresses that’s far more accurate and simpler to use than building your own. There’s no need to reinvent the wheel. To check an email address in PHP, you can use the filter_var function like so:

$email = ‘tom@example.org’;

if (filter_var($email, FILTER_VALIDATE_EMAIL) == false) {

       echo ‘Valid email address’;

}

else {

        echo ‘Invalid email address’;

}

The filter_var function is provided by PHP and takes two arguments. The first is the string to validate, and the second is the type of data to check against. There are several options, including FILTER_VALIDATE_URL and FILTER_VALIDATE_INT, for checking whether a given string is a valid URL or integer. The only one we need at this moment is FILTER_VALIDATE_EMAIL, which is used to validate email addresses. For a complete list of all the options supported by filter_var, see the function’s page on the PHP website. Let’s implement this check in the Register controller:

if (empty($author[’email’])) {

    $valid = false;

    $errors[] = ‘Email cannot be blank’;

}

else if (filter_var($author[’email’]) == false) {

    $valid = false;

    $errors[] = ‘Invalid email address’;

}

You can find this code in Registration-Validation-EmailFirst, we check whether the email address has been provided, and if it has, whether it’s a valid email address using filter_var. If both checks pass, the email address is valid and no errors are displayed.

Preventing the Same Person from Registering Twice

There’s one other check we need to do on the email address: ensuring the same person can’t have multiple accounts. Allowing someone to have multiple accounts can cause problems with a website. If they log in, which account do they log in to? If they can see their previous jokes, they’ll only see the ones posted by the account they’re logged in to, and any information tailored to them will only be displayed on one account. It’s very good practice to prevent the same person from registering twice with the same email address. This can be enforced in the database, but it’s more consistent to use PHP to check this. We already have the $authorsTable object for searching for records in the author database table. We can make use of it to check if an email address already exists. Currently, the class DatabaseTable contains a findById method that allows you to retrieve a record from the table by its ID. Let’s add another method called find that takes two arguments:

  • the column to search in
  • the value to search for

$results = $authorsTable->find(’email’, ‘tom@example.org’);

Once implemented, the find method will select all the records where the email column is set to tom@example.org using the query SELECT * FROM author WHERE email = ‘tom@example.org’Because the DatabaseTable class already contains the query function, we can implement the find function in a similar way to the current findyById function:

public function findById($value) {

       $query = ‘SELECT * FROM ‘ . $this->table . ‘ WHERE ‘ .

      $this->primaryKey . ‘ = :primaryKey’;

$parameters = [

        ‘primaryKey’ => $value

    ];

$query = $this->query($query, $parameters);

       return $query->fetch();

  }

public function find($column, $value) {

      $query = ‘SELECT * FROM ‘ . $this->table . ‘ WHERE ‘ .

      $column . ‘ = :value’;

$parameters = [

        ‘value’ => $value

   ];

$query = $this->query($query, $parameters);

    return $query->fetchAll();

}

There are two differences, but the code is very similar. Instead of searching using the column name from $this->primaryKey, the column name is provided by the argument $column, and the return statement uses $query->fetchAll() to return all the records as an array. The $query->fetch function will only return one record. If we’re searching any column for any value, it’s possible that multiple records may have the same value. The fetchAll method is used to return all the matched records. Ideally, we want a generic find function that we can use anywhere we’d like to search the database for records that have a value set for a specified column. Now that the find function has been added, we can use it to determine whether an email address already exists in the database:

if (count($authorsTable->find(’email’, $author[’email’]))> 0) {

$valid = false;

$errors[] = ‘That email address is already registered’;

}

The count function can be used to tally the number of records returned by the find method. If it’s greater than zero (> 0), there’s already a record in the system with the email address being searched for, and you can display an error accordingly. The only issue with this approach is that it’s case sensitive. If the user has already registered with tom@example.org and then re-registers using TOM@EXAMPLE.ORG, these will be seen as different email addresses. In order to correctly determine whether tom@example.org has already signed up, the email address can be stored in the database in lowercase, and searched using the lowercase version. The complete registerUser method now looks like this:

public function registerUser() {

$author = $_POST[‘author’];

// Assume the data is valid to begin with

$valid = true;

$errors = [];

// But if any of the fields have been left blank

// set $valid to false

if (empty($author[‘name’])) {

    $valid = false;

     $errors[] = ‘Name cannot be blank’;

}

if (empty($author[’email’])) {

    $valid = false;

    $errors[] = ‘Email cannot be blank’;

}

else if (filter_var($author[’email’],

     FILTER_VALIDATE_EMAIL) == false) {

         $valid = false;

         $errors[] = ‘Invalid email address’;

}

else { // If the email is not blank and valid:

// convert the email to lowercase

$author[’email’] = strtolower($author[’email’]);

// Search for the lowercase version of $author[’email’]

        if (count($this->authorsTable->

find(’email’, $author[’email’])) > 0) {

           $valid = false;

           $errors[] = ‘That email address is already registered’;

          }

}

if (empty($author[‘password’])) {

      $valid = false;

     $errors[] = ‘Password cannot be blank’;

}

// If $valid is still true, no fields were blank

// and the data can be added

if ($valid == true) {

// When submitted, the $author variable now contains a

// lowercase value for email

$this->authorsTable->save($author);

header(‘Location: /author/success’);

} else {

// If the data is not valid, show the form again

   return [‘template’ => ‘register.html.php’,

   ‘title’ => ‘Register an account’,

   ‘variables’ => [

               ‘errors’ => $errors,

              ‘author’ => $author

               ]

      ];

    }

}

By converting the email address to lowercase, when inserting the data and when searching for it, we can deal with email addresses in a case-insensitive manner.

Email Addresses and Case-sensitivity

Technically, email addresses can be case-sensitive. However, this is discouraged, and for all practical applications it’s safe to assume that email addresses are not case sensitive. No major email provider allows case-sensitive email addresses. The alternative is hoping that users always type in their email address with the same case, which is not a good assumption to make.

Securely Storing Passwords

Now that the validation has been added, it’s possible for anyone who enters valid data into the form to sign up and have their information added to the database. Go ahead and add some test users and check that it’s working correctly by verifying that the record has been added to the author table. Using the approach above, if someone types “mypassword123” into the password field, that’s what will be stored in the database. We might think that’s not a problem, since only we have access to the database, and we’re not going misuse the information. But would we really want the developers of every website we use to know our password?

If our website gets hacked, it’s then possible for the hacker to see all of our users’ passwords, and because people are forgetful, they tend to use the same password for every website they visit. Someone with access to someone else’s email/password combination could, therefore, do quite a bit of damage beyond accessing the account on this one site we just built—such as reading their email or accessing their PayPal account. A good website developer will help protect their users from this kind of attack. The most common method of achieving this is using a “one way hashing function”. A hashing function takes a string like mypassword123 and converts it to an encrypted version of the string, known as a hash. For example, mypassword123 would be hashed and produce a seemingly random string of numbers and letters such as 9c87baa223f464954940f859bcf2e233To convert a string to a hash, we can use one of several available hashing functions built into PHP, including md5 and sha1. Using these functions is simple:

echo md5(‘mypassword123’);

// prints 9c87baa223f464954940f859bcf2e233

This isn’t “encryption” in the true sense. There’s no way to decrypt that seemingly random string of letters and numbers back into mypassword123One method of storing passwords is to store these hashes in the database. When a user types “mypassword123” into the password field, 9c87baa223f464954940f859bcf2e233 is stored in the password column in the database instead of mypassword123Now, if someone does manage to gain access to the database, all they’ll see is a list of names and hashes. For example:

Kevin 9c87baa223f464954940f859bcf2e233

Laura 47bce5c74f589f4867dbd57e9ca9f808

Tom 9c87baa223f464954940f859bcf2e233

Jane 8d6e8d4897a32c5d011a89346477fb07

This solves the problem of someone with access to the database being able to read everyone’s password. However, it’s not perfect. What do you know about Kevin and Tom’s passwords? Looking at the list you can see that they’re the same! If you can work out Kevin’s password, you’ll also know Tom’s. And, what’s worse, we actually know what the password is, because we already discovered that 9c87baa223f464954940f859bcf2e233 is the hash for mypassword123. Because people all use the same common passwords, hackers will generate the hashes for common passwords in order to quickly work out which users are using them. Once you know that the hash for password is 5f4dcc3b5aa765d61d8327deb882cf99, you can query the database for any user who has that hash and you’ll know their password is password. Do this for the top 100, 200 or 1000 passwords, and on a large site you’ll work out dozens of real email/password combinations.

You’ll have heard about the importance of using a secure, not easily guessable password, and this is one of the many reasons why. If your password is uncommon, hackers won’t have generated a hash for it, and won’t be able to easily work out what your password is! There are several methods for solving this problem with duplicated hashes, but there’s a lot to consider, and making a truly secure password hash is more difficult than it seems. If you want to learn more about the theory for solving this, take a look at the SitePoint article “Password Hashing in PHP”.

Luckily for us, PHP includes a very secure way of storing passwords. It’s at least as good as any solution developers will come up with, and avoids developers like us needing to fully understand the security problems that can occur. For this reason, it’s strongly recommended to use the inbuilt PHP algorithm for hashing passwords rather than to create your own. Now that you understand the importance and the theory behind password hashing, let’s put it into practice. PHP contains two functions, password_hash and password_verify. For now, we’re only interested in password_hash. We’ll use password_verify  when we’re checking to see whether someone entered the correct username and password when logging in. This is how we can hash a password using the password_hash function:

$hash = password_hash($password, PASSWORD_DEFAULT);

$password stores the text of the password being hashed, and PASSWORD_DEFAULT is the algorithm to use. It’s generally best to leave it up to the PHP developers, as this will choose the best algorithm currently available. (At the time of writing, this is an algorithm known as bcrypt, but it may change over time.) If we run the code above with a password such as “mypassword123” and echo the $hash variable, we’ll see something like this:

$2y$10$XPtbphrRABcV95GxoeAk.OeI8tPgaypkKicBUhX/YbC9QYSSoowRq

We say “something like”, because each time you run the function, you’ll get a different result. Even if you use “mypassword123” as the password each time, you’ll get a different hash as a result. If two people have the same password, different hashes will be stored in the database. When we added the password column to the database table, we said to make it 255 characters. This is because hashes can be long, and if the default algorithm changes, they may grow in size. Let’s implement the password_hash function in the registration form. It’s surprisingly easy:

// …

if ($valid == true) {

// Hash the password before saving it in the database

$author[‘password’] = password_hash($author[‘password’],

PASSWORD_DEFAULT);

  // When submitted, the $author variable now contains a

// lowercase value for email and a hashed password

   $this->authorsTable->save($author);

  header(‘Location: /author/success’);

  }

// …

The password value in the $author array is replaced with the hashed version. Now, when the data is saved, the hashed password is stored in the database, instead of the value mypassword123 (or whatever was entered into the form).

Registration Complete

The final version of the Register controller looks like this:

Registration-Validation-Email2

<?php

namespace Ijdb\Controllers;

use \Ninja\DatabaseTable;

class Register

{

   private $authorsTable;

    public function __construct(DatabaseTable $authorsTable)

{

        $this->authorsTable = $authorsTable;

}

public function registrationForm()

{

    return [‘template’ => ‘register.html.php’,

    ‘title’ => ‘Register an account’];

}

public function success()

{

          return [‘template’ => ‘registersuccess.html.php’,

                     ‘title’ => ‘Registration Successful’];

}

public function registerUser()

{

$author = $_POST[‘author’];

// Assume the data is valid to begin with

  $valid = true;

  $errors = [];

// But if any of the fields have been left blank

// set $valid to false

if (empty($author[‘name’])) {

     $valid = false;

     $errors[] = ‘Name cannot be blank’;

}

if (empty($author[’email’])) {

    $valid = false;

    $errors[] = ‘Email cannot be blank’;

} elseif (filter_var($author[’email’],

FILTER_VALIDATE_EMAIL) == false) {

     $valid = false;

    $errors[] = ‘Invalid email address’;

} else { // If the email is not blank and valid:

// convert the email to lowercase

$author[’email’] = strtolower($author[’email’]);

// Search for the lowercase version of $author[’email’]

if (count($this->authorsTable->find(’email’,

  $author[’email’])) > 0) {

        $valid = false;

         $errors[] = ‘That email address is already registered’;

        }

}

if (empty($author[‘password’])) {

    $valid = false;

    $errors[] = ‘Password cannot be blank’;

}

// If $valid is still true, no fields were blank

// and the data can be added

if ($valid == true) {

// Hash the password before saving it in the database

      $author[‘password’] =

password_hash($author[‘password’],

   PASSWORD_DEFAULT);

// When submitted, the $author variable now contains a

// lowercase value for email and a hashed password

       $this->authorsTable->save($author);

header(‘Location: /author/success’);

} else {

// If the data is not valid, show the form again

      return [‘template’ => ‘register.html.php’,

            ‘title’ => ‘Register an account’, ‘variables’ => [

             ‘errors’ => $errors, ‘author’ => $author

            ]

      ];

           }

     }

}

we showed you how to add a new controller to the website, allow users to sign up for accounts, validate the data that’s entered into a form, and how to store passwords securely.

Cookies, Sessions, and Access Control

We showed you how users can register accounts on the website. Now it’s time to make those accounts functional, so that users can log in to the website. The process is familiar to web users: they enter a username and password, and get access to content that’s unique to their account. Although a familiar process from the perspective of someone using the website, for a developer, building a website that allows logging in can seem daunting at first. By its nature, HTTP is stateless. You connect to a website, the server gives you a file. As you’ve already seen, you can send data from the browser to the server using GET variables and HTML forms. However, the information is provided to a single page, and is only available when the browser provides GET (or POST) variables.

For a login system, the user will need to send their username and password to the server once, and then maintain a “logged-in” state on every subsequent page request. Although this information could be sent via URL parameters or HTML forms, it would need to be provided to each page. From a user’s point of view, entering their username and password each time they visit a different page is time consuming and inefficient.

Two technologies, cookies and sessions, can be used to store information about a particular user between pages. Cookies and sessions are two of those mysterious technologies that are almost always made out to be more intimidating and complex than they really are. Now we’ll debunk those myths by explaining in simple language what they are, how they work, and what they can do for you. We’ll also provide practical examples to demonstrate each. Finally, we’ll use these new tools to give our newly registered users the ability to navigate around the website and post jokes associated with their account.

Cookies

Most computer programs these days preserve some form of state, whether it be the position of the application window, or the names of the last five files you worked with. The values are usually stored in a small file on your system so they can be read back the next time the program is run. When web developers took web design to the next level, and moved from static pages to complete, interactive online applications, there was a need for similar functionality in web browsers. And thus, cookies were born. A cookie is a name–value pair, an array of sorts, associated with a given website, and stored on the computer that runs the client (browser). Once a cookie is set by a website, all future page requests to that same site will send the information stored in the cookie back to the website until it expires (or becomes out of date). Other websites are unable to access the cookies set by your site, and vice versa. So, contrary to popular belief, they’re a relatively safe place to store personal information. Cookies in and of themselves are incapable of compromising a user’s privacy.

The life cycle of a PHP-generated cookie is as follows:

  1. First, a web browser requests a URL that corresponds to a PHP script. Within that script is a call to the setcookie function that’s built into PHP.
  2. The page produced by the PHP script is sent back to the browser, along with an HTTP set-cookie header that contains the name (for example, mycookie) and the value of the cookie to be set.
  3. When it receives this HTTP header, the browser creates and stores the specified value as a cookie named mycookie.
  4. Subsequent page requests to that website contain an HTTP cookie header that sends the name–value pair (mycookie=value) to the script requested.
  5. Upon receipt of a page request with a cookie header, PHP automatically creates an entry in the $_COOKIE array with the name of the cookie ($_COOKIE[‘mycookie’]) and its value.

In other words, the PHP setcookie function lets us set a variable that will automatically be set by subsequent page requests from the same browser. Each browser (or website visitor) can have a different value set in the same cookie. Before we examine an actual example, let’s take a close look at the setcookie function:

bool setcookie ( string $name [, string $value = “” [,

int $expire = 0 [, string $path = “” [,

string $domain = “” [, bool $secure = false [,

bool $httponly = false ]]]]]] )

Square Brackets

The square brackets ([…]) indicate arguments of the function that are optional. You can omit these arguments and some defaults will be set by PHP automatically.

Like the header function we saw before, the setcookie function adds HTTP headers to the page, and thus must be called before any of the actual page content is sent. Any attempt to call setcookie after page content has been sent to the browser will produce a PHP error message. Typically, therefore, we’ll use these functions in our controller script before any actual output is sent (by an included PHP template, for example). The only required parameter for this function is the name parameter, which specifies the name of the cookie. Calling setcookie with only the name parameter will actually delete the cookie that’s stored on the browser, if it exists. The value parameter allows you to create a new cookie, or modify the value stored in an existing one.

By default, cookies will remain stored by the browser, and thus will continue to be sent with page requests until the browser is closed by the user. If you want the cookie to persist beyond the current browser session, you must set the expiryTime parameter to specify the number of seconds from January 1, 1970 to the time at which you want the cookie to be deleted automatically. Although that sounds arbitrary, this is a very common time format known as a unix timestamp, and PHP has inbuilt functions for calculating this so you don’t need to do it yourself. The current time in this format can be obtained using the PHP time function. Thus, a cookie could be set to expire in one hour, for example, by setting expiryTime to time() + 3600. To delete a cookie that has a preset expiry time, change this expiry time to represent a point in the past (such as one year ago: time() – 3600 * 24 * 365). Here are two examples showing these techniques in practice:

// Set a cookie to expire in 1 year

setcookie(‘mycookie’, ‘somevalue’,

time() + 3600 * 24 * 365);

// Delete it

setcookie(‘mycookie’, ”,

time() – 3600 * 24 * 365);

UNIX Timestamps

Unix timestamps will change in the future. In the year 2038, they’ll suffer a similar problem to the Y2K bug, because the data type used to store them can’t store a high enough number to count the seconds after January 19, 2038. This may seem a long way off at the moment, but it’s worth keeping in mind. To make a cookie persistent, we must set an expiration date. We might be inclined to make this a date very far in the future so that it effectively never expires—perhaps the current date + 20 years. Doing this calculation will break if our script executes after January 19, 2018, because the expiration date will be after 2038 and the cookie will not be set. Selecting 10 years’ time will break our program in 2028.We recommend we select one year, so that we’re safe until 2037—by which time there’ll be a proper fix in place.

The path parameter lets us restrict access to the cookie to a given path on our server. For instance, if we set a path of ‘/admin/’ for a cookie, only requests for pages in the admin directory (and its subdirectories) will include the cookie as part of the request. Note the trailing /, which prevents scripts in other directories beginning with /admin (such as /adminfake/) from accessing the cookie. This is helpful if you’re sharing a server with other users, and each user has a web home directory. It allows us to set cookies without exposing our visitors’ data to the scripts of other users on our server. The domain parameter serves a similar purpose: it restricts the cookie’s access to a given domain. By default, a cookie will be returned only to the host from which it was originally sent. Large companies, however, commonly have several host names for their web presence (for example, www.example.com and support.example.com). To create a cookie that’s accessible by pages on both servers, we would set the domain parameter to ‘.example.com’. Note the leading ., which allows anything ending in .example.com to access the cookie. However, cookies are never shared across different domains. Setting the domain parameter to example2.com won’t make the cookie available on another site.

The secure parameter, when set to 1, indicates that the cookie should be sent only with page requests that happen over a secure (SSL) connection (that is, with a URL that starts with https://). The httpOnly parameter, when set to 1, tells the browser to prevent JavaScript code on our site from seeing the cookie that we’re setting. Normally, the JavaScript code we include in our site can read the cookies that have been set by the server for the current page. While this can be useful in some cases, it also puts the data stored in our cookies at risk should an attacker figure out a way to inject malicious JavaScript code into our site. This code could then read your users’ potentially sensitive cookie data and do unspeakable things with them. If we set httpOnly to 1, the cookie we’re setting will be sent to our PHP scripts as usual, but will be invisible to JavaScript code running on our site.

While all parameters except name are optional, we must specify values for earlier parameters if we want to specify values for later ones. For instance, to call setcookie with a domain value, we also need to specify a value for the expiryTime parameter. To omit parameters that require a value, we can set string parameters (value, path, domain) to (the empty string) and numerical parameters (expiryTime, secure) to 0Let’s now look at an example of cookies in use. Imagine we want to display a special welcome message to people on their first visit to our site. We could use a cookie to count the number of times a user has been to our site before, and only display the message when the cookie hasn’t been set. Here’s the code:

Sessions-Cookie

<?php

if (!isset($_COOKIE[‘visits’])) {

         $_COOKIE[‘visits’] = 0;

}

$visits = $_COOKIE[‘visits’] + 1;

setcookie(‘visits’, $visits, time() + 3600 * 24 * 365);

if ($visits > 1) {

       echo “This is visit number $visits.”;

} else {

// First visit

echo ‘Welcome to our website! Click here for a tour!’;

}

This code starts by checking if $_COOKIE[‘visits’] is set. If it isn’t, it means the visits cookie has yet to be set in the user’s browser. To handle this special case, we set $_COOKIE[‘visits’] to 0. The rest of our code can then safely assume that $_COOKIE[‘visits’] contains the number of previous visits the user has made to the site. Next, to work out the number of this visit, we take $_COOKIE[‘visits’] and add the value 1. This variable, $visits, will be used by our PHP template. Finally, we use setcookie to set the visits cookie to reflect the new number of visits. We set this cookie to expire in one year’s time. The images below shows what this example looks like the first time a browser visits the page and after the second visit.

The first visit

The second visit

Before we go overboard using cookies, we have to be aware that browsers place a limit on the number and size of cookies allowed per website. Some browsers will start deleting old cookies to make room for new ones after we’ve set 20 cookies from our site. Other browsers will allow up to 50 cookies per site, but will reject new cookies beyond this limit. Browsers also enforce a maximum combined size for all cookies from all websites, so an especially cookie-heavy site might cause our own site’s cookies to be deleted.

Each time someone visits our website, all of the cookies are sent to the web server. If we store a lot of information in the cookie, it can slow down the responsiveness of the website, because extra data must be transferred with each page view. Cookies can also be read by anyone who gains access to the computer they’re stored on, so cookies are only as secure as the computer being used to view the website. For these reasons, we should do our best to keep the number and size of the cookies our site creates to a minimum.

PHP Sessions

Because of the limitations we’ve just described, cookies are inappropriate for storing large amounts of information. If we run an ecommerce website that uses cookies to store items in shopping carts as users make their way through our site, it can be a huge problem. The bigger a customer’s order, the more likely it will run afoul of a browser’s cookie restrictions. Sessions were developed in PHP as the solution to this issue. Instead of storing all our (possibly large) data as cookies in our visitor’s web browser, sessions let us store the data on our web server. The only value that’s stored in the browser is a single cookie containing the user’s session ID—a long string of letters and numbers that serves to identify that user uniquely for the duration of their visit to our site. It’s a variable for which PHP watches on subsequent page requests, and uses to load the stored data that’s associated with that session.

Unless configured otherwise, a PHP session automatically sets a cookie in the user’s browser that contains the session ID. The browser then sends that cookie, along with every request for a page from our site, so that PHP can determine which of the potentially many current sessions the request belongs to. Using a set of temporary files that are stored on the web server, PHP keeps track of the variables that have been registered in each session, along with their values. Before you can go ahead and use the spiffy session-management features in PHP, we should ensure that the relevant section of our php.ini file has been set up properly. If we’re using the Homestead box , it’s safe to assume this has been done for us. Otherwise, we need to open our php.ini file in a text editor and look for the section marked [Session]. Beneath it, we’ll find around 20 options that begin with the word session. Most of them are fine as they are, but there are a few crucial ones we’ll want to check:

session.save_handler   = files
session.save_path      = "/tmp"
session.use_cookies    = 1

Finding php.ini

The php.ini configuration applies globally to our PHP scripts, and can be stored on the server in various locations. On the Homestead Improved box we’re using, it’s located at /etc/php/7.1/php.ini. This location may change if you’re using a newer version of the virtual machine, and will almost certainly be different on a real web server. To find out the location of the configuration file that’s in use, run this PHP script:

<?php

echo phpinfo();

You’ll see a line near the top that says “Loaded Configuration File”, which is the path to the php.ini configuration file that’s in use on the website.

session.save_path tells PHP where to create the temporary files used to track sessions. It must be set to a directory that exists on the system, or we’ll receive ugly error messages when we try to create a session on one of our pages. On macOS and Linux systems, /tmp is a popular choice. In Windows, we could use C:\WINDOWS\TEMP, or some other directory if you prefer (D:\PHP\SESSIONS, for example). With these adjustments made, we restart our web server software to allow the changes to take effect. This directory must be writable by the user that PHP is running as. We’re now ready to start working with PHP sessions. Before we jump into an example, let’s quickly look at the most common session management functions in PHP. To tell PHP to look for a session ID, or start a new session if none is found, we simply call session_start. If an existing session ID is found when this function is called, PHP restores the variables that belong to that session. Since this function attempts to create a cookie, it must come before any page content is sent to the browser, just as we saw for setcookie above:

session_start();

To create a session variable that will be available on all pages in the site when accessed by the current user, we set a value in the special $_SESSION array. For example, the following will store the variable called password in the current session. The $_SESSION variable will be empty until we’ve called session_start(), so we need to ensure we don’t read or write to it before the session has been started. It’s useful to have an understanding of how sessions work behind the scenes. Once we call session_start(), it actually creates a cookie with a unique ID to represent each individual user. For example, the first person on the web page may be user 1, the second 2, and so on.

Then, when they visit the next page, their ID is sent back to the website, and when the session is started, all the information stored for that user is retrieved. For example, all the information stored for ID 1 represents user 1, and the information for session ID 2 represents user 2. This allows sessions to keep track of different information for each user of the website. In practice, IDs aren’t simple sequential numbers like 1, 2 or 3. They’re complex, difficult to guess strings of seemingly random numbers and letters. If sessions were easy to guess, hackers could easily pretend to be each user of the website by changing the ID stored in their session cookie! After the session has been started, we can treat the $_SESSION variable like a normal array, reading and writing values to it:

$_SESSION[‘password’] = ‘mypassword’;

To remove a variable from the current session, we can use PHP’s unset function:

unset($_SESSION[‘password’]);

Finally, if we want to end the current session and delete all registered variables in the process, clear all the stored values and use session_destroy:

$_SESSION = [];

session_destroy();

For more detailed information on these and the other session-management functions in PHP, see the relevant section of the PHP Manual. Now that we have these basic functions under our belt, let’s put them to work in a simple example.

Counting Visits with Sessions

We showed you how to use cookies to track the number of times someone had visited the page, and the same thing can be done with sessions:

Sessions-Count

<?php

session_start();

if (!isset($_SESSION[‘visits’])) {

      $_SESSION[‘visits’] = 0;

}

$_SESSION[‘visits’] = $_SESSION[‘visits’] + 1;

if ($_SESSION[‘visits’] > 1) {

      echo ‘This is visit number ‘ . $_SESSION[‘visits’];

} else {

// First visit

     echo ‘Welcome to my website! Click here for a tour!’;

}

$_SESSION is used in place of $_COOKIE, and you’ll notice the code is a little simpler than the code used for cookies. For the cookie, we needed to calculate the lifetime and set an expiration time. Sessions are simpler: no expiration time is required, but any data stored in the session is lost when the browser is closed.

Access Control

One of the most common reasons for building a database-driven website is that it allows users to interact with a site from any web browser, anywhere! But in a world where roaming bands of jubilant hackers will fill our site with viruses and pornography, we need to stop and think about the security of our website. At the very least, we’ll want to require username and password authentication before a visitor can post anything to the website. There are two main ways of doing this:

  • configure our web server software to require a valid login for the relevant pages
  • use PHP to prompt the user and check the login credentials as appropriate

If we have access to our web server’s configuration, the first option is often the easiest to set up, but the second is by far the more flexible. With PHP, we can design our own login form, and even embed it into the layout of our site if we wish. PHP also makes it easy to change the credentials required to gain access, or manage a database of authorized users, each with their own credentials and privileges. In this section, we’ll enhance our joke database site to protect sensitive features with username/password-based authentication. In order to control which users can do what, we’ll build a sophisticated role-based access control system. “What does all this have to do with cookies and sessions?” you might wonder. Well, rather than prompting our users for login credentials every time they want to view a confidential page or perform a sensitive action, we can use PHP sessions to hold on to those credentials throughout their visit to our site.

Logging In

We showed you how users could register accounts on the website and have their password stored securely. The next step is to allow those registered users to log in and post jokes to the website. Obviously, access control is a feature that will be handy in many different PHP projects and on different pages on the website. Therefore, like our database connection code and DatabaseTable class, it makes sense to write as much of our access control code as possible as a shared class, so that we can then reuse it throughout the website and in future projects.

The general process for “logging in” consists of the user supplying an email address and password. If the database contains a matching author, it means the user filled out the login form correctly and we have to log in the user. But what exactly does “log in the user” mean? There are two approaches to this, both of which involve using PHP sessions:

  1. We can log in the user by setting a session variable as a “flag” (for example, $_SESSION[‘userid’] = $userId). On future requests, we can just check if this variable is set and use it to read the ID of the logged-in user.
  2. We can store the supplied email address and password in the session, and then on future requests, we can check if these variables are set. If they are, we can check the values in the session against the values in the database.

The first option will give better performance, since the user’s credentials are only checked once—when the login form is submitted. The second option offers greater security, since the user’s credentials are checked against the database every time a sensitive page is requested. In general, the more secure option is preferable, since it allows us to remove authors from the site even while they’re logged in. Otherwise, once a user is logged in, they’ll stay logged in for as long as their PHP session remains active. That’s a steep price to pay for a little extra performance. The theory behind this is simple to implement, but it’s made more difficult because the password field in the database doesn’t store the password in plain text, as typed by the user. Instead, it stores a hashed password like this:

$2y$10$XPtbphrRABcV95GxoeAk.OeI8tPgaypkKicBUhX/YbC9QYSSoowRq

There’s no way to decrypt the password to do the comparison, but because we used password_hash to hash the password, we can use password_verify to check it. password_verify takes two arguments: the plain text password to check and the hashed password from the database. It returns true or false depending on whether the password is correct. To check the password, we’ll need the hash password from the database before being able to use it with password_verify. Luckily, thanks to the DatabaseTable class and the existing $authorsTable variable, it’s easy for us to look up the hashed password of a user by using their email address:

$author = $authorsTable->find(’email’, strtolower($_POST[’email’]);

Once the user’s information is stored in $author, it’s possible to check the password using the password_verify function, like so:

if (!empty($author) &&

password_verify($_POST[‘password’],

       $author[0][‘password’])) {

   // Login successful

}

else {

// Passwords don’t match, an error occurred  .    

}

Note the [0] after $author. That’s because the find function may return more than one record. We need to specifically read the first record returned. The first condition in the if statement checks to see if an author has been retrieved from the database. If one has, the second condition will check that the password entered by the user matches the one in the database. The order is important here. PHP will run and (&&) conditions left to right, and stop when one evaluates to false. If the order was reversed and the password check was done before !empty($author), an error might occur, because $author might not contain an array with a password key! By putting the !empty($author) check first, we know that something is set in the password key for the $author array.

Hashing

You may have thought that we could use this:

if (password_hash($_POST[‘password’],

PASSWORD_DEFAULT) == $author[0][‘password’]) { …

However, password_hash creates a different hash each time it’s called, even if it’s called with the same password string! We must use password_verify to check the password.

Once the user has entered the email address and password, they can be “logged in” by setting the session variable. After checking the password was correct using password_verify, it’s time to write some data to the session. There are various options here. We could just store the user ID or the email address of the person who’s been logged in. However, it’s good practice to store both the login name and password in the session and check them both on each page view. That way, if the user is logged in on two different computers and the password is changed, they’ll be logged out and required to log back in.

This is a useful security feature for users, since if one of those logged in locations were not really the user, someone having managed to get unauthorized access to their account, the attacker would be logged out as soon as the password was changed. Without storing the password in the session, the attacker could log in once, and as long as the browser was left open, they’d maintain access to the user’s account. One method of achieving this is to store both the email address and password in the session:

$_SESSION[’email’] = $_POST[’email’];

$_SESSION[‘password’] = $_POST[‘password’];

Then, on each page view, we’d check the information in the session against the database:

$author = $authorsTable->find(’email’, strtolower($_SESSION[’email’]))[0];

if (!empty($author) &&

password_verify($_SESSION[‘password’],

$author[‘password’])) {

     // Display password protected content

}

else {

// Display an error message and clear the session

// logging the user out

}

This is theoretically what we want to do. With this approach, if the password is changed in the database, or the author is removed from the database, the user will be logged out. However, there’s an obvious security issue here. Although sessions are stored on the server, if someone did gain access to our web server, they could see the plain text password of any logged-in users, completely forfeiting the benefit of hashing the password in the first place.

To avoid storing the plain text password of the logged-in users in the session, we’ll need to adjust the logic slightly. Instead of storing the plain text password in the session, it’s better to store the password hash from the database in the session. If someone is able to read the session data from the server, they’ll only see the hash, not the real password! To store the hash in the session, we can use the following code:

$_SESSION[’email’] = $_POST[’email’];

$_SESSION[‘password’] = $author[‘password’];

With the email address and hash stored, we can check the values from the database, and if either the email address or password stored in the database have changed, the user can be logged out. On each page, we’ll need to run this code:

$author = $authorsTable->find(’email’, strtolower($_SESSION[’email’]));

if (!empty($author) && $author[0][‘password’] ===

  $_SESSION[‘password’]) {

   // Display password protected content

}

else {

      // Display an error message and log the user out

}

The code above does three things:

  1. It searches the database for the user with the email address from the session, which would have been set when the user submitted the login form.
  2. It checks a record has been retrieved from the database. After all, it’s possible the user entered an email address that doesn’t actually exist in the database.
  3. It compares the password in the session to the password that’s currently in the database. If it’s changed between logging in and viewing this page, the user will be logged out.

As this check will need to be done on every page we want to password protect, let’s move it into a class for easy reuse. We’ll need two methods for now:

  • one that’s called when the user tries to log in with an email address and password
  • one that’s called on each page to check whether the user is logged in or not (with the check that ensures the password hasn’t changed in the database)

Since this is something that’s going to be useful on any website we build, we’ll place it in the Ninja framework namespace:

<?php

namespace Ninja;

class Authentication

{

    private $users;

    private $usernameColumn;

    private $passwordColumn;

public function __construct(DatabaseTable $users, $usernameColumn, $passwordColumn)

{

   session_start();

   $this->users = $users;

   $this->usernameColumn = $usernameColumn;

    $this->passwordColumn = $passwordColumn;

}

public function login($username, $password)

{

     $user = $this->users->find($this->usernameColumn, strtolower($username));

if (!empty($user) && password_verify($password, $user[0][$this->passwordColumn])) {

     session_regenerate_id();

   $_SESSION[‘username’] = $username;

   $_SESSION[‘password’] =

   $user[0][$this->passwordColumn];

   return true;

  } else {

     return false;

     }

}

public function isLoggedIn()

{

  if (empty($_SESSION[‘username’])) {

       return false;

}

$user = $this->users->find($this->usernameColumn, strtolower($_SESSION[‘username’]));

   if (!empty($user) &&

         $user[0][$this->passwordColumn]

         ===$_SESSION[‘password’]) {

        return true;

} else {

             return false;

          }

     }

}

Let’s save this as Authentication.php in the classes/Ninja directory so it can be used later. This is mostly the same code I already showed you with a few minor changes. Firstly, take a look at the constructor. There are three variables required for this class:

  1. a DatabaseTable instance, which is configured for the table that stores user accounts
  2. the name of the column that stores the login names
  3. the name of the column that stores the password

As this class will be useful on multiple websites, we need to code it so that it can be used in as many situations as possible. Although the column names that store the login names and passwords are email and password on this website, on another website the login name might be stored under username or customer_login or any other name you can think of. The same is true for the password. By making these constructor arguments rather than hardcoding them into the class, it’s now possible to use this class on any website, regardless of whether the column names in the database are email and password or something else.

Each time we want to read the password from the database, the class variables are used. The code is slightly more complex: $user[0][$this->passwordColumn] instead of $user[0][‘password’].But the added flexibility of being able to use this on websites that have the password stored in a column with a different name heavily outweighs the small additional complexity in this class. When the Authentication class is created, it starts the session. This avoids us needing to manually call session_start on each page. As long as the Authentication class has been instantiated, a session will have been started. When login or isLoggedIn are called, the session must have been started.

Both login and isLoggedIn return true or false, which we can later call to determine whether the user has entered valid credentials or is already logged in. There’s also an initial check in the isLoggedIn method, ensuring there is data in the session. If not, it returns false, as without a session variable username, the user is not logged in. One final security measure that’s worth implementing is changing the session ID after a successful login. Earlier we mentioned that session IDs should not easily be guessable. Otherwise, hackers could pretend to be someone else, an attack commonly known as session fixation. All the hacker needs to steal someone else’s session is the session ID.

It’s good practice to change the session ID after a successful login just in case someone managed to get hold of the session ID before the user logged in. PHP makes this very easy, and the single function session_regenerate_id does this by picking a new random ID for that user. This can be placed in the if block that runs when the login is successful:

if (!empty($author) && password_verify($password,

$author[0][‘password’])) {

session_regenerate_id();

$_SESSION[’email’] = $email;

$_SESSION[‘password’] = $author[‘password’]; return true;

}

If you follow the logic through, you may have realized that frequently changing the session ID can increase security. In fact, it would be very secure to change the user’s session ID on every page load.

However, doing so causes several practical problems. If someone has different pages open in different tabs, or the website uses a technology called Ajax, they effectively get logged out of one tab when they open another! These problems are worse than the minor security benefit of changing the session ID on every page.

Protected Pages

Now that the authentication class is complete, it’s time to use it in the website. Before creating a login page, let’s secure some of the existing pages so they can’t be viewed unless as user is logged in (or the isLoggedIn function returns true). Currently, we only have the Joke controller. The listJokes method should be visible without logging in, but the facility to add, edit or delete a joke should only be available to users who are logged in. To achieve this, we’ll need to determine whether or not a user is logged in. If they are, the page is displayed as normal. If not, an error message is displayed in its place. We already have the Authentication class, which allows us to determine whether or not someone is logged in. We could pass the $authentication instance into each controller and add a check to each controller action, like so:

public function edit() {

if (!$this->authentication->isLoggedIn()) {

return [‘template’ => ‘error.html’,

‘title’ => ‘You are not authorized to view this page’];

}

else {

// Display the form

}

// …

We’d also require a relevant error.html.php to display an error message such as “You must be logged in to view this page”. Although this approach will work, it will result in repeated code. Every controller action that should only be available to logged-in users would require repeating this same if statement. As you already know, if you find yourself repeating very similar code in multiple places, it’s usually better to move the code so it can be written once and reused. In this case, a better approach is adjusting the router to perform the login check and either use the requested route or display an error page. Firstly, let’s open up IjdbRoutes.php and add ‘login’ => true to each of the routes that we want to secure, joke/edit and joke/delete:

‘joke/edit’ => [

‘POST’ => [

    ‘controller’ => $jokeController,

    ‘action’ => ‘saveEdit’

],

‘GET’ => [

    ‘controller’ => $jokeController,

    ‘action’ => ‘edit’

],

   ‘login’ => true

],

‘joke/delete’ => [

  ‘POST’ => [

     ‘controller’ => $jokeController,

     ‘action’ => ‘delete’

],

‘login’ => true

],

Next, we’ll add a new method, getAuthentication. This method will return the Authentication object used by this website. By placing this method here, it allows us to configure the Authentication class differently on different websites. This object needs to be used in the EntryPoint class, but we need to avoid constructing it there, as the table and column names will be different on each website we build.By constructing the object in the IjdbRoutes class, it can be changed for each website we build.

public function getAuthentication() {

$authorsTable = new \Ninja\DatabaseTable($pdo,

‘author’, ‘id’);

return new \Ninja\Authentication($authorsTable, ’email’, ‘password’);

}

As the Authentication class requires an instance of DatabaseTable representing the table that stores the logins, I’ve copied the line that creates the $authorsTable object. This copy/paste approach is not ideal from either a performance or maintainability point of view. It’s better to have a single instance representing the authors table. To achieve that, move the construction of the database table into the constructor and store it in a class variable:

<?php

namespace Ijdb;

class IjdbRoutes implements \Ninja\Routes {

  private $authorsTable;

  private $jokesTable;

  private $authentication;

public function __construct()

{

  include __DIR__ . ‘/../../includes/DatabaseConnection.php’;

  $this->jokesTable = new \Ninja\DatabaseTable($pdo, ‘joke’, ‘id’);

  $this->authorsTable = new \Ninja\DatabaseTable($pdo, ‘author’, ‘id’);

  $this->authentication =

new \Ninja\Authentication($this->authorsTable, ’email’, ‘password’);

}

   public function getRoutes()

{

  $jokeController =

   new \Ijdb\Controllers\Joke($this->jokesTable, $this->authorsTable);

  $authorController = new \Ijdb\Controllers\Register ($this->authorsTable);

$routes = [

  ‘author/register’ => [

     ‘GET’ => [

       ‘controller’ => $authorController,

       ‘action’ => ‘registrationForm’

],

  ‘POST’ => [

      ‘controller’ => $authorController,

      ‘action’ => ‘registerUser’

    ]

],

  ‘author/success’ => [

    ‘GET’ => [

        ‘controller’ => $authorController,

       ‘action’ => ‘success’

  ]

],

‘joke/edit’ => [

   ‘POST’ => [

       ‘controller’ => $jokeController,

       ‘action’ => ‘saveEdit’

],

  ‘GET’ => [

     ‘controller’ => $jokeController,

     ‘action’ => ‘edit’

 ],

 ‘login’ => true

],

‘joke/delete’ => [

  ‘POST’ => [

      ‘controller’ => $jokeController,

     ‘action’ => ‘delete’

],

‘login’ => true

],

‘joke/list’ => [

  ‘GET’ => [

      ‘controller’ => $jokeController,

       ‘action’ => ‘list’

  ]

],

  ‘  ‘=> [

  ‘GET’ => [

  ‘controller’ => $jokeController, ‘action’ => ‘home’

    ]

    ]

];

return $routes;

}

  public function getAuthentication()

{

  return $this->authentication;

      }

}

For consistency, I’ve also made $jokesTable into a class variable. As the Authentication object might be required by controllers, I’ve moved that into the constructor as well. It then gets returned by the getAuthentication method.

Interfaces and Return Types

Before adding an authentication check to EntryPoint, let’s add getAuthentication to the interface Routes:

<?php

namespace Ninja;

interface Routes

{

public function getRoutes();

public function getAuthentication();

}

With the method getAuthentication added to the interface, any class that implements it must have a method called getAuthentication. When we add the relevant ShopRoutes class for an online shop, it will need to provide an array and an authentication object. Let’s improve the interface slightly. The Routes interface will ensure that the two methods exist in the class. However, what if getAuthentication in the IjdbRoutes class were not to return an Authentication object? If whatever is calling getAuthentication is expecting an Authentication object, perhaps to call the isLoggedIn method, and an instance of DatabaseTable or an array is returned, it will cause an error. As an extra line of defense, it’s possible to type hint a return value. If the method returns something other than the expected type, an error will occur. Let’s amend the interface like so:

<?php

namespace Ninja;

interface Routes

{

public function getRoutes(): array;

public function getAuthentication(): \Ninja\Authentication;

}

We’ve amended the method headers to include a return type appending : \Ninja\ Authentication to the method header. If the method returns anything other than an Authentication object, or doesn’t return anything at all, PHP will display a meaningful error. You’ll notice I’ve also hinted the return type for the getRoutes method: it must return an array. Once we’ve amended the interface, we’ll need to amend the IjdbRoutes class to match:

public function getRoutes(): array {

$jokeController =

new \Ijdb\Controllers\Joke($this->jokesTable, $this->authorsTable);

// …

return $routes;

}

public function getAuthentication(): \Ninja\Authentication { return $this->authentication;

}

The interface is now very explicit. Anyone who wants to build a website using our Ninja framework knows that the Routes class they create for that website must include a method called getRoutes, which returns an array and a method called getAuthentication that returns an Authentication object. An interface like this is very useful if we’re writing code we want others to build on. The interface can act as documentation and give other developers instructions to follow. By writing their code to fit our interface, it will work correctly with our class.

PHP programmers share code all the time, and interfaces like this make doing so a lot easier. We can share our Ninja framework and another developer knows how to use it by writing classes that implement our interface. Interfaces are a very powerful but under-utilized tool that act as bridge between framework code and project-specific code. An interface describes some gaps in the framework code that need to be filled by the project-specific code. Each project can then fill those gaps with code that’s specific to the individual website being built.

Making Use of the Authentication Class

In EntryPoint.php, add a check that looks for the login key in the route array. If it’s set, and it’s set to true, and the user is not logged in, redirect to a login page. Otherwise, display the page as normal:

Sessions-LoginCheck

if (isset($routes[$this->route][‘login’]) &&

isset($routes[$this->route][‘login’]) &&

   !$authentication->isLoggedIn()) {

header(‘location: /login/error’);

}

else {

   $controller = $routes[$this->route]

  [$this->method][‘controller’];

  $action = $routes[$this->route][$this->method] [‘action’];

  $page = $controller->$action();

  $title = $page[‘title’];

if (isset($page[‘variables’])) {

      $output = $this->loadTemplate($page[‘template’], $page[‘variables’]);

}

else {

    $output = $this->loadTemplate($page[‘template’]);

}

include __DIR__ . ‘/../../templates/layout.html.php’;

}

For pages that require users to be logged in, it’s important that the controller action is never called. Consider the following code:

if (isset($routes[$this->route][‘login’]) &&

isset($routes[$this->route][‘login’]) &&

!$authentication->isLoggedIn()) {

header(‘location: /login/error’);

}

$controller = $routes[$this->route][$this->method] [‘controller’];

$action = $routes[$this->route][$this->method]

[‘action’];

$page = $controller->$action();

// …

Without the else statement, this would look like it was working. If we visited joke/delete, we’d get redirected to the login page. However, take a look at what’s happening here: the redirect is being sent, but then the controller action is called. This would be a huge problem: although we’d be redirected to the login page, the relevant DELETE query would still be sent to the database!

Login Error Message

If we test the code above by trying to add a joke, we should see a page of errors, as we’ll have been redirected to /login/error. Let’s create a page at that location to display a more meaningful error message. You should be familiar with the process of adding a page to the website by now. Try adding the error page yourself. If you’re not quite sure or want to be consistent with the provided examples, follow these steps: Firstly, add loginerror.html.php in the templates directory:

<h2>You are not logged in</h2>

<p>You must be logged in to view this page.

<a href=”/login”>Click here to log in</a> or

<a href=”/author/register”>Click here to register an account</a></p>

Now add the controller Login.php in the Ijdb\Controllers directory:

<?php

namespace Ijdb\Controllers;

class Login

{

   public function error()

{

return [‘template’ => ‘loginerror.html.php’, ‘title’ => ‘You are not logged in’];

     }

}

Finally, instantiate the controller and add the route to IjdbRoutes.php:

Sessions-LoginError

public function getRoutes(): array {

  $jokeController = new \Ijdb\Controllers\Joke

  ($this->jokesTable, $this->authorsTable);

  $authorController = new \Ijdb\Controllers\Register ($this->authorsTable);

  $loginController = new \Ijdb\Controllers\Login();

$routes = [

  ‘author/register’ => [

   ‘GET’ => [

         ‘controller’ => $authorController,

       ‘action’ => ‘registrationForm’

],

  ‘POST’ => [

        ‘controller’ => $authorController,

       ‘action’ => ‘registerUser’

  ]

],

// …

‘login/error’ => [

   ‘GET’ => [

      ‘controller’ => $loginController,

      ‘action’ => ‘error’

     ]

]

];

If we visit any page where login is set to true in the $routes array, we’ll see the error page. By adding ‘login’ => true to a route, we now have a quick and easy method of restricting access to pages, and we don’t need to perform this check in every controller action.

Creating a Login Form

Now that the login check is in place and we know that it works, it’s time to build a form for logging in. As it stands, there’s no way to add or edit a joke, because there’s no facility to log in. We already created the Login controller, and we’ll need to add two methods—one for displaying the form, and one for handling the submission. As the login form will need to call the login method we created in the Authentication class, it will need the Authentication class as a constructor argument and class variable:

<?php

namespace Ijdb\Controllers;

class Login

{

private $authentication;

   public function __construct(\Ninja\Authentication $authentication)

{

   $this->authentication = $authentication;

  }

  public function error()

{

    return [‘template’ => ‘loginerror.html.php’, ‘title’ => ‘You are not logged in’];

   }

}

Once the constructor has been added, the function to display the form and check the login credentials can be added. Just displaying the form is simple:

public function loginForm() {

return [‘template’ => ‘login.html.php’, ‘title’ => ‘Log In’];

}

Now we can add the template login.html.php:

<?php

if (isset($error)):

echo ‘<div class=”errors”>’ . $error . ‘</div>’;

endif;

?>

<form method=”post” action=””>

<label for=”email”>Your email address</label> <input type=”text” id=”email” name=”email”>

<label for=”password”>Your password</label>

<input type=”password” id=”password” name=”password”>

<input type=”submit” name=”login” value=”Log in”> </form>

<p>Don’t have an account?

<a href=”/author/register”>Click here to register an account</a></p>

We’ve included some PHP code for displaying an error message in case logging in is not successful. Finally, amend IjdbRoutes.php to add the route. We’ll also need to provide the login controller with the $authentication instance:

public function getRoutes(): array {

// …

$loginController = new \Ijdb\Controllers\ Login($this->authentication);

$routes = [

// …

‘login’ => [

‘GET’ => [

‘controller’ => $loginController,

‘action’ => ‘loginForm’

    ]

],

//…

];

If we visit http://192.168.10.10/login, we’ll see a familiar login form with text boxes for entering our email address and password. As there’s no POST route or any logic to process the form, it won’t do anything yet.

Let’s add the POST action and a simple page for displaying a “Login Successful” message:

IjdbRoutes.php:

$routes = [

// …

‘login’ => [

‘GET’ => [

‘controller’ => $loginController,

‘action’ => ‘loginForm’

],

‘POST’ => [

‘controller’ => $loginController,

‘action’ => ‘processLogin’

]

],

‘login/success’ => [

‘GET’ => [

‘controller’ => $loginController,

‘action’ => ‘success’

],

‘login’ => true

]

We’ve already built the functionality for logging in, and checking whether a user is logged in, in the Authentication class. This is the difficult part of the login process, and we can call the existing login method with data that’s been entered on a form by the user. If the login method returns true, their details were correct and we can redirect to a page that says “Login successful”. Otherwise, show an error.

Login.php:

public function processLogin() {

     if ($this->authentication->login($_POST[’email’], $_POST[‘password’])) {

header(‘location: /login/success’);

}

else {

     return [‘template’ => ‘login.html.php’, ‘title’ => ‘Log In’, ‘variables’ => [

‘error’ => ‘Invalid username/password.’ ]

     ];

  }

}

public function success() {

    return [‘template’ => ‘loginsuccess.html.php’, ‘title’ => ‘Login Successful’];

}

loginsuccess.html.php:

  <h2>Login Successful</h2>

 <p>You are now logged in.</p>

You can find this code in Sessions-LoginForm.

If you haven’t already, create an account using the registration form we created, Visit http://192.168.10.10/login and log in. If you’re logged in, you’ll be able to add, edit and delete jokes. If you’re not logged in, an error message is shown.

Privacy Pointers

It would be possible to display different messages to the user depending on why their login failed—for example, “Invalid email address” when the email address doesn’t exist, and “Invalid password” when the email address is registered but the passwords don’t match. Although this helps the user by letting them see what went wrong, it’s a privacy breach. Anyone can type in someone else’s email address and see if they’re registered on your website based on the error message that’s shown.

Logging Out

Let’s add a new button to the site layout to allow logging in or logging out. For logged-in users, a “Log out” button should show. For users who aren’t logged in, the button should show “Log in”. This change will require editing layout.html to add the menu link. However, it will require some logic to display one of the two links. Currently, layout.html.php is just included directly with this line:

include __DIR__ . ‘/../../templates/layout.html.php’;

For consistency, and to reuse the code we already have, let’s use the existing loadTemplate function so that we can pass variables to layout.html. In this case, we’ll create a single variable, $loggedIn, which stores whether or not the user is logged in. Replace the include line with the following:

echo $this->loadTemplate(‘layout.html.php’, [‘loggedIn’ =>

$authentication->isLoggedIn(),

‘output’ => $output,

‘title’ => $title

]);

Now, inside layout.html.php the variable loggedIn will store true or false depending on whether or not the user is logged in. Open up layout.html.php and add the log in/log out links with an if … else statement to display the correct link, depending on whether or not the user is logged in:

<ul>

  <li><a href=”/”>Home</a></li>

  <li><a href=”/joke/list”>Jokes List </a></li>

   <li><a href=”/joke/edit”>Add a new Joke </a></li>

    <?php if ($loggedIn): ?>

    <li><a href=”/logout”>Log out</a></li>

   <?php else: ?>

    <li><a href=”/login”>Log in</a></li>

  <?php endif; ?>

</ul>

Finally, let’s create the logout page and route:

Login.php:

public function logout() {

unset($_SESSION);

return [‘template’ => ‘logout.html.php’, ‘title’ => ‘You have been logged out’];

}

unset($_SESSION); will remove any data from the current session, logging the user out. Let’s add the route to IjdbRoutes:

‘logout’ => [

‘GET’ => [

‘controller’ => $loginController,

‘action’ => ‘logout’

     ]

],

logout.html.php:

<h2>Logged out</h2>

<p>You have been logged out</p>

You can find this code in Sessions-Logout.

Assigning Added Jokes to the Logged-in User

Now that users can register and log in, it’s time to make it so that when a joke is posted, it’s associated with the user who’s logged in. We already have an authorId column in the joke table. All we need to do is give it a value when the joke is added. The saveEdit method in the Joke controller currently contains this code:

public function saveEdit() {

  $joke = $_POST[‘joke’];

  $joke[‘jokedate’] = new \DateTime();

  $joke[‘authorId’] = 1;

  $this->jokesTable->save($joke);

header(‘location: /joke/list’);

}

At the moment, authorId is always set to 1. To get the ID of the logged-in user, we’ll need to amend the Authentication class slightly to provide a way of retrieving the record for the logged-in user. In the Authentication class, add the following method:

public function getUser() {

   if ($this->isLoggedIn()) {

            return $this->users->find($this->usernameColumn,

              strtolower($_SESSION[‘username’]))[0];

}

else {

      return false;

       }

}

This function checks to see if the user is logged in, and if they are, returns an array that contains the record representing the user who’s logged in. As with the login and isLoggedIn methods, we need [0] after the find method call to return the first record retrieved. It’s possible just to return the ID of the logged-in user, but later on we might want to know the name or the email address of the user. Returning the entire record gives more flexibility moving forward. Let’s make the Authentication class available in the Joke controller by adding a use line for Ninja\Authentication and adding the class variable and constructor argument:

<?php

namespace Ijdb\Controllers;

use \Ninja\DatabaseTable;

use \Ninja\Authentication;

class Joke {

private $authorsTable;

private $jokesTable;

public function __construct(DatabaseTable $jokesTable, DatabaseTable $authorsTable,

Authentication $authentication) {

$this->jokesTable = $jokesTable;

$this->authorsTable = $authorsTable;

$this->authentication = $authentication;

}

Here’s the argument list in IjdbRoutes:

$jokeController = new  \Ijdb\Controllers\Joke($this->jokesTable,

 $this->authorsTable, $this->authentication);

Once JokesController has access to the authentication class, assigning an author to a joke when it’s created is very easy:

public function saveEdit() {

$author = $this->authentication->getUser();

$joke = $_POST[‘joke’];

$joke[‘jokedate’] = new \DateTime();

$joke[‘authorId’] = $author[‘id’];

$this->jokesTable->save($joke);

header(‘location: /joke/list’);

}

You can find this code in Sessions-AuthorIdWhenever a joke is added, the author’s ID is assigned to the joke in the database. The currently logged-in user is retrieved from the database and their ID copied to the authorId column of the joke table. But there’s a concern here. What if $author contains false because the user isn’t logged in? Because the login check already happens in EntryPoint, there should be no way for the saveEdit method to be called unless someone is logged in. Go ahead and add a few jokes to the website. You’ll need to be logged in, but the jokes should get attributed to you! When you view the list of jokes, the jokes you post will be listed as being posted by you. You now have a fully functional login system. You can add pages to the website and make them visible only to logged-in users. Your users can register for accounts and log in to the website!

User Permissions

If you’ve been testing out the login system and have been playing around editing, deleting and adding jokes, you’ll have noticed a problem: anyone can delete or edit anyone else’s jokes! For most websites, when someone posts something, they have complete control over it and only they are able to delete it or make changes to it. Imagine how confusing Facebook or Twitter would be if people could edit and delete each other’s posts! Let’s add some checks to the site that prevent users from being able to add or edit each other’s jokes. The first thing to do is hide the edit and delete buttons from the joke list for jokes that don’t belong to the logged-in user. To do this, firstly we’ll need to provide the ID of the author along with the name and email in the $jokes array in the list method:

public function list() {

    $result = $this->jokesTable->findAll();

  $jokes = [];

foreach ($result as $joke) {

     $author = $this->authorsTable->

findById($joke[‘authorId’]);

    $jokes[] = [

   ‘id’ => $joke[‘id’],

   ‘joketext’ => $joke[‘joketext’],

   ‘jokedate’ => $joke[‘jokedate’],

   ‘name’ => $author[‘name’],

   ’email’ => $author[’email’],

   ‘authorId’ => $author[‘id’]

];

}

// …

Then, in the same method, we’ll pass the ID of the logged-in user to the template:

// …

$totalJokes = $this->jokesTable->total();

$author = $this->authentication->getUser();

return [‘template’ => ‘jokes.html.php’,

    ‘title’ => $title,

   ‘variables’ => [

   ‘totalJokes’ => $totalJokes,

   ‘jokes’ => $jokes,

   ‘userId’ => $author[‘id’] ?? null

]

];

As the person viewing the page may not be logged in, there may not be an author ID associated with the current user. To account for that, we’ve used $author[‘id’] ?? null to set the userId variable in the template to null when user is logged in. Finally, in the jokes.html.php, add an if statement inside the loop that iterates over the jokes. If the currently logged-in user is the user who posted the joke, display the edit and delete buttons. Otherwise, don’t show them:

// …

echo $date->format(‘jS F Y’);

?>)

<?php if ($userId == $joke[‘authorId’]): ?>

<a href=”/joke/edit?id=<?=$joke[‘id’]?>”> Edit</a>

<form action=”/joke/delete” method=”post”>

<input type=”hidden” name=”id” value=”<?=$joke[‘id’]?>”>

<input type=”submit” value=”Delete”>

</form>

<?php endif; ?>

</p>

</blockquote>

<?php endforeach; ?>

You can find this code in Sessions-CheckUser The clever part here is the if statement. It checks that $userId, which stores the ID of the currently logged-in user, is equal to the authorId of the joke being printed. If the logged-in user posted the joke, then the edit and delete buttons are displayed.

Mission Accomplished?

If you test the site at this point, you might think that’s done. Users can’t edit or delete each other’s jokes. However, that’s not quite true. Users can’t see the edit or delete buttons for jokes they didn’t post, but there’s nothing to stop them from visiting the edit page directly. Try visiting http://192.168.10.10/joke/edit?id=1 and changing the ID in the URL. You’ll see the edit page for any of the jokes, regardless of whether your account posted them. To fix this, we need to add a check to this page in the same way we did for the joke list. Firstly, like the joke list page, we supply the editjoke.html.php template with the ID of the logged-in user:

public function edit() {

  $author = $this->authentication->getUser();

if (isset($_GET[‘id’])) {

      $joke = $this->jokesTable->findById($_GET[‘id’]);

}

  $title = ‘Edit joke’;

return [‘template’ => ‘editjoke.html.php’,

   ‘title’ => $title,

‘  variables’ => [

  ‘joke’ => $joke ?? null,

   ‘userId’ => $author[‘id’] ?? null

   ]

   ];

}

Then, in editjoke.html.php, only display the form if the userId matches the joke’s authorId:

<?php if ($userId == $joke[‘authorId’]): ?>

<form action=”” method=”post”>

<input type=”hidden” name=”joke[id]” value=”<?=$joke[‘id’] ?? ”?>”>

<label for=”joketext”>Type your joke here: </label>

<textarea id=”joketext” name=”joke[joketext]” rows=”3″

cols=”40″><?=$joke[‘joketext’] ?? ”?>

</textarea>

<input type=”submit” name=”submit” value=”Save”>

</form>

<?php else: ?>

<p>You may only edit jokes that you posted.</p>

<?php endif; ?>

We’ve included an error message so that something is displayed in case someone does try to edit a joke they didn’t create. Now the user can’t see the edit form for jokes they didn’t create. Before celebrating the new security of our site, there’s one more thing we need to do. A sneaky attacker could create an HTML file with a form that posted data to your website. For example, they could create the file editjoke.html:

<form action=”http://192.168.10.10/joke/edit?id=1″

method=”post”>

<input type=”hidden” name=”joke[id]” value=”1″>

<label for=”joketext”>Type your joke here: </label>

<textarea id=”joketext” name=”joke[joketext]” rows=”3″

cols=”40″></textarea>

<input type=”submit” name=”submit” value=”Save”>

</form>

As long as they were logged in to the website, they could submit this form and regardless of who they were logged in as, edit the joke with the ID 1. We need to add the same check to the method that handles the form submission. To do this, we need to read the existing joke from the database and check that the ID matches the ID of the existing user:

public function saveEdit() {

$author = $this->authentication->getUser();

if (isset($_GET[‘id’])) {

   $joke = $this->jokesTable->findById($_GET[‘id’]);

if ($joke[‘authorId’] != $author[‘id’]) {

     return;

      }

}

$joke = $_POST[‘joke’];

$joke[‘jokedate’] = new \DateTime();

$joke[‘authorId’] = $author[‘id’];

$this->jokesTable->save($joke);

header(‘location: /joke/list’);

}

The check here issues the return command if the joke’s authorId column is not the same as the ID of the currently logged-in user. The return command will exit the method, and the rest of the code won’t run. The same thing needs to be done in the delete method to prevent someone creating a form to delete other people’s jokes:

public function delete() {

$author = $this->authentication->getUser();

$joke = $this->jokesTable->findById($_POST[‘id’]);

if ($joke[‘authorId’] != $author[‘id’]) {

      return;

}

$this->jokesTable->delete($_POST[‘id’]);

header(‘location: /joke/list’);

}

You can find this code in Sessions-CheckUser-Secured That’s it! You’ve secured all the relevant features of the website so that jokes can only be edited or deleted by the person who posted them. It’s very easy to forget that hiding a link is not enough to make something secure. We also need to ensure that people can’t find the URL and access the page anyway.

The Sky’s the Limit

You learned about the two main methods of creating persistent variables—those variables that continue to exist from page to page in PHP. The first stores the variable in the visitor’s browser in the form of a cookie. By default, cookies terminate at the end of the browser session, but by specifying an expiry time, they can be preserved indefinitely. Unfortunately, cookies are fairly unreliable, because you have no way of knowing when the browser might delete your cookies, and because some users occasionally clear their cookies out over concern for their privacy. Sessions, on the other hand, free you from all the limitations of cookies. They let you store an unlimited number of potentially large variables. Sessions are an essential building block in modern ecommerce applications, as we demonstrated in our simple shopping cart example. They’re also a critical component of systems that provide access control, like the one we built for our joke content management system.

At this point, you should be equipped with all the basic skills and concepts you need to build your very own database-driven website. While you may be tempted to skip the challenge of building a complete system for safely accepting public submissions, we encourage you to give it a try. You already have all the skills necessary to build it, and there’s no better way to learn than to make a few mistakes of your own.If you can tackle it with confidence, you may wish to try another challenge. Want to let users rate the jokes on the site? How about letting joke authors make changes to their jokes, but with the backup of requiring an administrator to approve the changes before they go live on the site? The power and complexity of the system is limited only by your imagination. We’ll cover more advanced topics that will help optimize our site’s performance and solve some complex problems using less code. Oh, and of course we’ll explore more exciting features of PHP and MySQL!

MySQL Administration

At the core of most well-designed, content-driven sites is a relational database. We’ve used the MySQL Relational Database Management System (RDBMS) to create our database. MySQL is a popular choice among web developers because it’s free, and because MySQL servers are fairly simple to set up. As we demonstrated , with proper instructions and a pre-configured Vagrant box, getting a MySQL database up and running only takes a couple of minutes. If all you need is a MySQL server so you can play with a few examples and experiment a little, the initial a process we went through is likely to be all you’ll need. If, on the other hand, you want to set up a database back end to a real live website—perhaps a site your company depends on—there are a few more fundamentals you’ll need to learn before you can rely on a MySQL server day in, day out.

First, we’ll look at backups. Backing up data that’s important to you or your business should be an essential item on any administrator’s list of priorities. Because administrators usually have more interesting work to do, though, backup procedures are often arranged once out of necessity and deemed “good enough” for all applications. If, until now, your answer to the question, “Should we back up our databases?” has been, “It’s okay; they’ll be backed up along with everything else,” you really should read on. We’ll show you why a generic file-backup solution is inadequate for many MySQL installations, and we’ll demonstrate the right way to back up and restore a MySQL database. Next, it’s time we looked more closely at how to control access to your MySQL database. We showed you the basics early, but it turns out there are some tricky details that can make your life difficult if you don’t understand them.We’ll show you how to regain control of your MySQL server should you forget your password! Then we’ll turn our attention to performance, and how to keep our SELECT queries running quickly. With the careful application of database indexes (a skill many working PHP developers lack, surprisingly), you can keep your database speedy even as it grows to contain thousands (or even hundreds of thousands) of rows. Finally, we’ll show you how to use a feature of MySQL—foreign keys—to express the structure of your database, and how the tables it contains are related to one another. As you can see a real mixed bag, but by the end of it you’ll understand MySQL a whole lot better! However, MySQL is a large and complex database with a lot of nuances and advanced features.

Backing Up MySQL Databases

Like web servers, most MySQL servers are expected to remain online 24 hours a day, seven days a week. This makes backups of MySQL database files problematic. Because the MySQL server uses memory caches and buffers to improve the efficiency of updates to the database files stored on disk, these files may be in an inconsistent state at any given time. Since standard backup procedures involve merely copying system and data files, backups of MySQL data files are unreliable, as there’s no guarantee the files that are copied are in a fit state to be used as replacements in the event of a crash. Furthermore, as many website databases receive new information at all hours of the day, standard backups can provide only periodic snapshots of database data. Any information stored in the database that’s changed after the last backup will be lost in the event that the live MySQL data files are destroyed or become unusable. In many situations, such as when a MySQL server is used to track customer orders on an ecommerce site, this is an unacceptable loss.

Facilities exist in MySQL to keep up-to-date backups that are largely unaffected by server activity at the time at which the backups are generated. Unfortunately, they require you to set up a backup scheme specifically for your MySQL data, completely apart from whatever backup measures you’ve established for the rest of your data. As with any good backup system, however, you’ll appreciate it when the time comes to use it.

Database Backups Using MySQL Workbench

The MySQL administration tool we’ve been using throughout, MySQL Workbench, also offers a convenient facility for obtaining a backup of your site’s database. After logging in to the server, the right-hand menu has an option for Data ExportThis opens a panel that displays all the databases on the server.

Click Data Export to save a backup of your database

There are two main options: Export to Dump Project Folder and Export to Self-Contained File. The latter is more useful for us, as it allows us to store the backup in a single file. Select Export to Self-Contained File, pick where you want to store the backup, and press Start Export. The default settings are perfect for our needs. If you open the backup file in a text editor, you’ll find it contains a series of SQL CREATE TABLE and INSERT commands that, if run on a blank database, would reproduce the current contents of your database. Yes, a MySQL database backup is just a series of SQL commands!

To restore your database from a backup file like this one, first make sure your database is empty (right-click on each table and select Drop Table). Then just click Data Import/Restore. To select the backup file, choose Import from Self-Contained file, find your backup file, and select the schema (for the jokes database it’s ijdb). In this way, we can use MySQL Workbench to create backups of our databases. MySQL Workbench connects to the MySQL server to perform backups, rather than accessing the MySQL database data files directly. The backup it produces is therefore guaranteed to be a valid copy of the database, instead of merely a point-in-time snapshot of the database files stored on disk, which may be in a state of flux as long as the MySQL server is running.

Database Backups Using mysqlpump

MySQL Workbench makes it really easy to obtain a database backup whenever the mood strikes you, but the best backups are automated, and MySQL Workbench is not an automated backup tool. As you’ll already know if you’ve ever worked with MySQL on Linux, the MySQL database server software comes with a handful of utility programs designed to be run from the command prompt. One of these programs is mysqlpump. MySQL Workbench actually uses this tool behind the scenes; it just provides a user-friendly interface. When run, mysqlpump connects to a MySQL server (in much the same way as PHP does) and downloads the complete contents of the database(s) you specify. It then outputs the series of SQL commands required to create a database with those same contents. If you save the output of mysqlpump to a file, you’ll have yourself the same kind of backup file that MySQL Workbench can generate for you!

You can run mysqlpump from your computer if you have MySQL installed. However, the Homestead Improved virtual machine we’re using already has the mysqlpump tool installed for us. One of the advantages of using a virtual server such as Homestead Improved is that it acts like a real web server. By learning how to perform a backup on the virtual server, you’ll know how to create a backup on a real web server! For both a real web server and our virtual one, you’ll need to log in to the server using a protocol called Secure Shell (SSH for short). This is a command line protocol that gives you a command prompt on a remote computer. Any commands you run via SSH will be executed on the web server, not on your local PC. Vagrant includes a shortcut command to do this. Make sure your server is running and you can type vagrant ssh to connect to the virtual server via SSH. This is a convenient shorthand, but it’s worth understanding what this does behind the scenes so you’re familiar with the process on a real server. Connecting to a remote server via SSH is very easy. From the Git Bash command prompt on Windows, or the Terminal/Console on macOS/Linux, type the following:

ssh vagrant@127.0.0.1 -p 2222

The above command will ask you if you want to trust the server (type yes), and when asked for a password, provide vagrantThe ssh command can be broken down into three parts: vagrant is the username, 127.0.0.1 is the server address and -p 2222 is the port. For a real web server, the -p 2222 option will not be necessary, unless you change this in the SSH settings. By default, SSH runs on port 22, but Vagrant uses port 2222, which is why we need to specify it when we connect. Once you’ve connected to the server via SSH, you can create a database backup with the mysqlpump command. The following command (typed all on one line) connects to the MySQL server running on the local machine as user root with password password, and saves a backup of the ijdb database into the file ijdb.sql:

mysqlpump -u homestead -psecret ijdb > ijdb.sql

Let’s break this command down to its individual parts:

mysqlpump executes the mysqlpump application.

-u homestead sets the username used to log in to the server. We can specify any user here, including the ijdb user we created.

-psecret sets the password to secret. Note that there’s no space between -p and the password, secret. This is used to specify the password on the command line.

We could use only -p, but we’ll be prompted for a password. Although more secure, a password prompt is unsuitable for automated backups, as someone has to type in the password each time the database is exported. ijdb is the name of the schema to export. The > operator can be used with any command, and is used to redirect the output to a file. Without this, the entire database backup is printed to the screen. For testing purposes, it can be useful to omit this (and the following filename) to check that the output looks correct. There’s no point having an automated backup if it’s not actually backing up the data we’re expecting it to! ijdb.sql is the name of the file to create. It defaults to the “current working directory”, but it can be useful to specify a full path such as /var/backups/ ijdb.sql when using automated backups, as the command may not run from a directory you can write to. To restore this database after a server crash, we could again feed this SQL file to MySQL Workbench. Alternatively, we could use the mysql utility program:

mysql -u homestead -psecret ijdb < ijdb.sql

This command has the same options as the mysqlpump command. However, in this instance, we’re loading a file using the < operator and sending the contents of the file to the mysql program, rather than saving to a file. But how do we bridge the gap between these snapshots to maintain a database backup that’s always up to date?

Incremental Backups Using Binary Logs

As I mentioned, many situations in which MySQL databases are used would make the loss of data—any data—unacceptable. In cases like these, we need a way to bridge the gaps between the backups we made using MySQL Workbench or mysqlpump as recently described. The solution is to configure the MySQL server to keep a binary log, a record of all SQL queries that were received by the database, and which modified the contents of the database in some way. This includes INSERT, UPDATE, and DELETE statements (among others), but excludes SELECT statements. The basic idea of a binary log is that you should be able to restore the contents of the database at the very moment at which a disaster occurs. This restoration involves applying a backup (made using MySQL Workbench or mysqlpump), and then applying the contents of the binary logs that were generated after that backup was made.

You can also edit binary logs to undo mistakes that might have been made. For example, if a co-worker comes to you after accidentally issuing a DROP TABLE command, you can export your binary log to a text file and then edit that file to remove the command. You can then restore the database using your last backup and then running the edited binary log. In this way, you can even preserve database changes that were made after the accident. And, as a precaution, you should probably also revoke your co-worker’s DROP privileges. To tell your MySQL server to keep binary logs, you need to edit the server’s my.ini (Windows) or my.cnf (macOS or Linux) configuration file. This is a simple text file with a list of options that control some of the finer points of how your MySQL server works. In many cases, MySQL is installed without a configuration file, and simply runs with the default settings. In this situation, you’ll need to create a new file and set the appropriate option.

To enable binary logging, you add a log-bin setting to the [mysqld] section of your configuration file. The Homestead Improved virtual machine doesn’t have binary logs enabled by default. To enable them, you’ll need to connect to the virtual machine via SSH, as described in the previous section, and edit the configuration file. As you’re connected via a command line, you won’t be able to open up the configuration file using your normal editor (Sublime Text, Atom or even Notepad) and you’ll need to use a command line text editor. The simplest editor is called nano, and the configuration file you’ll need to edit is stored at the location /etc/mysql/mysqld.conf.d/mysqld.conf. If you’re used to Windows, this file path may look a little strange: there’s no drive letter, and there are forward slashes instead of back slashes. Don’t worry, this is the way files are referenced on Linux systems. To open the file in nano, use this command while logged in to the virtual machine with SSH:

sudo nano /etc/mysql/mysqld.conf.d/mysqld.conf

The sudo prefix will open the file with administrator privileges. After all, you wouldn’t want just anyone being able to make changes to the MySQL configuration. If you try the command without sudo, you won’t be able to save changes to the file. With the file open on the screen, you can use the arrow keys to move around. If you’re new to command line text editors, it can be a little disconcerting, as there are no scrollbars and your mouse cursor doesn’t do anything! Don’t worry, we don’t have to do much using nano. We’ll just uncomment a couple of lines. The setting we’re looking for is called log_bin. This option stores a file path for where we want the binary log to be kept. To find the setting, press Ctrl+W on your keyboard. This issues a “Where” command. (You’re probably familiar with Ctrl+F for “Find”; nano uses the term “where” instead.) After pressing Ctrl+W, type log_bin and press return. The editor will take you to this line:

#log_bin = /var/log/mysql/mysql-bin.log

The log-bin setting tells MySQL where to store the binary log files and what name to give them. The default path is fine. The only change needed on this line is to remove the preceding #—which acts as a comment (like // in PHP). Remove the # using the backspace or delete key so the line reads like this:

log_bin = /var/log/mysql/mysql-bin.log

We’ll also need to uncomment the line above, which is #server-id = 1, again remove the # so it becomes server-id = 1. That’s all the changes we need to make to the configuration. (If you can’t see the line, use Ctrl+W again and type server-id as you did before for log_bin.) Now save the file by pressing Ctrl+O. (That’s the letter “o”, not a zero. It stands for “output” in nano’s terminology). We’re nearly done. Press Ctrl+X to exit nano and return to the command prompt. The final step is to restart MySQL so that it reads the updated configuration. To do this, run the following command:

sudo systemctl restart mysql.service

The sudo prefix here is important, as only people with administrator privileges can start and stop services.

Just the Same on a Real Web Server

You’ve just learned how to enable binary logging and how to change configuration files on a virtual machine, but the process will be the same if you ever need to do this on a real web server. Now that the binary log is enabled, a new file will be created each time the server flushes its log files. In practice, this occurs whenever the server is restarted.

Where to Store Your Logs

If possible, you should store your binary logs on a hard disk other than the one where your MySQL database files are stored. That way, if a hard disk goes bad, you won’t lose both your database and your backups! From now on, the server will create binary log files. To make sure, check the location you specified, in order to verify that a new log file was created when the server started up. Run the following command:

ls /var/log/mysql

In the event of a disaster, as long as you have a full backup and the binary log files that were generated after the backup was made, restoring your database should be fairly simple. Set up a new, empty MySQL server, then apply the full backup as described in the previous section. All that’s left is to apply the binary logs using the mysqlbinlog utility program. mysqlbinlog’s job is to convert the data format of MySQL binary logs into SQL commands that you can run on your database. Say you had two binary log files that you needed to apply after restoring your most recent full backup. You can generate an SQL text file from the two files using mysqlbinlog, and then apply that file to your MySQL server just as you would a file generated by mysqlpump:

mysqlbinlog binlog.000041 binlog.000042 > binlog.sql

mysql -u root -psecret < binlog.sql

MySQL Access Control Tips

Homestead Improved

In the case of the Homestead Improved box, the steps in this section have been done for us, and you don’t need to make any changes to the user accounts. This section will be beneficial when you want to start working with a real web server.

We mentioned that the database called mysql, which appears on every MySQL server, is used to keep track of users, their passwords, and what they’re allowed to do. We showed you how to use MySQL Workbench to create another user account, with access only to your website’s database. In essence, user access is governed by the contents of five tables in the mysql database: user, db, host, tables_priv and columns_priv. If you plan to edit these tables directly using INSERT, UPDATE and DELETE statements, we suggest you read the relevant section of the MySQL manual first. But for us mere mortals, MySQL Workbench provides all the tools you’ll need to manage access to your MySQL server.As a result of the way the access control system in MySQL works, there are some idiosyncrasies you should be aware of if you’re going to be responsible for controlling access to a MySQL server.

Host Name Issues

When you create users who can log in to the MySQL server only from the computer on which that server is running (for example, you require them to log in to the server and run the mysql command prompt utility from there, or to communicate using server-side scripts like PHP), you may ask yourself what to enter in the Host field of MySQL Workbench’s Add a new User form. Imagine the server is running on www.example.com. Should you specify the Host as www.example.com or localhostThe answer is that neither is reliable enough to handle all connections. In theory, if, when connecting, the user specifies the host name either with the mysql command prompt utility program, or with PHP’s PDO class, that host name will have to match the entry in the access control system. However, as you probably want to avoid forcing your users to specify the host name in a particular way (in fact, users of the mysql utility program are likely to want to steer clear of stating the host name at all), it’s best to use a workaround.

For users who need the ability to connect from the machine on which the MySQL server is running, it’s best to create two user entries in the MySQL access system: one with the actual host name of the machine (www.example.com, for example), and the other with localhost. Of course, you’ll have to grant/revoke all privileges to both user entries individually, but it’s the only workaround that you can really rely on. Another problem commonly faced by MySQL administrators is that user entries whose host names contain wildcards (for example, %.example.com) may fail to work. When MySQL’s access control system behaves unexpectedly, it’s usually due to the way MySQL prioritizes the user entries. In particular, it orders entries so that more specific host names appear first. (For example, www.example.com is absolutely specific, %.example.com is less specific, and % is totally unspecific.) In a fresh installation, the MySQL access control system contains two root user entries, and two anonymous user entries. (These latter allow connections to be made from the local host using any username. The two entries support connections from localhost and the server’s actual host name, as described before.) The problem just described occurs when the anonymous user entries take precedence over our new entry, because their host name is more specific. Let’s look at the abridged contents of the user table on www.example.com, a fictitious MySQL server, where we’ve just added a new account for a user named Jess. The rows are sorted in the order in which the MySQL server considers them when it validates a connection:

Host                   User       Password
------------------- -------- -----------------
localhost              root    encrypted value
www.example.com        root    encrypted value
localhost
www.example.com
%.example.com          jess     encrypted value

As you can see, since Jess’s entry has the least specific host name, it comes last in the list. When Jess attempts to connect from www.example.com, the MySQL server matches her connection attempt to one of the anonymous user entries (a blank User value matches anyone). Since a password is unnecessary for these anonymous entries, and presumably Jess enters her password, MySQL rejects the connection attempt. Even if Jess managed to connect without a password, she would be given the very limited privileges that are assigned to anonymous users, as opposed to the privileges assigned to her entry in the access control system. The solution is either to make your first order of business as a MySQL administrator the deletion of those anonymous user entries (DELETE FROM mysql.user WHERE User=””), or to give two more entries to all users who need to connect from localhost (that is, entries for localhost and the actual host name of the server):

Host                User     Password
------------------- -------- -----------------
localhost           root     encrypted value
www.example.com     root     encrypted value

localhost           jess    encrypted value
www.example.com     jess   encrypted value
localhost
www.example.com
%.example.com       jess     encrypted value

As it’s excessively burdensome to maintain three user entries (and three sets of privileges) for each user, we recommend that you remove the anonymous users, unless you have a particular need for them:

Host                User     Password
------------------- -------- -----------------
localhost           root     encrypted value
www.example.com     root     encrypted value
%.example.com       jess     encrypted value

Locked Out?

Like locking your keys in the car, forgetting your password after you’ve spent an hour installing and tweaking a new MySQL server can be rather embarrassing! Fortunately, if you have administrator access to the computer on which the MySQL server is running, or if you can log in as the user you set up to run the MySQL server, all is well. The following procedure will let you regain control of the server.

Assuming You Know How to Log In to the Server

Again, we are going to go ahead and assume in this section that if you’re locked out of your MySQL server, you know how to log in to the server and that you have SSH access.

First, you must shut down the MySQL server by running this command:

sudo systemctl stop mysql.service

Now that the server’s down, you must restart it using the skip-grant-tables option. You can do this by adding the option to your MySQL server’s my.cnf configuration file (see the instructions for setting up such a file in the “Incremental Backups Using Binary Logs” section above): Under the [mysqld] line, add skip-grant-tables and save the file:

[mysqld]

skip-grant-tables

This instructs the MySQL server to allow unrestricted access to anyone. Obviously, you’ll want to run the server in this mode as briefly as possible, to avoid the inherent security risks. Restart the server with this command:

sudo systemctl start mysql.service

Connect to the MySQL server (using MySQL Workbench or the mysql command prompt utility), then change your user’s password to a memorable one:

UPDATE mysql.user SET Password=PASSWORD(“newpassword”)

WHERE User=”homestead”

Finally, disconnect, shut down your MySQL server as above, and remove the skip-grant-tables option. Start the server again and you’ll be able to connect with the new password. That’s it! (And thankfully, nobody ever has to know what you did.) As for locking your keys in your car, you’re on your own there.

Indexes

Just as the index makes it a lot easier to find every mention of a particular topic in its pages, a database index can make it much easier for MySQL to find the records you’ve asked for in a SELECT query. Let me give you an example. As the Internet Joke Database grows, the joke table might grow to contain thousands or rows, if not hundreds of thousands. Now, let’s say PHP asks for the text of a particular joke:

SELECT joketext FROM joke WHERE id = 1234

In the absence of an index, MySQL must look at the value of the id column in each and every row of the joke table, one by one, until it finds the one with the value 1234. Worse yet, without an index, MySQL has no way of knowing that there is only one row with that value, so it must also scan the rest of the table for more matching rows to make sure it gets them all! Computers are fast, and good at menial labor, but in the web development game, where half seconds count, large tables and complex WHERE clauses can easily combine to create delays of 30 seconds or more!

Fortunately for us, this query will always run quickly, and that’s because the id column of the joke table has an index. To see it, open MySQL Workbench, right-click the joke table, and click Alter table. At the bottom of the window are some tabs, one of which is Indexes. Click on that and you’ll see a list of indexes on the table.

Each of our tables already has a single index

Take a look at the left-hand column and you’ll see an index called PRIMARYRemember how we defined the id column of the table:

CREATE TABLE joke (

   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

   joketext TEXT,

  jokedate DATE NOT NULL,

  authorId INT

) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

In fact, “key” is just a fancy way to say “index” in database parlance, and a primary key is just an index named PRIMARY that requires each value in the table for that particular column to be unique. What all this boils down to is that every database table we’ve created so far has an index on its id column. Any WHERE clause that seeks a particular id value will be able to find the record with that value quickly, because it will be able to look it up in the index to know exactly where in the table the relevant record is. You can confirm this by asking MySQL to explain how it performs a particular SELECT query. To do this, just add the command EXPLAIN at the start of the query:

EXPLAIN SELECT joketext FROM joke WHERE id = 1

Specifying a Joke ID that Exists

Note that we’ve specified a joke ID of 1 in this query, which actually exists in my database. Had we used a made-up value like 1234, MySQL is smart enough to know that this ID doesn’t exist in the joke table and wouldn’t even try to fetch results from the table.

If you run this EXPLAIN query in MySQL Workbench, you’ll see something similar to this:

These results confirm that the SELECT query will use the PRIMARY index

Now consider this SELECT query, which fetches all jokes by a particular author:

SELECT * FROM joke WHERE authorId = 2

Ask MySQL to EXPLAIN this SELECT, and you’ll see a result like this:

Those NULLs indicate slowness

As you can see, MySQL is unable to find an index to assist with this query, so it’s forced to perform a complete scan of the table for results. We can speed up this query by adding an index to the table for the authorId column.

But the author Table Has an Index?

Yes, the id column of the author table has an index by virtue of it being the primary key for that table. This won’t help in the case of this query, however, which has no involvement with the author table at all. The WHERE clause in this case is looking for a value in the authorId field of the joke table, which is without an index.

In MySQL Workbench, select the joke table, right-click and choose Alter Table. On the Indexes tab, add a new row under PRIMARY by double-clicking the empty cell. Give your index a name (this can be anything, but the column name is often used) and select the type as INDEX. We can’t use UNIQUE here, because that would prevent each author from being able to have more than one joke in the table.

Creating a new index for the authorId column

Now select the authorId column from the middle panel and click Apply. Ask MySQL to EXPLAIN the SELECT query again to confirm that it will use your new authorId index this time. It might be tempting to index each and every column in your database, but I’d advise against it. Not only do indexes require extra disk space, but every time you make a change to the contents of your database (with an INSERT or UPDATE query, for example), MySQL has to spend time rebuilding all affected indexes! For this reason, you should usually add the indexes required to keep your website’s SELECT queries speedy and no more. As a rule of thumb, you should create an index on any column that you use in a WHERE, GROUP BY, ORDER BY or JOIN … ON clause.

Multicolumn Indexes

But wait! Not every table will have an id column. Consider a table that associates jokes with categories. We’d need a table for categories, which will have an ID:

CREATE TABLE `category` (

`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

`name` VARCHAR(255),

PRIMARY KEY (`id`)

) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

However, in this instance we’d need what’s known as a many-to-many relationship. Each joke might fit into more than one category. For example, the joke “Why did the programmer quit his job? He didn’t get arrays” fits into both the categories “programming jokes” and “one-liners”. We can’t model this relationship in the same way as we have with the relationship between authors and jokes—by creating a categoryId column in the joke table. Doing so only allows us to have a single category for a joke. Instead, we need to create something called a join table or junction table, which has two columns,jokeId and categoryId. The joke_category junction table might contain values like this:

jokeId | catoryId

        1|         1

        1|        2

       2|        3

       3|        1

It’s all numbers, but assuming category 1 is “one-liners”, to find all the jokes in the category we can use this query:

SELECT jokeId FROM joke_category WHERE categoryId = 1

This would give the following results:

jokeId

1

3

Then we can read the individual jokes from the database by looping over the results and issuing a select query for each of the jokes:

SELECT * FROM joke WHERE id = 1;

SELECT * FROM joke WHERE id = 3;

The joke_category table doesn’t have an obvious primary key. Although we could create an id auto increment column, this is far from ideal. We don’t want the same record appearing twice. For example:

jokeId | catoryId

     1|            1

     1|           2

    2|           3

    3|           1

    3|           1

Now joke 3 in category 1 appears twice. Any query to select the jokes in category 1 will return joke 3 twice! To prevent that, we can actually create a multi-column primary key:

CREATE TABLE joke_category (

jokeId INT NOT NULL,

categoryId INT NOT NULL,

PRIMARY KEY (jokeId, categoryId)

) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

This table’s primary key is made up of two columns: jokeid and categoryid. The image below shows what this index looks like in MySQL Workbench. Because a primary key has to be unique, there’s no way for this table to store the same record twice.

Indexes can contain multiple columns

A multi-column index like this is called a composite index. It’s great at speeding up queries that involve both indexed columns, like the one below, which checks if joke ID 3 is in category ID 4:

SELECT * FROM joke_category WHERE jokeid = 3 AND categoryid = 4

A two-column index like this one can also be used as a one-column index on the first column in the list. In this case, that’s the jokeid field, so this query will also use the index to list the categories that joke ID 1 belongs to:

SELECT * FROM joke_category WHERE jokeid = 1

Foreign Keys

By now, you should be used to the concept of a column in one table pointing to the id column in another table to represent a relationship between the two tables. For example, the authorId column in joke points to the id column in author to record which author wrote each joke. In database design lingo, a column that contains values that match those in another table is called a foreign key. That is, we say that authorId is a foreign key that references the id column in author.

Up to this point, we’ve simply designed tables with foreign key relationships in mind, but these relationships haven’t been enforced by MySQL. That is, we’ve made sure to only store values in authorId that correspond to entries in the author table. But if we carelessly inserted an authorId value without any matches for an author record, MySQL would do nothing to stop us. As far as MySQL is concerned, authorId is just a column that contains whole numbers. MySQL supports a feature called foreign key constraints, which you can use to record relationships between tables like this one explicitly and have MySQL enforce them. You can include foreign key constraints in your CREATE TABLE commands, or you can add foreign key constraints to existing tables using ALTER TABLE:

CREATE TABLE joke (

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

   joketext TEXT,

   jokedate DATE NOT NULL,

   authorId INT,

FOREIGN KEY (authorId) REFERENCES author (id)

) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB

ALTER TABLE joke

ADD FOREIGN KEY (authorId) REFERENCES author (id)

You can also use MySQL Workbench to create foreign key constraints. First, you must make sure the foreign key column (authorId in this case) has an index. MySQL will create this index for you automatically if you use either of these two queries, but MySQL Workbench requires you to do it yourself. Thankfully, we already added an index to authorId. Next, on the Foreign Keys tab for the joke table, double-click in the top row of the Foreign Key Name column. A name will be generated for you, and this isn’t used by MySQL. It’s for your own reference. Next, we call the key fk_joke_author to highlight that we’re creating a foreign key between the joke and author tables. Then select the author table as the referenced table and choose the authorId column as the Column and id column as the Referenced column.

Setting up a foreign key in MySQL Workbench

With this foreign key constraint in place, MySQL will reject any attempt to insert into joke an authorId value that fails to correspond to an entry in the author table. Furthermore, it will stop you from deleting an entry in the author table unless you first remove any joke records that point to it.

Referential Action

Instead of rejecting attempts to delete or update records that have foreign keys pointing to them (for example, preventing you from deleting authors who still have jokes associated with them), you can perform a referential action. This involves configuring a foreign key constraint in MySQL to automatically resolve the conflict. It can do this either by cascading the operation (that is, deleting any jokes associated with the author that you’re deleting), or by setting the values of any affected foreign key columns to NULL (setting the authorId of the author’s jokes to NULL). That’s what the ON RESTRICT and ON UPDATE options for the foreign key constraint in MySQL Workbench are all about. It can be tempting to use this feature to let MySQL take care of what happens to affected jokes when a user deletes an author or a category. It’s certainly easier to select an option in MySQL Workbench than it is to write the PHP code to automatically delete related jokes before removing an author.

The problem with doing this is that it splits the logic of your website into two places: your PHP code and the foreign key constraints. No longer will you be able to see and control everything that happens when you delete a joke by just looking at the PHP controller responsible for doing that job. For this reason, most experienced PHP developers (myself included) prefer to avoid using referential actions in foreign key constraints. In fact, some developers prefer to avoid using foreign key constraints altogether!

Better Safe than Sorry

Admittedly, this chapter hasn’t been the usual nonstop, action–packed codefest to which you may have become accustomed by now. But our concentration on these topics—the backup and restoration of MySQL data, the inner workings of the MySQL access control system, the improvement of query performance with indexes, and the enforcement of the structure of your database with foreign keys—has armed you with the tools you’ll need to set up a MySQL database server that will stand the test of time, as well as endure the constant traffic your site will attract.

This Is A Custom Widget

This Sliding Bar can be switched on or off in theme options, and can take any widget you throw at it or even fill it with your custom HTML Code. Its perfect for grabbing the attention of your viewers. Choose between 1, 2, 3 or 4 columns, set the background color, widget divider color, activate transparency, a top border or fully disable it on desktop and mobile.

This Is A Custom Widget

This Sliding Bar can be switched on or off in theme options, and can take any widget you throw at it or even fill it with your custom HTML Code. Its perfect for grabbing the attention of your viewers. Choose between 1, 2, 3 or 4 columns, set the background color, widget divider color, activate transparency, a top border or fully disable it on desktop and mobile.