Chapter 13: Relationships
As you’ve worked through the construction of the Internet Joke Database website, you’ve had opportunities to explore most aspects of Structured Query Language (SQL). From the basic form of a CREATE TABLE query to the two syntaxes of INSERT queries, you probably know many of these commands by heart now.
In Chapter 5, I showed you how to perform basic JOINs using SQL to fetch data from more than one table at a time. A lot of the time, you’ll come across situations where you want to do this—for example, finding information about an author as well as all the jokes they’ve posted, or finding a category and all the jokes that reside inside it.
SQL JOIN is one of many solutions to this problem. Although there are performance advantages to using JOIN, unfortunately JOINs don’t work well with object-oriented programming. The relational approach used by databases is generally incompatible with the nested structure of object-oriented programming. In object-oriented programming, objects are stored in a hierarchical structure. An author contains—or, in the correct OOP terminology, encapsulates—a list of their jokes, and a category also encapsulates a list of the jokes within the category.
A SELECT query that fetches an author along with all of their jokes can be written like this:
SELECT author.name, joke.id, joke.joketext
FROM author
INNER JOIN joke ON joke.authorId = author.id
WHERE authorId = 123
Using an object-oriented approach, there are various practical ways of achieving this, which we’ll look into shortly. But at a more general level, using OOP, rather than an SQL query, fetching a list of jokes would be expressed like this:
// Find the author with the id `123`
$author = $authors->findById(123);
// Get all the jokes by this author
$jokes = $author->getJokes();
// Print the text of the first joke by that author.
echo $jokes[0]->joketext;
Notice that there’s no SQL here. The data is coming from the database, but it all happens behind the scenes.
We could fetch all the information by using the SQL query I provided above, but this doesn’t work well with the DatabaseTable class we’ve used so far. It would be very difficult to design the class in such a way that it would account for every possible set of relationships we may want.
So far, we’ve dealt with the relationship between jokes and authors in a relational way. If we wanted to get the information about an author, along with a list of all their jokes, we’d do this:
// Find the author with the ID 123
$author = $this->authors->findById(123);
// Now find all the jokes posted by the author with that ID
$jokesByAuthor = $this->jokes->find('authorId',
$authorId);
This runs two separate SELECT queries, and the two DatabaseTable instances are entirely separate.
We used a similar approach when inserting a joke into the database:
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');
}
This code uses the authentication class to fetch the record that stores the currently logged-in user. It then reads the author’s id in order to provide it when the joke is added: $joke['authorId'] = $author['id'];.
Whoever writes this code must know about the underlying structure of the database and that the authors and jokes are stored in a relational way.
In object-oriented programming, it’s preferable to hide the underlying implementation, and the code above would be expressed like this:
public function saveEdit() {
$author = $this->authentication->getUser();
$joke = $_POST['joke'];
$joke['jokedate'] = new DateTime();
$author->addJoke($joke);
header('location: /joke/list');
}
Not a lot has changed, so look closely! Firstly, the line $joke['authorId'] = $author['id']; has been removed. Secondly, instead of saving the joke to the $jokesTable object, it’s being passed to the author object: $author->addJoke($joke);.
What is the advantage of this approach? The person who writes this code doesn’t have to know anything about what happens behind the scenes, or how the relationship is modeled in the database—that there’s an authorId column in the joke table.
Instead of modeling the relationships in a relational way, object-oriented programming takes a hierarchical approach, using data structures. Just as the routes in the IjdbRoutes class is a multi-dimensional array, OOP has multi-dimensional data structures stored within objects.
In the example above, the $author->addJoke($joke) method call might be writing the joke data to a database. Alternatively, it might be saving the data to a file. And that file could be in JSON format, XML format or an Excel spreadsheet. The developer who writes this saveEdit method doesn’t need to know anything about the underlying storage mechanism—how data is being stored—but only that the data is being stored somehow, and that it’s being stored inside the author instance.
In object-oriented programming terminology, this is known as implementation hiding, and it has several advantages. Different people can work on different sections of the code. The developer who writes the saveEdit doesn’t have to be familiar with how addJoke actually works. They only need to know that it saves data, and that the data can be retrieved later.
When you use the method $pdo->query, you don’t need to know how $pdo actually communicates with the database. You only need to know what the method returns and what arguments it requires. We can imagine what the following lines of code do, knowing how each of them works:
$jokes = $author->getJokes();
echo $joke->getAuthor()->name;
$joke = $_POST['joke'];
$joke['jokedate'] = new DateTime();
$author->addJoke($joke);
In the middle example, as long as you know there’s a getAuthor method that you can call on the $joke instance, it doesn’t matter how it works. The author name could be hardcoded into the class, or it could go off and fetch it from the database.
This is particularly useful, because the storage system can be changed at any time, and the code above doesn’t need to change. The methods getJokes, getAuthor and $joke can be completely rewritten to write to/read from a file, for example, but the code above will still work without any further changes.
If the saveEdit contained the INSERT query with all the relevant database fields, we’d need to rewrite the whole method to change the way the data is stored, and we’d need to do this anywhere data was inserted.
This approach to splitting up the logic is loosely known as separation of concerns. The process of saving a joke is different from the process of writing data to the database. Each of these is a different concern. By splitting out the two concerns, you have a lot more flexibility. The addJoke method can be called from anywhere without needing to repeat the logic. The way the addJoke method works can be completely rewritten to work in a different way, but the code that calls it can remain unchanged.
This added flexibility is incredibly useful for an increasingly popular development methodology called test-driven development (TDD). In TDD, you’d test the code by writing a version of the DatabaseTable class that worked as a placeholder for the test, rather than needing a working database containing relevant test data.
Although TDD is beyond the scope of this book, by thinking about separation of concerns, you’ll be able to start writing automated tests for your code without making large-scale changes.
A common problem mid-level developers have when first learning about TDD is that they understand the advantages of it, but their code isn’t written in such a way that makes it easy to test. By considering separation of concerns earlier on in your programming career, you’ll make it easier for yourself moving forward.
For more information on TDD, check out the introductory article Re-Introducing PHPUnit – Getting Started with TDD in PHP”.
Object Relational Mappers
The DatabaseTable class we’ve built step by step so far is a type of library called an object relational mapper (or ORM). There are a lot of ORM implementations available, such as Doctrine, Propel and ReadBeanPHP. These all do essentially the same job as the DatabaseTable class we’ve been building: providing an object-oriented interface for a relational database. They bridge the gap between the relational database’s SQL queries and the PHP code we’re using for everything else on the website.
Generally, ORMs deal with Objects. Using our DatabaseTable class to find an author and print their name, we can use this code:
$author = $authors->findById(123);
echo $author['name'];
Here, the $author variable is an array with keys for each of the columns in the database. Arrays can’t contain functionsOK, that’s a lie. They can contain a special type of function called a closure, but doing so has several severe limitations compared to the OOP based approach I’m going to show you instead., so implementing an addJoke method on the $author instance isn’t possible.
If we want the ability to call methods on the $author instance, such as $author->addJoke($joke) like above, the $author variable needs to be an object rather than an array. The first thing we need to do is create the relevant class to represent authors. Firstly, some properties for each of the columns from the database:
namespace IjdbEntity;
class Author {
public $id;
public $name;
public $email;
public $password;
}
As the properties of an author are unique to the joke website, I’ve put the class in the Ijdb namespace.
A class like this, which is designed to map directly to a record in the database, is commonly known as an Entity Class, which is why I’ve used the name Entity in the namespace. We’ll have a different entity class for each of the database tables we need to represent. Create the directory Entity inside the Ijdb folder and save the class inside it with the name Author.php.
Although the variables don’t need to be declared here, and what we’re doing will work identically whether they are or not, it makes the code easier to read and understand if the variables are included.
There’s some repetition here: every time you add a column to the database table, you’ll need to add it to this entity class. Because of this, many ORMs provide a method of generating these entity classes from the database schema, or even creating the database table from the object!
I’m not going to show you how to do that, but if you do want to try something similar, you should take a look at the MySQL DESCRIBE query to retrieve a list of columns in a table, or the PHP Reflection library to get a list of properties in a class.
Public Properties
Every time we’ve created a class variable so far, it’s been private, so that the data used is only accessible to methods within the class. The advantage of this is that class variables can be easily added, renamed or removed without potentially breaking any of the code that uses the class.
It also prevents developers accidentally breaking the functionality of the class. If the pdo variable were public in the DatabaseTable class, it would be possible to do this:
$this->jokesTable->pdo = 1234;
Any further call to methods on the DatabaseTable class would break:
$this->jokesTable->findById('1243');
The findById method would call $this->pdo->query('…'), but because the pdo variable is no longer a PDO instance, it would break!
In most cases, private properties are strongly preferred over public ones. However, in the case of entity classes, you should use public properties.
The sole purpose of an entity class is to make some data available. It’s no good having a class representing an author if you can’t even read the author’s name!
Nine times out of ten—in fact, ninety-nine times out of a hundred—public properties are the wrong solution to any given problem. However, if the responsibility of the class is to represent a data structure, and it’s interchangeable with an array, then public properties are fine.
Methods in Entity Classes
A class is used to store the data about authors instead of an array, because the class can contain methods, and we can do things like this:
// Find the author with the id 1234
$author = $this->authorsTable->findById('1234');
// Find all the jokes by that author
$author->getJokes();
// Add a new joke and associate it with the author
// represented by $author
$author->addJoke($joke);
Let’s take a moment to think about what the getJokes method might look like. Assuming the id property in the $author class is set, it would be possible to do this:
public function getJokes() {
return $this->jokesTable->find('authorId',
$this->id);
}
To do this, the author class needs access to the jokesTable instance of the DatabaseTable class. Add the getJokes method, along with a constructor and class variable to store the reference to the jokesTable instance:
<?php
namespace IjdbEntity;
class Author
{
public $id;
public $name;
public $email;
public $password;
private $jokesTable;
public function __construct(NinjaDatabaseTable $jokesTable)
{
$this->jokesTable = $jokesTable;
}
public function getJokes()
{
return $this->jokesTable->find('authorId',
$this->id);
}
}
We’re going to amend the DatabaseTable class to return an instance of this class instead of an array. But before we do that, let’s take a look at how the Author class can be used on its own:
$jokesTable = new NinjaDatabaseTable($pdo, 'joke', 'id');
$author = new IjdbEntityAuthor($jokesTable);
$author->id = 123;
$jokes = $author->getJokes();
This would query the database for all the jokes by the author with the ID 123. We can now fetch data that’s related to the author, once we have an Author instance that represents any given author, by setting the id property.
Next, we need the addJoke method that takes a joke as an argument, sets the authorId property and then inserts it into the database:
public function addJoke($joke) {
$joke['authorId'] = $this->id;
$this->jokesTable->save($joke);
}
Let’s use the new class inside the Joke controller’s saveEdit method to save the jokes using $author->addJoke($joke).
public function saveEdit() {
$author = $this->authentication->getUser();
$authorObject = new IjdbEntityAuthor
($this->jokesTable);
$authorObject->id = $author['id'];
$authorObject->name = $author['name'];
$authorObject->email = $author['email'];
$authorObject->password = $author['password'];
$joke = $_POST['joke'];
$joke['jokedate'] = new DateTime();
$authorObject->addJoke($joke);
header('location: /joke/list');
}
You can find this code in Relationships-Author.
Using Entity Classes from the DatabaseTable Class
Because the database returns data as an array, I’ve copied the data from the $author array returned by getUser() to an instance of the newly created Author class.
Both the array $author and the object $authorObject will represent the same author. The only difference is that one is an object and the other is an array.
Most of the lines of code in the method simply copy data from the array to an object. This is obviously inefficient, and the problem can be avoided if we can construct the Author object outside of the saveEdit method and have getUser return the constructed object like so:
public function saveEdit() {
$authorObject = $this->authentication->getUser();
$joke = $_POST['joke'];
$joke['jokedate'] = new DateTime();
$authorObject->addJoke($joke);
header('location: /joke/list');
}
Currently, an array is created, because the getUser method in the Authentication class calls the findById method in the DatabaseTable class:
public function findById($pdo, $table, $primaryKey,
$value) {
$query = 'SELECT * FROM ' . $table . ' WHERE ' .
$primaryKey . ' = :primaryKey';
$parameters = [
'primaryKey' => $value
];
$query = $this->query($query, $parameters);
return $query->fetch();
}
Here, $query->fetch() returns an array. Luckily for us, there’s also a fetchObject method, which returns an instance of a specified class—in our case, Author. This will instruct PDO to create an instance of the Author class and set the properties on that, rather than returning a simple array.
For example, to have fetchObject() return an Author object, you can use this code:
return $query->fetchObject('Author', [$jokesTable]);
There are two arguments here:
- The name of the class to instantiate.
- An array of arguments to provide to the constructor when the object is created. Because there’s only a single element in the array,
[$jokesTable]looks a little strange. However, as constructors can have multiple arguments, an array is required so you can provide each constructor argument.
Now, rather than writing new Author somewhere in our code, we can have the PDO library create an instance of the Author class in place of returning the data as an array.
$pdo->query('SELECT * FROM `author` WHERE id = 123');
$author = $query->fetchObject('Author', [$jokesTable]);
Because the Author class requires the $jokesTable class as a constructor argument, this must also be provided as an argument when fetchObject is called. However, we can’t amend the DatabaseTable class to use the return line above—firstly, because it doesn’t have access to the $jokesTable variable, and secondly, because we’re using the DatabaseTable class to interact with different database tables. When the findById method is called, it may be on the authorsTable instance, the jokesTable instance, or an instance of the DatabaseTable class that represents some other database table.
We’ll want a different entity class for each table, and they almost certainly won’t have the same constructor arguments.
Instead of hardcoding the class name and constructor argument, we can amend the constructor of the DatabaseTable class to take two optional arguments—the name of the class to create, and any arguments to provide to it:
class DatabaseTable {
private $pdo;
private $table;
private $primaryKey;
private $className;
private $constructorArgs;
public function __construct(PDO $pdo, string $table,
string $primaryKey, string $className = 'stdClass',
array $constructorArgs = []) {
$this->pdo = $pdo;
$this->table = $table;
$this->primaryKey = $primaryKey;
$this->className = $className;
$this->constructorArgs = $constructorArgs;
}
Notice that I’ve given default values to each of the new arguments. The stdClass class is an inbuilt PHP empty class that can be used for simple data storage.
By specifying this as a default value, if no class name is specified, it will use this generic inbuilt one. The advantage this gives us is that we don’t need to create a unique entity class for every database table, but only those we want to add methods to!
The findById method can now be changed to read the new class variables and use those in place of hardcoded ones in the fetchObject method call:
public function findById($value) {
$query = 'SELECT * FROM `' . $this->table . '` WHERE `' .
$this->primaryKey . '` = :value';
$parameters = [
'value' => $value
];
$query = $this->query($query, $parameters);
return $query->fetchObject($this->className,
$this->constructorArgs);
}
PDO’s fetchAll method—which we’re using in the DatabaseTable’s find and findAll methods—can also be instructed to return an object by providing PDO::FETCH_CLASS as the first argument, the class name as the second, and the constructor arguments as the third:
return $result->fetchAll(PDO::FETCH_CLASS, $this->className, $this->constructorArgs);
Now that we’ve amended the DatabaseTable class, we can change IjdbRoutes, where the DatabaseTable class is instantiated, and provide the class name and the arguments for the $authorsTable instance:
$this->jokesTable = new NinjaDatabaseTable($pdo,
'joke', 'id');
$this->authorsTable = new NinjaDatabaseTable($pdo,
'author', 'id', 'IjdbEntityAuthor',
[$this->jokesTable]);
Now, when the $authorsTable instance is used to retrieve a record, like this:
$author = $authorsTable->findById(123);
… the $author variable will be an instance of the Author class, and any methods in the class (such as addJoke) will available for us to call.
As we’ve made a change to the DatabaseTable class, this will affect every instance of the class. When jokes are retrieved from the database, an object will also be retrieved. As we haven’t specified an entity class for jokes (yet!), they will be an stdClass instance.
If you load up the joke list page in your browser, at this point you’ll see this error:
Fatal error: uncaught Error: cannot use object of type stdClass as array in /home/vagrant/Code/Project/classes/Ijdb/Controllers/Joke.php on line 21
We’ll fix this later, but first let’s get saveEdit working. Amend the saveEdit method to avoid all the value copying. Change the code from this:
public function saveEdit() {
$author = $this->authentication->getUser();
$authorObject = new IjdbEntityAuthor
($this->jokesTable);
$authorObject->id = $author['id'];
$authorObject->name = $author['name'];
$authorObject->email = $author['email'];
$authorObject->password = $author['password'];
$joke = $_POST['joke'];
$joke['jokedate'] = new DateTime();
$authorObject->addJoke($joke);
header('location: /joke/list');
}
… to this:
public function saveEdit() {
$author = $this->authentication->getUser();
$joke = $_POST['joke'];
$joke['jokedate'] = new DateTime();
$author->addJoke($joke);
header('location: /joke/list');
}
Because getUser now returns an instance of Author with all the properties already set, we can use this instance rather than having to manually create the $authorObject instance and set each property individually.
Before we can test that this works, we’ll also need to amend the Authentication class to use an object rather than an array. In the isLoggedIn method, replace this line:
if (!empty($user) &&
$user[0][$this->passwordColumn] ===
$_SESSION['password']) {
… with this:
$passwordColumn = $this->passwordColumn;
if (!empty($user) && $user[0]->$passwordColumn
=== $_SESSION['password']) {
This code looks complicated! However, let’s take a moment to understand what’s happening here.
The $user[0] variable now stores an instance of the Author class. The Author class has a property called password. To read this property, you could use this:
$user[0]->password
However, if you remember back to Chapter 11, you’ll recall that the Authentication class has a class variable, which stores the name of the database column that contains the password. It might not be password in every website you build.
Assuming the password column is password, once the variables have been evaluated, all we’re doing here is replacing $user['password'] with the object variant $user->password. But this is complicated by the fact that we’re using a variable for the column name. We really need $user[$this->passwordColumn] to be replaced with $user->$this->passwordColumn.
It’s possible to use a variable to access a property on an object using a string, as we do with an array:
$columnName = 'password';
// Read value stored under key 'password' from array
$password = $array[$columnName];
// Read value stored under property 'password' from object
$password = $object->$columnName;
You might be wondering why I didn’t use $user[0]->$this->passwordColumn. As PHP evaluates left to right, it would try to look up the contents of the variable $this (an object), then try to read a variable with that name. As a variable name can’t be an object, PHP will give an error. Instead, I read the value into its own variable, $passwordColumn, then used $user[0]->$passwordColumn.
Using Braces
As an alternative to creating a new variable, you can also use braces to tell PHP to evaluate the $this->passwordColumn lookup first:
$user[0]->{$this->passwordColumn};
Do the same with this line in the login method:
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;
}
}
The above code changes to this:
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;
}
}
This code can be found in Relationships-DatabaseTableEntity.
After submitting the form, you’ll see an error on the list page, but you can check the new saveEdit method is working by logging in to the website and adding a joke. When you’re redirected to the list page, you’ll see an error, but you can check that the joke has been added by viewing the contents of the joke table in MySQL Workbench.
Joke Objects
Now we’ll fix the joke list page. At the moment it displays an error, thanks to this code in the controller:
$author = $this->authorsTable->
findById($joke['authorId']);
$jokes[] = [
'id' => $joke['id'],
'joketext' => $joke['joketext'],
'jokedate' => $joke['jokedate'],
'name' => $author['name'],
'email' => $author['email']
];
The error occurs because $author and $joke are no longer arrays. This is a simple fix: change the syntax that reads from an array to use the object syntax:
$author = $this->authorsTable->
findById($joke->authorId);
$jokes[] = [
'id' => $joke->id,
'joketext' => $joke->joketext,
'jokedate' => $joke->jokedate,
'name' => $author->name,
'email' => $author->email
];
You’ll also need to change the method’s return statement to use the object syntax for the $author variable.
return ['template' => 'jokes.html.php',
'title' => $title,
'variables' => [
'totalJokes' => $totalJokes,
'jokes' => $jokes,
'userId' => $author->id ?? null
]
];
Now also amend the delete method to read the joke’s authorId from the new object:
if ($joke->authorId != $author->id)
This code can be found in Relationships-Objects.
Although this solution works, now that we’re using an object-oriented approach, it can be solved in a much nicer way. Currently, each value from either the author or joke table is stored under an equivalent key in the $jokes array.
The code for generating the $jokes array looks like this:
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
];
}
The $jokes array is used to provide the template access to each joke and its author.
The process currently looks like this:
- query the database and select all the jokes
- loop over each joke and:
- select the related author
- create a new array containing all the information about the joke and the author
- pass this constructed array to the template for display
This is a very long-winded process for something we can make a lot simpler using OOP.
At the moment, we can fetch all the jokes by a specific author using $author->getJokes(). However, we can also model the inverse relationship and do something like this:
echo $joke->getAuthor()->name;
This would let us get the author for any given joke, and this code could even be run from the template.
If the $this->jokesTable->findAll(); call returned an array of joke objects, each with their own getAuthor method, this process of creating an array with both sets of data would be unnecessary!
Firstly, let’s create the Joke entity class in Ijdb/Entity/Joke.php:
<?php
namespace IjdbEntity;
class Joke
{
public $id;
public $authorId;
public $jokedate;
public $joketext;
private $authorsTable;
public function __construct(NinjaDatabaseTable
$authorsTable)
{
$this->authorsTable = $authorsTable;
}
public function getAuthor()
{
return $this->authorsTable->
findById($this->authorId);
}
}
The Joke class works in the same way as the Author class: it has a constructor that asks for an instance of a DatabaseTable class that contains related data. In this case, it will be passed the DatabaseTable instance, which represents the author database table.
The getAuthor method returns the author for the current joke. If $this->authorId is 5, it will return an Author object that represents the author with the ID 5.
Using the Joke Class
To use the new Joke class, we’ll need to update the IjdbRoutes class to provide the authorsTable instance as a constructor argument. The relevant section of code currently looks like this:
$this->jokesTable = new NinjaDatabaseTable($pdo,
'joke', 'id', 'IjdbEntityJoke',
[$this->authorsTable]);
$this->authorsTable = new NinjaDatabaseTable($pdo,
'author', 'id', 'IjdbEntityAuthor',
[$this->jokesTable]);
This will pass the authorsTable instance to the jokesTable instance and the authorsTable instance.
Think about that for a second. It poses a problem that’s not immediately obvious.
If the authorsTable instance constructor requires an instance of jokesTable, and the jokesTable constructor requires an authorsTable instance, we have a catch-22: to create the jokesTable instance, you need an existing authorsTable instance. To create the authorsTable instance, you need an existing jokesTable instance. Both instances require the other instance to exist before they do!
If you try the code above, the PDO library will throw an exception—“Cannot call constructor”. Although the message isn’t very clear, it’s because of the problem I just highlighted.
This catch-22 occurs sometimes in object-oriented programming. Luckily, in this case it can be fairly easily solved using something called a reference.
References
A reference is special type of variable, a bit like a shortcut in Windows, or a symlink in macOS or Linux. A shortcut on your computer is a special type of file that doesn’t contain any data itself. Instead, it points to another file. When you open the shortcut, it actually opens the file the shortcut is pointing to.
References work in a similar way. Instead of a variable containing a specific value, a it can contain a reference to another variable. When you read the value of a variable that stores the reference, it will read the value of the variable being referenced.
To create a reference, you prefix the variable you want to create a reference to with an ampersand (&):
$originalVariable = 1;
$reference = &$originalVariable;
$originalVariable = 2;
echo $reference;
The code above will print 2. Without the &, it would print 1! That’s because the variable $reference contains a reference to the $originalVariable variable. Whenever the value of $reference is read, it will actually go off and read the value of the variable $originalVariable as it is at that moment in time.
This is important, because it allows us to solve the catch-22 we encountered earlier. By providing references as the constructor arguments for the Joke and Author classes, by the time the authorsTable and jokesTable instances are needed, they’ll have been created:
$this->jokesTable = new NinjaDatabaseTable($pdo,
'joke', 'id', 'IjdbEntityJoke',
[&$this->authorsTable]);
$this->authorsTable = new NinjaDatabaseTable($pdo,
'author', 'id', 'IjdbEntityAuthor',
[&$this->jokesTable]);
Now, when the DatabaseTable class creates a Joke or Author object and has to provide it the authorsTable or jokesTable instance, it will read what’s stored in the authorsTable or jokesTable class variables at the time any Author or Joke entity is instantiated.
Simplifying the List Controller Action
The following is now possible inside a controller:
$joke = $this->jokesTable->findById(123);
echo $joke->getAuthor()->name;
Now that a joke is an object, we can pass the whole object into the template and read the author from there.
Remove this code from the list method:
$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
];
}
This code was necessary when using arrays, because for each joke, we needed to fetch the information about the joke, and the information about the author of that particular joke, then combine them into a single data structure.
However, now that we’re using objects, this ugly copying code is redundant. Once we’ve removed it, we can also replace this code:
$result = $this->jokesTable->findAll();
… with this:
$jokes = $this->jokesTable->findAll();
The DatabaseTable class now provides an array of objects rather than arrays. Each Joke object has a method getAuthor, which returns the author of the joke.
Rather than fetching the author in the controller, we can now do it in the template jokes.html.php. Let’s update the template to use the new objects rather than the arrays:
Relationships-JokeObject
<p><?=$totalJokes?> jokes have been submitted to the Internet Joke Database.</p>
<?php foreach ($jokes as $joke): ?>
<blockquote>
<p>
<?=htmlspecialchars($joke->joketext, ENT_QUOTES,
'UTF-8')?>
(by <a href="mailto:<?=htmlspecialchars(
$joke->getAuthor()->email,
ENT_QUOTES, 'UTF-8'
); ?>">
<?=htmlspecialchars(
$joke->getAuthor()->name,
ENT_QUOTES,
'UTF-8'
); ?></a> on
<?php
$date = new DateTime($joke->jokedate);
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; ?>
For the fields from the joke table, this is fairly simple. We just change the syntax from an array to an object. For example, $joke['joketext'] becomes $joke->joketext.
It’s slightly more complicated where we want to read information about each joke’s author. Before reading the author’s email, we need to fetch the author instance. To read the author’s email, we previously used $joke['email'], which now becomes $joke->getAuthor()->email.
This actually fetches the author from within the template! Previously, when writing the controller, we had to anticipate exactly which variables were needed by the template.
Now, the controller just provides a list of jokes. The template can now read any of the values it needs, including information about the author. If we added a new column in the database—for example, a joke category—we could amend the template to show this value without needing to change the controller.
Tidying Up
Now that we’ve changed the way the DatabaseTable class works, we’ve broken the “Edit Joke” page. To fix it, open up the template editjoke.html.php and replace the array syntax with object syntax for accessing the joke’s properties:
Relationships-EditJoke
<?php if (empty($joke->id) || $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 now got an almost entirely object-oriented website! All the entities have their own class, and we can add any methods we like to each entity class.
Caching
You might have noticed a potential performance problem with the Joke entity class. The getAuthor method looks like this:
public function getAuthor() {
return $this->authorsTable->findById($this->authorId);
}
Although this works fine, it’s unnecessarily slow. Each time this getAuthor method is called, it will send the same query to the database and retrieve the same result. The following code will send three queries to the database:
echo $joke->getAuthor()->name;
echo $joke->getAuthor()->email;
echo $joke->getAuthor()->password;
Querying the database is considerably slower than just reading a value from a variable. Each time a query is sent to the database, it will slow down the page’s speed slightly. Although each query adds only a tiny overhead, if this is done inside a loop on a page, it can cause a noticeable slowdown.
To avoid this problem, you can fetch the author object once, then use the existing instance:
$author = $joke->getAuthor();
echo $author->name;
echo $author->name;
echo $author->password;
By doing this, we avoid sending three queries to the database, because getAuthor is only called once. This method works, but it’s rather crude. You have to remember to implement this technique, and on a larger website you’d have to mentally keep track of all the places you’d need to do this.
Instead, it’s better to implement a technique called transparent caching. The term caching refers to storing some data for quicker access later on, and the technique I’m about to show you is called transparent caching because the person using the class doesn’t even need to know it’s happening!
To implement caching, add a property to the Joke entity class to store the author between method calls:
class Joke {
// …
public $joketext;
private $authorsTable;
private $author;
// …
Then, in the getAuthor method, we can add logic that will do the following:
- check to see if the
authorclass variable has a value - if it’s empty, fetch the author from the database and store it in the class variable
- return the value stored in the
authorvariable
public function getAuthor() {
if (empty($this->author)) {
$this->author = $this->authorsTable->findById($this->authorId);
}
return $this->author;
}
You can find this code in Relationships-Cached.
With this simple if statement in place, the database will only be queried the first time the getAuthor method is called on any given joke instance.
Now, the following code will only send a single query to the database:
echo $joke->getAuthor()->name;
echo $joke->getAuthor()->email;
echo $joke->getAuthor()->password;
By solving the potential performance issue inside the class, it no longer matters how it’s used externally. There will only ever be a single query for each instance of the class.
Joke Categories
Now that you know how to add relationships between different tables and model them using classes, let’s add a new relationship.
At the moment, we have a single list of jokes. As we only have half a dozen jokes on the website, this works fine, but moving forward as more people register for the website and post jokes, the joke list page will keep getting longer!
People viewing the website may want to view a specific type of joke. For example, programming jokes, knock-knock jokes, one-liners, puns and so on.
The most obvious way to achieve this is the way we modeled the relationship between jokes and authors: create a category table to list the different categories, then create a categoryId column in the joke table to allow each joke to be placed in a category.
However, a joke may fall into more than one category.
Before modeling the relationship, let’s add a new form that allows creating new categories and storing them in the database. It’s been a while since we’ve added a new page to the website, and we’ve made a few changes since we last did, so I’ll go through this in detail.
Firstly, let’s create the table to store the categories:
CREATE TABLE `ijdb_sample`.`category` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NULL,
PRIMARY KEY (`id`));
Either use the SQL code above or use MySQL Workbench to create the category table with two columns: id and name. id is the primary key, so make sure it’s AUTO_INCREMENT, and type should be VARCHAR.
Create a new controller called Category in Ijdb/Controllers/Category.php. This controller will need access to a DatabaseTable instance that allows interacting with the new category table.
<?php
namespace IjdbControllers;
class Category
{
private $categoriesTable;
public function __construct(NinjaDatabaseTable
$categoriesTable)
{
$this->categoriesTable = $categoriesTable;
}
}
Like the controller for jokes, we’ll need several actions: list to display a list of categories, delete to delete a category, edit to display the add/edit form, and saveEdit to handle the form submission.
Let’s add the form first. Create the template editcategory.html.php:
<form action="" method="post">
<input type="hidden"
name="category[id]"
value="<?=$category->id ?? ''?>">
<label for="categoryname">Enter category name:
</label>
<input type="text"
id="categoryname"
name="category[name]"
value="<?=$category->
name ?? ''?>" />
<input type="submit" name="submit" value="Save">
</form>
You can find this code in Relationships-AddCategory.
Like editjoke.html.php, this template will be used for both the edit and add pages, so we’ll pre-fill the text box if the $category variable is set.
Add the edit method to the new Category class:
public function edit() {
if (isset($_GET['id'])) {
$category = $this->categoriesTable->findById($_GET['id']);
}
$title = 'Edit Category';
return ['template' => 'editcategory.html.php',
'title' => $title,
'variables' => [
'category' => $category ?? null
]
];
}
Open up IjdbRoutes. The first thing to do here is to create the instance of the DatabaseTable class for the category table. Like authorsTable and jokesTable, this can be stored in a class variable and created in the constructor:
class IjdbRoutes implements NinjaRoutes {
private $authorsTable;
private $jokesTable;
private $categoriesTable;
private $authentication;
public function __construct() {
include __DIR__ . '/../../includes/DatabaseConnection.php';
$this->jokesTable = new NinjaDatabaseTable($pdo,
'joke', 'id', 'IjdbEntityJoke',
[&$this->authorsTable]);
$this->authorsTable = new NinjaDatabaseTable($pdo,
'author', 'id',
'IjdbEntityAuthor',
[&$this->jokesTable]);
$this->categoriesTable = new NinjaDatabaseTable($pdo, 'category', 'id');
// …
Notice that I haven’t specified an entity class for the category table. Unless we need to add some functionality to it, it can use the stdClass class that we set as the default.
Next, create an instance of the Category controller and add the route for the edit page. We’ll display this on the URL /category/edit:
Relationships-AddCategory2
$authorController = new IjdbControllersRegister
($this->authorsTable);
$loginController = new IjdbControllersLogin
($this->authentication);
$categoryController = new IjdbControllersCategory
($this->categoriesTable);
$routes = [
// …
'category/edit' => [
'POST' => [
'controller' => $categoryController,
'action' => 'saveEdit'
],
'GET' => [
'controller' => $categoryController,
'action' => 'edit'
],
'login' => true
],
// …
If you’ve followed the steps above, visit http://192.168.10.10/category/edit and you should see the form. We’ve already set up the route, so let’s add the saveEdit method to add a category to the database when the form is submitted:
Relationships-AddCategory-Save
public function saveEdit() {
$category = $_POST['category'];
$this->categoriesTable->save($category);
header('location: /category/list');
}
If you test the form and press Submit, you’ll see an error message, because we haven’t built the list page yet. However, you can check the form is working by selecting all the records from the table in MySQL Workbench.
Once a category has been added, you should even be able to edit it by visiting http://192.168.10.10/category/edit?id=1.
At this point, it’s worth momentarily stepping back and considering the benefit of the classes and framework we’ve built. With fairly little effort, we’ve created a form that allows inserting data into the database, loading a record into it and editing. Think back to Chapter 4, when we were manually writing INSERT and UPDATE queries, and think about how much more code you would have needed to write with that approach!
List Page
We can also add the list page with fairly little code. Firstly, let’s create a template, categories.html.php, that loops through a list of categories and displays an edit/delete button for each of them:
<h2>Categories</h2>
<a href="/category/edit">Add a new category</a>
<?php foreach ($categories as $category): ?>
<blockquote>
<p>
<?=htmlspecialchars($category->name,
ENT_QUOTES, 'UTF-8')?>
<a
href="/category/edit?id=<?=$category->id?>">
Edit</a>
<form action="/category/delete" method="post">
<input type="hidden"
name="id"
value="<?=$category->id?>">
<input type="submit" value="Delete">
</form>
</p>
</blockquote>
<?php endforeach; ?>
Here’s the controller action in Category.php:
public function list() {
$categories = $this->categoriesTable->findAll();
$title = 'Joke Categories';
return ['template' => 'categories.html.php',
'title' => $title,
'variables' => [
'categories' => $categories
]
];
}
And finally, here’s the route in IjdbRoutes:
'category/list' => [
'GET' => [
'controller' => $categoryController,
'action' => 'list'
],
'login' => true
],
You can find this code in Relationships-ListCategories.
The Edit link from the template already works. Add the route and controller action for the Delete button to finish the category management pages:
'category/delete' => [
'POST' => [
'controller' => $categoryController,
'action' => 'delete'
],
'login' => true
],
public function delete() {
$this->categoriesTable->delete($_POST['id']);
header('location: /category/list');
}
Example: Relationships-DeleteCategory
Assigning Jokes to Categories
Now that categories can be added to the website, let’s add the ability to assign a joke to a category.
As I mentioned previously, the simplest approach would be to have a <select> box on the add joke page, which sets a categoryId column in the joke table.
However, this approach is the least flexible. It’s possible that a joke will fall into more than one category. Instead, we’re going to model this relationship using a join table—that is, a table with just two columns.
In our case, the columns will be jokeId and categoryId.
Let’s create the following table using either MySQL Workbench or by running this query:
CREATE TABLE `ijdb_sample`.`joke_category` (
`jokeId` INT NOT NULL,
`categoryId` INT NOT NULL,
PRIMARY KEY (`jokeId`, `categoryId`));
Note that both the jokeId and categoryId columns are the primary key. As I explained in Chapter 4, this is to prevent the same joke being added to a category twice.
Before continuing, let’s add some categories to the database. Using the form at http://192.168.10.10/category/edit, add the categories “programming jokes” and “one-liners”.
Currently we have three jokes, each of which falls into the “programming jokes” category:
How many programmers does it take to screw in a lightbulb? None, it’s a hardware problem.
Why did the programmer quit his job? He didn’t get arrays.
Why was the empty array stuck outside? It didn’t have any keys.
Here are some more jokes that fit in the two categories:
Bugs come in through open Windows. (“programming jokes” and “one-liners” categories)
How do functions break up? They stop calling each other. (“programming jokes” only)
You don’t need any training to be a litter picker, you pick it up on the job. (“one-liners” only)
Venison’s dear, isn’t it? (“one-liners” only)
It’s tricky being a magician. (“one-liners” only)
Don’t add these just yet. We’ll amend the Add Joke page to allow selecting categories and then add the jokes.
Instead of using a <select> box, we’ll use a series of checkboxes to allow users to select which categories a joke falls into.
As the Joke controller will now need to be able to pass a list of categories to the editjoke.html.php template, let’s amend the controller with a new constructor argument and class variable:
class Joke {
private $authorsTable;
private $jokesTable;
private $categoriesTable;
private $authentication;
public function __construct(DatabaseTable $jokesTable,
DatabaseTable $authorsTable,
DatabaseTable $categoriesTable,
Authentication $authentication) {
$this->jokesTable = $jokesTable;
$this->authorsTable = $authorsTable;
$this->categoriesTable = $categoriesTable;
$this->authentication = $authentication;
}
// …
Aesthetic Choices
For consistency, I’ve placed the argument before the Authentication argument. There’s no practical reason for this; it’s an entirely aesthetic choice to put all the DatabaseTable instances together.
Now we should pass in the categoriesTable DatabaseTable instance when the Joke controller is instantiated in IjdbRoutes:
$jokeController = new IjdbControllersJoke
($this->jokesTable, $this->authorsTable,
$this->categoriesTable,
$this->authentication);
In the edit method, pass the list of categories to the template:
public function edit() {
$author = $this->authentication->getUser();
$categories = $this->categoriesTable->findAll();
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,
'categories' => $categories
]
];
}
}
The next step is to set up the list of categories in the editjoke.html.php template and create a checkbox for each:
<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>
<p>Select categories for this joke:</p>
<?php foreach ($categories as $category): ?>
<input type="checkbox"
name="category[]"
value="<?=$category->id?>" />
<label><?=$category->name?></label>
<?php endforeach; ?>
<input type="submit" name="submit" value="Save">
</form>
The code here should be familiar by now, but I’ll quickly go over the additions I’ve made.
<?php foreach ($categories as $category): ?>: loop over each of the categories.
<input type="checkbox" name="category[]" value="<?=$category->id?>" />: this creates a checkbox for each category with the value property set to the ID of the category.
name="category[]": by setting the name of the checkbox to category[], an array will be created when the form is submitted. For example, if you checked the checkboxes with the values 1 and 3, the variable $_POST['category'] would contain an array with the values 1 and 3 (['1', '3']).
If you try the code above, the formatting will look strange. Add the following CSS to jokes.css to fix it:
form p {clear: both;}
input[type="checkbox"] {float: left; clear: left; width: auto; margin-right: 10px;}
input[type="checkbox"] + label {clear: right;}
You can find this code in Relationships-JokeCategory
Now that the categories are listed and selectable on the Add Joke page, we need to change the saveEdit method to handle new data from the form submission.
It’s important to understand what needs to happen here. When the form is submitted, an array of the IDs of the checked categories will be sent as a $_POST variable. Each category ID and the ID of the new joke will then be written to the joke_category table.
Before we can add records to the joke_category table, we’ll need a DatabaseTable instance for it. Add the class variable and create the instance in IjdbRoutes:
// …
private $jokeCategoriesTable;
public function __construct() {
include __DIR__ . '/../../includes/DatabaseConnection.php';
$this->jokesTable = new NinjaDatabaseTable($pdo,
'joke', 'id', 'IjdbEntityJoke',
[&$this->authorsTable]);
$this->authorsTable = new NinjaDatabaseTable($pdo,
'author', 'id',
'IjdbEntityAuthor',
[&$this->jokesTable]);
$this->categoriesTable = new NinjaDatabaseTable($pdo,
'category', 'id');
$this->jokeCategoriesTable =
new NinjaDatabaseTable($pdo,
'joke_category', 'categoryId');
// …
We could pass the jokeCategoriesTable instance to the Joke controller. However, like we did with the $author->addJoke method, it’s better to implement this using an object-oriented approach, where a joke is added to a category using this code:
$joke->addCategory($categoryId);
To do this, we’ll need a Joke entity instance in the saveEdit method in the Joke controller. The code for the updated saveEdit method will look like this:
public function saveEdit() {
$author = $this->authentication->getUser();
$joke = $_POST['joke'];
$joke['jokedate'] = new DateTime();
$jokeEntity = $author->addJoke($joke);
foreach ($_POST['category'] as $categoryId) {
$jokeEntity->addCategory($categoryId);
}
header('location: /joke/list');
}
The important change to what we have at the moment is the line $jokeEntity = $author->addJoke($joke);.
The addJoke method, which currently does not have a return value, will need to return a Joke entity instance, which represents the joke that has just been added to the database.
As a first thought, a simple approach would be to fetch the joke from the jokesTable instance after it’s been created, using the following process:
- take the data for the new joke from
$_POST - pass it to the
addJokemethod in theAuthorentity class - retrieve the newly added joke from the database using a
SELECTquery (or thefindByIdmethod)
For example:
public function addJoke($joke) {
$joke['authorId'] = $this->id;
// Store the joke in the database
$this->jokesTable->save($joke);
// Fetch the new joke as an object
return $this->jokesTable->findById($id);
}
There are two problems with this:
We don’t know what the newly created joke’s
idis.It adds additional overhead. We’re actually making two trips to the database—once to run an
INSERTquery to send the data about the new joke, and then aSELECTquery to fetch that very same information back out of the database immediately afterwards! We already have the information in the$jokevariable, so there’s no need to fetch it from the database here.
Although, it would be possible to create the Joke entity instance in the addJoke method, it makes more sense to place this functionality in the DatabaseTable class. Whenever save is called, it can return the relevant entity instance. By placing this logic inside the save method, any time any data is written to any database table, the save method will return an object representing that newly added record.
The addJoke method above should be changed to this:
public function addJoke($joke) {
$joke['authorId'] = $this->id;
return $this->jokesTable->save($joke);
}
This will just return the return value of the save method from the DatabaseTable class. That is, whatever the save method returns to the addJoke method will also be returned by the addJoke method.
Open up the DatabaseTable class and find the save method:
public function save($record) {
try {
if ($record[$this->primaryKey] == '') {
$record[$this->primaryKey] = null;
}
$this->insert($record);
}
catch (PDOException $e) {
$this->update($record);
}
}
This calls either the insert or update method. The first thing we need to do here is create an instance of the relevant entity class. For jokes, it will be the Joke class; for authors, it will be the Author class; and so on.
To create the relevant entity, we can use this code:
$entity = new $this->className(...$this->constructorArgs);
This looks complicated. There are lots of variables, and a brand new operator! It’s important to understand what’s happening here, so I’ll show you how this works.
Let’s think about what we want to do for the joke class. To create an instance of the Joke entity class, we’d need the following code:
$joke = new IjdbEntityJoke($authorsTable);
The class name, IjdbEntityJoke, is stored in the $this->className variable, so the above could be expressed like this:
$joke = new $this->className($authorsTable);
And this will still work in the same way.
However, each entity class has different arguments and, potentially, a different number of arguments. The Author entity class, for example, requires the $jokesTabe instance. We already have the list of arguments for the entity class in the $this->constructorArgs variable as an array.
The ... operator, known as the argument unpacking operator or splat operator, allows specifying an array in place of several arguments.
For example, consider this code:
$array = [1, 2];
someFunction(...$array);
It’s the same as this:
someFunction(1, 2);
In our use case, this code:
$entity = new $this->className(...$this->constructorArgs);
… is equivalent to this:
$entity = new $this->className($this->constructorArgs[0], $this->constructorArgs[1]);
Once this code has run, the $entity variable will store an object. The type of that object will depend on the class defined in $this->className. For the $jokesTable instance, it will be an instance of the Joke entity class.
This line can be placed inside the save method, along with a line to return the newly created entity object:
public function save($record) {
$entity = new $this->
className(...$this->constructorArgs);
try {
if ($record[$this->primaryKey] == '') {
$record[$this->primaryKey] = null;
}
$this->insert($record);
}
catch (PDOException $e) {
$this->update($record);
}
return $entity;
}
With those lines in place, the save method will instantiate the relevant, empty, entity class. The next stage is writing the data that was sent to the database to the class. This can be done with a simple foreach:
public function save($record) {
$entity = new $this->
className(...$this->constructorArgs);
try {
if ($record[$this->primaryKey] == '') {
$record[$this->primaryKey] = null;
}
$this->insert($record);
}
catch (PDOException $e) {
$this->update($record);
}
foreach ($record as $key => $value) {
if (!empty($value)) {
$entity->$key = $value;
}
}
return $entity;
}
The important line here is $entity->$key = $value;. Each time foreach iterates, the $key variable is set to the column name—for example joketext—and the $value variable is set to the value being written to that column. By using the $key variable after the object access operator (->), it will write to the property with the name of the column.
The if (!empty($value)) check is in place to prevent values that are already set on the entity (such as the primary key) being overwritten with null.
$record = ['joketext' => 'Why did the empty array get stuck outside? It didn't have any keys',
'authorId' => 1,
'jokedate' => '2017-06-22'];
foreach ($record as $key => $value) {
if (!empty($value)) {
$joke->$key = $value;
}
}
The code above will have the same result as this:
$joke->joketext = 'Why did the empty array get stuck outside? It didn't have any keys';
$joke->authorId = 1;
$joke->jokedate = '2018-06-22';
Converting Arrays to Objects
This approach is a common method of converting an array to an object.
With the foreach in place, writing the values to the entity object, the save method will return the object with all the values that were passed as an array to the method.
This will work fine for records being updated, as the $record variable will include keys for all the columns in the database table.
A newly created record, however, will not have the primary key set. When a joke is added, we pass the save method values for the joketext, jokedate and authorId columns, not the id. In fact, we couldn’t pass the id even if we wanted to, as we don’t know what it will be before the record has been created in the database.
The id primary key is actually created by MySQL inside the database. For INSERT queries, we’ll need to read the value from the database immediately after the record has been added.
Luckily, the PDO library provides a very simple method of doing this. After an INSERT query has been sent to the database, you can call the lastInsertId method on the PDO instance to read the ID of the last record inserted.
To implement this, let’s amend the insert method in our DatabaseTable class to return the last insert ID:
private function insert($fields) {
$query = 'INSERT INTO `' . $this->table . '` (';
foreach ($fields as $key => $value) {
$query .= '`' . $key . '`,';
}
$query = rtrim($query, ',');
$query .= ') VALUES (';
foreach ($fields as $key => $value) {
$query .= ':' . $key . ',';
}
$query = rtrim($query, ',');
$query .= ')';
$fields = $this->processDates($fields);
$this->query($query, $fields);
return $this->pdo->lastInsertId();
}
Now, the save method can read this value and set the primary key on the created entity object:
public function save($record) {
$entity = new $this->
className(...$this->constructorArgs);
try {
if ($record[$this->primaryKey] == '') {
$record[$this->primaryKey] = null;
}
$insertId = $this->insert($record);
$entity->{$this->primaryKey} = $insertId;
}
catch (PDOException $e) {
$this->update($record);
}
foreach ($record as $key => $value) {
$entity->$key = $value;
}
return $entity;
}
I’ve used the shorthand method with braces to set the primary key, but it’s the same as this:
$insertId = $this->insert($record);
$primaryKey = $this->primaryKey;
$entity->$primaryKey = $insertId;
The save method is now complete. Any time the save method is called, it will return an entity instance representing the record that’s just been saved.
Assigning Categories to Jokes
This functionality is required to enable the joke controller to assign categories to a joke instance. We can amend the saveEdit method in the Joke controller to look like this:
public function saveEdit() {
$author = $this->authentication->getUser();
$joke = $_POST['joke'];
$joke['jokedate'] = new DateTime();
$jokeEntity = $author->addJoke($joke);
foreach ($_POST['category'] as $categoryId) {
$jokeEntity->addCategory($categoryId);
}
header('location: /joke/list');
}
Now that $author->addJoke($joke); returns a Joke entity object, we can call methods on an entity representing the record that has just been inserted. In this case, $jokeEntity->addCategory($categoryId); can be used to assign a category to the joke that was just added to the database.
Of course, for this to work we’ll need to make some changes to the Joke entity class.
As the addCategory method will write a record to the new joke_category table, it will need a reference to the jokeCategoriesTable DatabaseTable instance. You know the drill here: add a class variable and constructor argument:
<?php
namespace IjdbEntity;
class Joke {
public $id;
public $authorId;
public $jokedate;
public $joketext;
private $authorsTable;
private $author;
private $jokeCategoriesTable;
public function __construct(NinjaDatabaseTable
$authorsTable, NinjaDatabaseTable
$jokeCategoriesTable) {
$this->authorsTable = $authorsTable;
$this->jokeCategoriesTable = $jokeCategoriesTable;
}
// …
Then amend IjdbRoutes to provide the instance as an argument to the constructor of the $jokesTable instance:
$this->jokesTable = new NinjaDatabaseTable($pdo,
'joke', 'id', 'IjdbEntityJoke',
[&$this->authorsTable,
&$this->jokeCategoriesTable]);
By adding &$this->jokeCategoriesTable to the array passed as the fifth argument, each time an instance of IjdbEntityJoke is created inside the $jokesTable instance, the constructor will be called with the authorsTable and jokeCategoriesTable instances.
Next, add the addCategory method to the Joke entity class:
public function addCategory($categoryId) {
$jokeCat = ['jokeId' => $this->id,
'categoryId' => $categoryId];
$this->jokeCategoriesTable->save($jokeCat);
}
You can find this code in Relationships-AssignCategory
This code is fairly simple. The first line creates an array that represents the record to be added. The jokeId is the id of the joke that we’re adding the category to, and the categoryId comes from the argument.
With this code in place, whenever a joke is added to the website it’s assigned to the categories that were checked.
Go ahead and add the jokes I supplied earlier, or your own, and verify that the records have been added to the joke_category join table by selecting the records from the table in MySQL Workbench.
Displaying Jokes by Category
Now that we have jokes in the database that are assigned to a category, let’s add a page that allows selecting jokes by category.
On the Joke List page, let’s add a list of categories to allow filtering of the jokes.
The first part is fairly simple: we need a list of categories as links on the Joke List page. This involves two fairly simple steps:
- Amend the
listaction to pass a list of categories to the template:
public function list() {
$jokes = $this->jokesTable->findAll();
$title = 'Joke list';
$totalJokes = $this->jokesTable->total();
$author = $this->authentication->getUser();
return ['template' => 'jokes.html.php',
'title' => $title,
'variables' => [
'totalJokes' => $totalJokes,
'jokes' => $jokes,
'userId' => $author->id ?? null,
'categories' => $this->categoriesTable->findAll()
]
];
}
- Loop through the categories in the
jokes.html.phptemplate and create a list with links for each one:
<ul class="categories">
<?php foreach ($categories as $category): ?>
<li><a href="/joke/list?category=
<?=$category->id?>">
<?=$category->name?></a><li>
<?php endforeach; ?>
</ul>
To make it look a little nicer, you can also add a containing div and a div around the list of jokes:
<div class="jokelist">
<ul class="categories">
<?php foreach ($categories as $category): ?>
<li><a href="/joke/list?category=
<?=$category->id?>">
<?=$category->name?></a><li>
<?php endforeach; ?>
</ul>
<div class="jokes">
<p><?=$totalJokes?> jokes have been submitted to the Internet Joke Database.</p>
<?php foreach ($jokes as $joke): ?>
// …
<?php endforeach; ?>
</div>
Then apply the following CSS:
.jokelist {display: table;}
.categories {display: table-cell; width: 20%; background-color: #333; padding: 1em; list-style-type: none;}
.categories a {color: white; text-decoration: none;}
.categories li {margin-bottom: 1em;}
.jokelist .jokes {display: table-cell; padding: 1em;}
You can find this code in Relationships-CategoryList.
That’s the list done, but the links currently don’t do anything. Each link sets a $_GET variable called category to the ID of the category we want to view. If you click on one of the new category links, you’ll see the page you visit is /jokes/list?category=1 or similar.
You’ve likely already worked out what we need to do now. We need to use the new $_GET variable to display a filtered list of jokes if the category variable is set.
If we had done a simpler relationship with a categoryId column in the joke table, this would be relatively simple. In the list controller action, we’d just amend the way in which the $jokes variable was set:
if (isset($_GET['category'])) {
$jokes = $this->jokesTable->find('category',
$_GET['category']);
}
else {
$jokes = $this->jokesTable->findAll();
}
However, as we have a many-to-many relationship, it's not quite so simple. One option is to pass the jokeCategoriesTable to the controller and do something like this:
if (isset($_GET['category'])) {
$jokeCategories = $this->jokeCategoriesTable->
find('categoryId', $_GET['categoryId']);
$jokes = [];
foreach ($jokeCategories as $jokeCategory) {
$jokes[] = $this->jokesTable->
findById($jokeCategory->jokeId);
}
}
else {
$jokes = $this->jokesTable->findAll();
}
In this example, we’re getting a list of all the records from the joke_cagegory table for the chosen category by its id—for example, 4. This gives us a set of records, each with a categoryId and a jokeId.
In our example, the categoryId will always be 4, because we’ve only selected the records from that category, but each record has a unique jokeId. We then loop through all the records and find the relevant record from the joke table, adding each joke record to the $jokes array.
If you want to go ahead and test this approach for yourself, use the code above in the list method, create the jokeCategoriesTable class variable, constructor argument, and pass the instance into the Joke controller from IjdbRoutes.
I haven’t given you the code for this because it’s not a great solution. One of the most difficult parts of programming is placing code in the right place. The logic above is correct. It works, and it was fairly simple to work out. However, it would be better if we could get a list of jokes from a category like this:
$category = $this->categoriesTable->
findById($_GET['category']);
$jokes = $category->getJokes();
Doing so would allow us to get a list of jokes from a category anywhere in the program, not just the list method.
You already know how to achieve this: we did the same thing with $joke->getAuthor(). In principle, this is the same.
We’ll need a Category entity class that has access to the jokesTable instance, the jokeCategoriesTable instance, and has a method called getJokes:
<?php
namespace IjdbEntity;
use NinjaDatabaseTable;
class Category
{
public $id;
public $name;
private $jokesTable;
private $jokeCategoriesTable;
public function __construct(DatabaseTable $jokesTable,
DatabaseTable $jokeCategoriesTable)
{
$this->jokesTable = $jokesTable;
$this->jokeCategoriesTable = $jokeCategoriesTable;
}
public function getJokes()
{
$jokeCategories = $this->jokeCategoriesTable->
find('categoryId', $this->id);
$jokes = [];
foreach ($jokeCategories as $jokeCategory) {
$joke = $this->jokesTable->
findById($jokeCategory->jokeId);
if ($joke) {
$jokes[] = $joke;
}
}
return $jokes;
}
}
Save this in classes/Ijdb/Entity/Category.php. You’ll notice the code in the getJokes method is almost the same as the code I showed you earlier. The only differences are that it uses $this->id instead of $_GET['category'] and returns the $jokes array. One minor change I’ve added is a safety precaution: the if ($joke) check ensures that a joke is only added to the $jokes array if it can be retrieved from the database.
Amend IjdbRoutes to set the categoriesTable instance to use the new Category entity class and provide the two constructor arguments:
$this->categoriesTable = new NinjaDatabaseTable($pdo, 'category', 'id', 'IjdbEntityCategory', [&$this->jokesTable, &$this->jokeCategoriesTable]);
Finally, use the new getJokes method to retrieve the jokes in the list controller action:
if (isset($_GET['category'])) {
$category = $this->categoriesTable->
findById($_GET['category']);
$jokes = $category->getJokes();
}
else {
$jokes = $this->jokesTable->findAll();
}
You can find this code in Relationships-CategoryList2
Using this approach, any time you need a list of jokes that exist in a category, you can find the category and then use $category->getJokes().
If you visit the Joke List page of the website, you’ll be able to click on the category links to filter the jokes.
Editing Jokes
We’ve got most of the functionality for placing jokes in categories, but you’ll notice a problem if you try to edit a joke. In fact, there are two problems.
The first, and the most obvious, can be found by editing a joke: try to edit one of the jokes that’s already in a category and you’ll immediately notice that the boxes aren’t checked.
To fix this, we need to amend the code that prints the checkboxes:
<p>Select categories for this joke:</p>
<?php foreach ($categories as $category): ?>
<input type="checkbox" name="category[]"
value="<?=$category->id?>" />
<label><?=$category->name?></label>
<?php endforeach; ?>
To check a checkbox, you add the attribute checked to the input element:
<input type="checkbox" checked name="category[]"
value="<?=$category->id?>" />
This is simple to add with an if statement to display checked if the joke is inside the category. The difficult part is determining if the joke is inside any given category.
We’ll also solve this in an object-oriented way. Let’s add a method in the joke entity so we can use this:
if ($joke->hasCategory($category->id))
Add the hasCategory method to the Joke entity class:
public function hasCategory($categoryId) {
$jokeCategories = $this->jokeCategoriesTable->
find('jokeId', $this->id);
foreach ($jokeCategories as $jokeCategory) {
if ($jokeCategory->categoryId == $categoryId) {
return true;
}
}
}
This works by finding all the categories that are associated with a joke, looping through them and checking to see whether one of those matches a given $categoryId.
With that in place, we can use it in the editjoke.html.php template:
<p>Select categories for this joke:</p>
<?php foreach ($categories as $category): ?>
<?php if ($joke &&
$joke->hasCategory($category->id)): ?>
<input type="checkbox" checked name="category[]"
value="<?=$category->id?>" />
<?php else: ?>
<input type="checkbox" name="category[]"
value="<?=$category->id?>" />
<?php endif; ?>
<label><?=$category->name?></label>
<?php endforeach; ?>
If you go to edit a joke, the relevant category boxes will now be checked, solving the first problem.
The second problem is a little more subtle. If you edit a joke but don’t change the categories, everything will appear to work. However, if you uncheck a box, the changes won’t be saved!
Try editing one of the jokes and unchecking all the category boxes before pressing Save. When you go back to edit the joke, the boxes will still be checked.
The reason for this issue is a common one when dealing with checkboxes in this way. Although we have some logic that says, “If the box is checked, add a record to the joke_category table,” we don’t have anything to remove that record after it’s been added and the checkbox has been unticked.
We could use this process:
- loop through every single category
- check to see if the corresponding checkbook box wasn’t checked
- if the box wasn’t checked and there’s a corresponding record, delete the record
We’d need do this check for every category, and it would take a fairly large amount of code to achieve.
Instead, a much simpler approach is to delete all the records from the joke_category table that are related to the joke we’re editing, then apply the same logic as before: loop through the checked boxes and insert records for each category that was checked.
Admittedly, this isn’t entirely efficient. If the joke is edited and the category checkboxes aren’t changed, this will cause unnecessary deletion and reinsertion of identical data. However, it’s still the simplest approach.
Our DatabaseTable class has a delete method that allows deleting a record by its primary key. However, our table has two primary keys—jokeId and categoryId—so we can’t use it as it currently is.
Instead, let’s add a deleteWhere method to the DatabaseTable class that works like the existing find method:
public function deleteWhere($column, $value) {
$query = 'DELETE FROM ' . $this->table . '
WHERE ' . $column . ' = :value';
$parameters = [
'value' => $value
];
$query = $this->query($query, $parameters);
}
The code here is identical to the find method, except that it sends a DELETE query to the database rather than a SELECT query. For example, $jokesTable->deleteWhere('authorId', 7) would delete all the jokes by the author with the id of 7.
Add a clearCategories method to the Joke entity class that removes all the related records from the jokeCategories table for a given joke.
public function clearCategories() {
$this->jokeCategoriesTable->deleteWhere('jokeId',
$this->id);
}
When $joke->clearCategories() is called, it will remove every record from the joke_category table that represents the joke stored in $joke. Add this call to the saveEdit method in the Joke controller:
public function saveEdit() {
$author = $this->authentication->getUser();
$joke = $_POST['joke'];
$joke['jokedate'] = new DateTime();
$jokeEntity = $author->addJoke($joke);
$jokeEntity->clearCategories();
foreach ($_POST['category'] as $categoryId) {
$jokeEntity->addCategory($categoryId);
}
header('location: /joke/list');
}
You can find this code in Relationships-ChangeCategories.
Test it for yourself by editing a joke and unchecking some categories, then going back and adding them again. If you’ve followed the steps here, you’ll be able to change the categories as you’d expect.
User Roles
We’ve now got a fully functional jokes website where users can register, post jokes, edit/delete their own submissions and view jokes by category.
But what happens if someone posts something you want to delete, or you want to fix a spelling mistake in someone else’s joke?
At the moment, you can’t do that! There’s a check in place that only allows authors to edit their own jokes. If someone else posts something, there’s currently no way for you to amend it.
The website is also set up so that anyone can add new categories. It would be better if only you (the website owner) were able to do that.
This is a very common problem on websites, and it’s generally solved with access levels, where different accounts can perform different tasks.
On our website, we would need at minimum the following access levels:
Standard users: can post new jokes and edit/delete jokes they’ve posted.
Administrators: can add/edit/remove categories, post jokes, and edit/delete jokes anyone has posted. They should also be able to turn other users into administrators.
The simplest way to do this is to have a column in the author table that represents the author’s access level. A 1 in the column could represent a normal user and, and a 2 could represent an administrator. It would then be easy to add a check on any page to determine whether or not the logged-in user was an administrator:
$author = $this->authentication->getUser();
if ($author->accessLevel == 2) {
// They're an administrator
}
else {
// Otherwise, they're not
}
This method is very simple to understand, and we could even very easily abstract it to if ($author->isAdmin()) to improve the readability.
This implementation of access levels is fine for small websites with only a few users, or when there’s only one administrator.
However, on larger, real-world websites, you often need to give users different levels of access. For example, we may want someone to be able to add categories, but not able to grant other people administrator access, or worse, revoke your administrator privileges so they have complete control of the website!
A more flexible approach is to give each user individual permissions for each action. For example, we could set a user up to be able to edit a category but not add an administrator.
For this website, we’ve already considered these permissions:
- edit other people’s jokes
- delete other people’s jokes
- add categories
- edit categories
- remove categories
- edit user access levels
Before we model this in the database, let’s think about how we’d check these in the existing code.
Our Author entity class could have a method called hasPermission that takes a single argument and returns true or false, depending on whether or not the user has a specific permission.
We could assign numbers to each of the permissions above, so that the following code could be used to check whether they have permission to edit other people’s jokes:
if ($author->hasPermission(1))
2 could represent deleting other users’ jokes, 3 could be whether members are allowed to add categories, and so on.
This is roughly what we want to do, but looking at the line of code above, it’s really not clear what’s happening. If you saw the code $author->hasPermission(6), you’d have to go away and look up what 6 meant.
To make the code much easier to read, each value can be stored inside a constant—which, like a variable, is a label given to a value. The difference, however, is that a constant always has the same value. The value is set once at the beginning of the program and can’t be changed.
In object-oriented programming, constants are defined in classes like so:
<?php
namespace IjdbEntity;
class Author {
const EDIT_JOKES = 1;
const DELETE_JOKES = 2;
const LIST_CATEGORIES = 3;
const EDIT_CATEGORIES = 4;
const REMOVE_CATEGORIES = 5;
const EDIT_USER_ACCESS = 6;
Constant Conventions
By convention, constants are written in uppercase, with words separated by underscores. Although it’s possible to use lowercase letters, it’s almost universal in every programming language that constants are written like this!
I’ve defined the constants in the Author entity class, since the permissions are related to authors.
We’ll also define a method called hasPermission in the class:
<?php
namespace IjdbEntity;
class Author {
const EDIT_JOKES = 1;
const DELETE_JOKES = 2;
const LIST_CATEGORIES = 3;
const EDIT_CATEGORIES = 4;
const REMOVE_CATEGORIES = 5;
const EDIT_USER_ACCESS = 6;
// …
public function hasPermission($permission) {
// …
}
Before we write the code for this method, I’m going to show you how it will be used:
$author = $this->authentication->getUser();
if ($author->hasPermission
(IjdbEntityAuthor::LIST_CATEGORIES)) {
// …
}
Notice that the constant is prefixed with the namespace and class name, then a ::. The :: operator is used to access constants in a given class.
When accessing a constant, you don’t need an instance to be created, as each instance would have the same value for the constant anyway.
There are two different places we’ll need to implement this. The first is page-level access. As we did with the login check, a check can be done in the router to stop people even viewing a page if they don’t have the correct permissions.
This will need to be done in EntryPoint, but because each website you build might have a different way of handling these checks, we’ll add a new method to IjdbRoutes called checkPermissions:
public function checkPermission($permission): bool {
$user = $this->authentication->getUser();
if ($user && $user->hasPermission($permission)) {
return true;
} else {
return false;
}
}
This fetches the current logged-in user and checks to see if they have a specific permission.
As this is something that will need to be provided by any website you build, amend the Routes interface to include the checkPermission method:
<?php
namespace Ninja;
interface Routes
{
public function getRoutes(): array;
public function getAuthentication(): NinjaAuthentication;
public function checkPermission($permission): bool;
}
To implement this in the EntryPointy class, we’ll add an extra entry to the $routes array to specify which permissions are required for accessing each page. Let’s start with the category permissions:
'category/edit' => [
'POST' => [
'controller' => $categoryController,
'action' => 'saveEdit'
],
'GET' => [
'controller' => $categoryController,
'action' => 'edit'
],
'login' => true,
'permissions' => IjdbEntityAuthor::EDIT_CATEGORIES
],
'category/delete' => [
'POST' => [
'controller' => $categoryController,
'action' => 'delete'
],
'login' => true,
'permissions' => IjdbEntityAuthor::REMOVE_CATEGORIES
],
'category/list' => [
'GET' => [
'controller' => $categoryController,
'action' => 'list'
],
'login' => true,
'permissions' => IjdbEntityAuthor::LIST_CATEGORIES
],
I’ve added the relevant permission requirements to each page using an extra key in the array. This only defines an extra value in the array. We’ll need to have the EntryPoint class call the checkPermission method that we just added to determine whether the logged-in user is allowed to view the page.
The process here is fairly straightforward. When you visit a page—for example, /category/edit—the EntryPoint class will read the value stored in the permissions key for that route, then call the new checkPermission method to determine whether the user who is logged in and viewing the page has that permission.
This will work in the same way as the login check:
if (isset($routes[$this->route]['login']) &&
!$authentication->isLoggedIn()) {
header('location: /login/error');
}
else if (isset($routes[$this->route]['permissions']) &&
!$this->routes->checkPermission
($$routes[$this->route]['permissions'])) {
header('location: /login/error');
}
else {
// …
Firstly, the login check is performed, then the permissions check. If the route contains a permissions key, the value stored under that key—for example, IjdbEntityAuthor::REMOVE_CATEGORIES—is passed to the checkPermission method, which determines whether the logged-in user has the required permission.
Since we haven’t written the code for the hasPermission method in the Author entity class yet, any permission check will always return false. Go ahead and try viewing the category list by visiting http://192.168.10.10/category/list.
You’ll see the error page that says “You are not logged in”. At this point, you might want to add a new template and route that displays a more accurate error message, but by now you should be very familiar with adding pages, so I’m not going to walk you through it!
You can find this code in Relationships-PermissionsCheck.
Creating a Form to Assign Permissions
Before we can implement the hasPermission method, the website needs a page that allows assigning permissions to any given user.
We’ll need two pages—one that lists all the authors, so we can select the one we want to give permissions to, and a second that contains a form with checkboxes for each permission.
Add the following routes:
'author/permissions' => [
'GET' => [
'controller' => $authorController,
'action' => 'permissions'
],
'POST' => [
'controller' => $authorController,
'action' => 'savePermissions'
],
'login' => true
],
'author/list' => [
'GET' => [
'controller' => $authorController,
'action' => 'list'
],
'login' => true
],
For now, we only have a login check on here. If we add a permissions check right now, we won’t be able to use the form to set the permissions, because your account won’t have the required permission to view the page!
Rather than adding a new controller, we’ll use the Register controller that already exists and is used for handling changes to users’ accounts.
Author List
Let’s do the list first. Add a method in the Register controller called list that fetches a list of all the registered users and passes them to the template:
public function list() {
$authors = $this->authorsTable->findAll();
return ['template' => 'authorlist.html.php',
'title' => 'Author List',
'variables' => [
'authors' => $authors
]
];
}
And here’s the template authorlist.html.php for listing the users:
<h2>User List</h2>
<table>
<thead>
<th>Name</th>
<th>Email</th>
<th>Edit</th>
</thead>
<tbody>
<?php foreach ($authors as $author): ?>
<tr>
<td><?=$author->name;?></td>
<td><?=$author->email;?></td>
<td>
<a href="/author/permissions?id=<?=$author->id;?>">
Edit Permissions</a></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
If you visit http://192.168.10.10/author/list, you’ll see the list of registered authors, each with a link for editing their permissions.
The Edit Permissions link goes to the /author/permissions page, and passes it the id of the author whose permissions we want to change.
Edit Author Permissions
There’s nothing on the Edit Permissions page yet, because we haven’t created it. It’s quite a simple page: it will display a checkbox for each permission in the system, and it will be checked if the author currently has that permission.
The template could look like this:
<input type="checkbox" value="1" <?php if ($author->hasPermission(EDIT_JOKES)) {
echo 'checked';
} ?> Edit Jokes
<input type="checkbox" value="2" <?php if ($author->hasPermission(DELETE_JOKES)) {
echo 'checked';
} ?> Delete Jokes
<input type="checkbox" value="3" <?php if ($author->hasPermission(LIST_CATEGORIES)) {
echo 'checked';
} ?> Add Categories
// etc.
This would work, but it requires storing the information about the permissions in two different places—the constants in the Author entity class, and the template. We also need to write out all the HTML and PHP for each checkbox.
Like most cases when we find repetition like this, there’s a much easier way!
It’s actually possible to read information about the variables, methods and constants that are contained inside a class using a tool called Reflection.
The class already contains a list of permissions and their values in the the class constants:
class Author {
const EDIT_JOKES = 1;
const DELETE_JOKES = 2;
const LIST_CATEGORIES = 3;
const EDIT_CATEGORIES = 4;
const REMOVE_CATEGORIES = 5;
const EDIT_USER_ACCESS = 6;
// …
Rather than repeating this information inside the template file by creating a checkbox for every permission, it would be better to read the list of available permissions from the class and loop through them to create the checkboxes. Then adding a new permission involves a single change: adding a constant to the class. With the approach above, you'd need to add the constant to the class and also add the checkbox to the template file.
You can read information about variables, methods and constants that are contained inside a class using a tool called Reflection. PHP makes this fairly simple. To reflect the Author entity class and read all its properties, you can use the following code:
$reflected = new ReflectionClass('IjdbEntityAuthor');
$constants = $reflected->getConstants();
The $constants array will contain information about all the constants that are defined within the class. If you use var_dump to print out the contents of the $constants variable, you’ll see an array with the constant names as the key and the constant values as the value:
array (size=6)
'EDIT_JOKES' => int 1
'DELETE_JOKES' => int 2
'LIST_CATEGORIES' => int 3
'EDIT_CATEGORIES' => int 4
'REMOVE_CATEGORIES' => int 5
'EDIT_USER_ACCESS' => int 6
Reflection
Reflection can be a very powerful tool, and I’ve only scratched the surface of what you can do. For more information on Reflection, see the PHP manual page.
By passing this array to the template, we can loop over the list of constants and create the checkboxes automatically.
Firstly, add this permissions method to the Register controller.:
public function permissions() {
$author = $this->authorsTable->findById($_GET['id']);
$reflected = new ReflectionClass('IjdbEntityAuthor');
$constants = $reflected->getConstants();
return ['template' => 'permissions.html.php',
'title' => 'Edit Permissions',
'variables' => [
'author' => $author,
'permissions' => $constants
]
];
}
Secondly, change the template to loop over the $permissions array, which is a list of constants from the class:
<h2>Edit <?=$author->name?>’s Permissions</h2>
<form action="" method="post">
<?php foreach ($permissions as $name => $value): ?>
<div>
<input name="permissions[]"
type="checkbox"
value="<?=$value?>"
<?php if ($author->hasPermission($value)):
echo 'checked'; endif; ?> />
<label><?=$name?>
</div>
<?php endforeach; ?>
<input type="submit" value="Submit" />
</form>
Adding a constant to the class will now result in the checkbox for that constant appearing on the user permissions page.
Like we did with categories, I’ve made the checkbox list an array. If you go to edit one of the user’s permissions, you’ll see a checkbox for each of the constants in the Author class!
This makes future developments a lot easier. Once the constant has been added to the Author entity class, it will automatically appear on the list without us having to manually go and edit the template each time we want to add a new permission to the website.
You can find this code in Relationships-EditPermissions
Setting Permissions
The next stage is storing the user’s permissions once you press save. Each user will have a set of permissions, and there are many different ways to represent this in the database.
We could do it in the same way we did with categories: create a user_permission table with two columns authorId and permission. Then we could write a record for each permission. A user with the id of 4 and the permissions EDIT_JOKES, LIST_CATEGORIES and REMOVE_CATEGORIES would have the following records:
authorId | permission
4 | 1
4 | 3
4 | 5
You already know how to implement this. You’ll need to create the table, the relevant DatabaseTable instance, and write the savePermissions method to create a record in the user_permission table for each checked box. Finally, the hasPermission method in the Author entity class would look something like this:
public function hasPermission($permission) {
$permissions = $this->userPermissionsTable->
find('authorId', $this->id);
foreach ($permissions as $permission) {
if ($permission->permission == $permission) {
return true;
}
}
}
Before you implement this, I’m going to show you an alternative approach.
A Different Approach
Imagine if we set up the author table with a column for each permission:
CREATE TABLE `author` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) DEFAULT NULL,
`email` VARCHAR(255) DEFAULT NULL,
`password` VARCHAR(255) DEFAULT NULL,
`editJoke` TINYINT(1) NOT NULL DEFAULT 0,
`deleteJokes` TINYINT(1) NOT NULL DEFAULT 0,
`addCatgories` TINYINT(1) NOT NULL DEFAULT 0,
`removeCategories` TINYINT(1) NOT NULL DEFAULT 0,
`editUserAccess` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8;
Each column is TINYINT(1), which means it can store a single bit. It can either be a 1 or 0, and I’ve set it to default to 0.
We could then set any author’s permissions using an UPDATE statement. For the user I mentioned earlier with the id of 4, this could be sent to the database as:
UPDATE `author` SET `editJokes` = 1, `listCategories` = 1, `removeCategories` = 1 WHERE `id` = 4
It would be possible to name our checkboxes with the names of the columns and use them in the same way as any other field.
The advantage of this approach is that it’s simpler. To find out if a user has a permission, you can just use this:
// Can this author edit jokes?
if ($author->editJokes == 1)
Or this:
// Can this author remove categories?
if ($author->removeCategories == 1)
This is a nicer approach than a join table, as the same check using a join table would require querying the database for all the user’s permissions, looping through the records, and checking to see whether the permission we’re looking for has a corresponding record.
The downside to this approach is that every time we add a permission to the website, we’d need to add a column to the table.
In principle, though, this is a lot simpler. It avoids a database table, and checking whether a user has a permission requires significantly less code and fewer database queries.
Each permission is a simple 1 or 0 in the database.
If you examined a user’s record in MySQL Workbench, you might see something like 0 1 0 0 1 0 0 in the permissions columns.
A Crash Course in Binary
This sequence of ones and zeros looks a lot like binary. In fact, every number stored in a database is actually stored as a series of ones and zeros behind the scenes.
If you have an INT column and it stores the number 6 for a particular record, it will actually store the binary value 0110 on the hard disk. Everything stored on a computer is binary!
Each binary number is a bit like a database record. Each one or zero is in a specific column, and that column represents a particular value.
In fact, with normal decimal numbers, when you see the value 2395, you know that the 3 (since it’s in the third column from the right) represents 300.
The above number can actually be expressed like this:
2 x 1000 +
3 x 100 +
9 x 10
5 x 1
You’re so familiar with this process that it’s second nature, and you don’t need to think about it. Binary works in the same way. The only differences are that only the digits one and zero are available, and the column numbers are different.
In both binary and decimal, numbers are created right to left. As an extra digit is added, it’s added to the left-hand side. To add 300 to 27, you add a 3 to the left. The same thing is true of binary: digits further to the right have a higher value.
In the case of the binary number 0110, the third column from the right represents 4, and the second column from the right represents 2.
To translate the value of the binary number 0110 into decimal, you can do the same kind of calculation as long as you know the values of each column. In decimal, each column is multiplied by ten each time you move to the left. In binary, each column is multiplied by two.
To work out the total of 0110, we can do the same calculation:
0 x 8 +
1 x 4 +
1 x 2 +
0 x 1
If you calculate this, you’ll get 6. Each column is called a bit, and in this example we can describe the 8 bit as not being set (because it’s set to zero) and the 4 bit as being set (because it’s set to one).
See if you can convert the following binary numbers to decimalThe answers are 8 (1x8), 2 (1x2) and 10 (1x2 + 1x8).:
- 1000
- 0020
- 1010
Be Bit-Wise
You’re probably wondering why any of this matters and what it has to do with user permissions.
In short, binary has nothing to do with permissions. But neither do if statements or checkboxes. All three are tools we can use to solve the problem of permissions.
What’s useful is that you can use PHP (and almost every programming language) to inquire whether any given integer has a 1 or 0 set for any of the bits that make up the number.
Bitwise Permissions
Rather than using a different database column to store each one or zero, we could use a single binary number to store those ones and zeros in a single column.
By assigning a column to a permission, a binary number can represent which permissions any user has.
EDIT_USER_ACCESS | REMOVE_CATEGORIES | EDIT_CATEGORIES |
32 | 16 | 8 |
LIST_CATEGORIES | DELETE_JOKES | EDIT_JOKES |
4 | 2 | 1 |
The binary number 000001, which has a 1 in the EDIT_JOKES column, would represent a user with EDIT_JOKES permissions.
111111 would represent a user that had all the permissions, and 011111 would represent a user that had every permission apart from being able to edit the permissions of other users (EDIT_USER_ACCESS).
This process is identical to using multiple columns in a database, each with a one or zero. We’re just using a binary number to represent the same data. Rather than one column per bit, we can store multiple bits in a single INT column.
Let’s convert the binary numbers to decimal: 000001 becomes 1, 111111 becomes 63 and 011111 becomes 31. We can easily store these numbers as integers in a database!
If someone has the permissions value 63, we know they have all the permissions that are available.
Back to PHP
The difficult part is extracting the individual permissions. What if we want to know whether the user has the EDIT_CATEGORIES permission? On the chart above, I’ve assigned the 8 bit to mean EDIT_CATEGORIES. If a user has the permissions value 13, it’s not clear whether the 8 bit is set.
In a database with multiple columns, we can use SELECT * FROM author WHERE id = 4 AND editCategories = 1 to determine whether the editCategories column is set to 1 for a specific user—in this case, the user with the id of 4.
Most programming languages, including PHP and MySQL, support something called bitwise operations. These allow you to inquire whether a specific bit is set in any integer. Using a single permissions column, the query above can be expressed as follows:
SELECT * FROM author WHERE id = 4 AND 8 & permissions
The clever part here is the part AND 8 & permissions. This uses the bitwise and (&) operator to inquire whether the 8 bit is set in the number stored in the permissions column for that record.
PHP also provides the bitwise and operator. You’ve already seen that the number 6 is represented as 0110, which means that the bits 4 and 2 are set.
The bitwise & operator can be used to determine whether a bit is set in a specific number, and it’s used like this:
if (6 & 2) {
}
This says, “Is the bit 2 set in the number 6?”, and it will evaluate to true. If you wanted to check bit 1, however, it would return false, because the 1 bit is not set in the binary representation of 6 (0110):
if (6 & 1) {
}
Binary operations like this are actually fairly common in PHP! When you set the error_reporting variable in PHP to E_WARNING | E_NOTICE, what you’re doing is setting the bits that represent warnings and notices. PHP will then internally check which bits are set when it encounters an error.
Internally, PHP will do something like this:
if (E_NOTICE & ini_get('error_reporting')) {
display_notice($notice);
}
We can apply this to permissions. Imagine the author table had a column called permissions: it's possible to determine whether an author has the permission EDIT_CATEGORIES by using this code:
if ($author->permissions & 8) {
}
This code has the same problem I mentioned earlier: it’s not clear exactly what’s happening here to anyone looking at the code. Again, we could represent the bits as constants:
const EDIT_JOKES = 1;
const DELETE_JOKES = 2;
const LIST_CATEGORIES = 4;
const EDIT_CATEGORIES = 8;
const REMOVE_CATEGORIES = 16;
const EDIT_USER_ACCESS = 32;
And we could write the permissions check like so:
// Does the author have the EDIT_CATEGORIES permission?
if ($author->permissions & EDIT_CATEGORIES) {
}
// Does the author have the DELETE_JOKES permission?
if ($author->permissions & DELETE_JOKES) {
}
You don’t even need to understand the underlying binary to understand what’s happening here, and the individual numbers don’t even matter!
Storing Bitwise Permissions in the Database
Let’s implement this on the website. Amend the author table by adding a column called permissions, and set it to INT(64) so we can store a maximum of 64 different permissions.
Change the constant values in the Author entity class as I have above.
We don’t need to make any changes to the Edit Permissions form page, but we need to add the savePermissions method and have it store the binary permissions in the database.
Before we do that, let’s consider what will happen when the form is submitted. If you checked the boxes labeled EDIT_JOKES and REMOVE_CATEGORIES, the variable $_POST['permissions'] would be an array containing the numbers 1 and 16 ([1, 16]).
We want to convert those to the binary representation, where the 1 bit and 16 bits are set. It sounds difficult but, as you’ll see very shortly, it’s not!
The number we need to generate is 010001, where bits 16 and 1 are set. Use your new binary knowledge to calculate the decimal version of this value, and you’ll work out that this binary number represents 17.
All we need to do is add the numbers together!
Just to prove the theory, let’s imagine that the boxes for EDIT_JOKES, DELETE_JOKES, LIST_CATEGORIES and EDIT_USER_ACCESS are ticked. When the form is submitted, we’d get the array [1, 2, 4, 32].
The binary representation of those permissions is 100111. If you work out what that is in decimal, you’ll get 39. Add together the values from the array 1 + 2 + 4 + 32 and you’ll also get 39!
All we need to do to store the numbers in the database is add together each element in the $_POST['permissions'] array.
PHP even includes a function called array_sum that can do exactly that.
The savePermissions method in the Register controller can be written like this:
public function savePermissions() {
$author = [
'id' => $_GET['id'],
'permissions' => array_sum($_POST['permissions'] ?? [])
];
$this->authorsTable->save($author);
header('location: /author/list');
}
The line 'permissions' => array_sum($_POST['permissions'] ?? []) is used to add all the values from the $_POST['permissions'] array. However, if no boxes are ticked, $_POST['permissions'] won’t be set. The ?? operator is used to provide the array_sum operator with an empty array if there’s nothing in the $_POST['permissions'] variable.
That’s it! The savePermissions method is converting the checked boxes into a number, and that number’s binary representation is how we’re modeling the permissions of each user.
Join Table or Bitwise
When using the join table approach for categories, to cater for boxes that were unchecked, we specifically had to delete all the records and reinsert them each time the form was submitted. As the permissions column is a single number, if no boxes are checked, array_sum will return 0 and the 0 will be inserted into the database, avoiding the need to specifically handle unchecked boxes.
The final piece is the hasPermission method in the Author entity class. Add the $permission class variable. Then, to check if a user has a permission, we need a single line of code:
Relationships-BinaryPermissions
public function hasPermission($permission) {
return $this->permissions & $permission;
}
There are several advantages to this approach over a join table. The first is performance: we don’t need to query the database for the user’s permissions. Secondly, this is considerably less code for both saving the form and checking whether the permission is set.
The downsides to this approach are that it can be more difficult to understand if you’re not familiar with the bitwise operator. However, bitwise operators are fairly common in PHP. They’re used by the PDO library and for the various php.ini configuration settings such as error_reporting, so it’s a good idea to have a basic understanding of what they do. The second downside is that you’re limited to 64 bits, because that’s all a CPU can process. However, if you find yourself needing more, you can group the permissions into different columns, such as jokePermissions and adminPermissions.
Whether you choose to implement user roles as bitwise, like I have here, or whether you use a join table, is up to you. There are pros and cons to each approach. Personally, I prefer the shorter code and fewer database operations, which bitwise operators offer.
Cleaning Up
There’s a little tidying up left to do. Firstly, we need to add the permissions to the routes.
Make sure you have granted your user account the permission EDIT_USER_ACCESS before making these changes, or you won’t be able to change anyone’s permissions!
We already amended the routes for LIST_CATEGORIES, EDIT_CATEGORIES and REMOVE_CATEGORIES.
You can test this works by visiting http://192.168.10.10/category/list and giving and revoking your LIST_CATEGORIES permission.
Let’s do the same for EDIT_USER_ACCESS. Set the permissions key in the IjdbRoutes $routes array for the author list and permissions pages:
$routes = [
// …
'author/permissions' => [
'GET' => [
'controller' => $authorController,
'action' => 'permissions'
],
'POST' => [
'controller' => $authorController,
'action' => 'savePermissions'
],
'login' => true,
'permissions' => IjdbEntityAuthor::EDIT_USER_ACCESS
],
'author/list' => [
'GET' => [
'controller' => $authorController,
'action' => 'list'
],
'login' => true,
'permissions' => IjdbEntityAuthor::EDIT_USER_ACCESS
],
This will prevent anyone who doesn’t have the EDIT_USER_ACCESS permission from changing the permissions of other users.
Editing Others’ Jokes
The final two permissions are EDIT_JOKES and DELETE_JOKES, which determine whether the logged-in user can edit or delete a joke someone else has posted.
We can’t do this with the $routes array, because the check isn’t done there. The edit link and delete button are hidden in the template, and there are checks inside the joke controller.
Firstly, let’s make the edit link and delete button appear on the list page for all jokes if you have the EDIT_JOKES or DELETE_JOKES permissions.
The relevant section of jokes.html.php looks like this:
<?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
} ?>
Now that we have different permissions for edit and delete, we’ll need two separate if statements—one for the delete button and one for the edit link.
However, we can’t do this with the $userId alone. Instead of passing in the $userId variable to the template, change the list method in the Joke controller to pass in the entire $author object that represents the logged-in user:
return ['template' => 'jokes.html.php',
'title' => $title,
'variables' => [
'totalJokes' => $totalJokes,
'jokes' => $jokes,
'user' => $author, //previously 'userId' =>
$author->id
'categories' => $this->categoriesTable->findAll()
]
];
The check in the template can now be amended so that the button and link are only visible to the person who posted the joke, or someone with the relevant permission:
<?php if ($user): ?>
<?php if ($user->id == $joke->authorId ||
$user->hasPermission(IjdbEntityAuthor::EDIT_JOKES)): ?>
<a href="/joke/edit?id=<?=$joke->id?>">
Edit</a>
<?php endif; ?>
<?php if ($user->id == $joke->authorId ||
$user->hasPermission(IjdbEntityAuthor::DELETE_JOKES)):
?>
<form action="/joke/delete" method="post">
<input type="hidden" name="id"
value="<?=$joke->id?>">
<input type="submit" value="Delete">
</form>
<?php endif; ?>
<?php endif; ?>
This is a lot more complicated, as there are now three if statements. I’ve added if ($user) around the entire block, because the $user variable may be empty if no one is logged in.
The following two use a logical or to determine whether the person who’s viewing the page is the same person who posted the joke, or if they have the relevant permission.
Editing the jokes.html.php template makes the buttons appear, but if you have the EDIT_JOKES permission and attempt to edit a joke you didn’t post, you’ll see the error “You may only edit jokes that you posted”. That’s because we added a specific check in the editjoke.html.php template, and the delete method in the Joke controller.
Change delete to include the permissions check:
public function delete() {
$author = $this->authentication->getUser();
$joke = $this->jokesTable->findById($_POST['id']);
if ($joke->authorId != $author->id &&
!$author->hasPermission(IjdbEntityAuthor::DELETE_JOKES)) {
return;
}
$this->jokesTable->delete($_POST['id']);
header('location: /joke/list');
}
And, like the list method, pass the entire author object to the template in the edit method, and adjust the template to include the permissions check.
controllers/joke.php:
return ['template' => 'editjoke.html.php',
'title' => $title,
'variables' => [
'joke' => $joke ?? null,
'user' => $author,
'categories' => $categories
]
];
In editjoke.html.php, this code:
<?php if (empty($joke->id) || $userId == $joke->authorId): ?>
… becomes this:
<?php if (empty($joke->id) || $user->id == $joke->authorId || $user->hasPermission(IjdbEntityAuthor::EDIT_JOKES)): ?>
That’s it! All the permission checks are now in place.
Phew!
In this chapter, I showed you how to think in a more object-oriented way, and how to handle relationships between objects in an OOP way rather than a relational way.
You learned how to represent many-to-many relationships using both join tables and bitwise operations.
We added permissions to the existing site, but moving forward, you can think about user permissions as you go and create them while you’re writing the original code.
That’s it! We have a completely functional and working website that does almost anything we would want for a real-world project. I have a couple of small things left to show you, but we’re basically done! There’s little else I can teach you.
In the next chapter, I'll show you how to make a few tweaks to the DatabaseTable class to allow sorting and limiting, but you already have almost all the tools you need to build a fully functional website.