PHP CHAPTER 3 2019-01-02T12:20:33+00:00

PHP Chapter 3

Structured PHP Programming

Before we plow headlong into the next enhancements of our joke database, let’s spend a little time honing your “PHP-fu.” Specifically, we want to show you a few techniques to better structure your code. Structured coding techniques are useful in all but the simplest of PHP projects. You’ve learned how to split your PHP code into multiple files: a controller and a set of associated templates. This lets you keep the server-side logic of your site separate from the HTML code used to display the dynamic content generated by that logic. In order to do this, you learned how to use the PHP include command. The PHP language offers many such facilities to help you add structure to your code. The most powerful of these is undoubtedly its support for object-oriented programming (OOP). But there’s no need to learn all the complexities of OOP to build complex (and well-structured) applications with PHP. Thankfully, there are also opportunities for structuring your code through the more basic features of PHP.

We’ll explore some methods of keeping your PHP code manageable and maintainable. As projects grow in size, so does the code. When you want to make a change to something, you’ll need to find the point in the code you want to change. This can be tricky, and sometimes requires editing code in more than one place. Programmers are lazy, and we don’t want to have to make the same change in multiple locations. By placing code in one place, and using it with the include statement, it allows us to avoid repetition. If you ever find yourself copying and pasting code, you’re almost certainly better off moving that repeated code into its own file and using it in both locations with an include statement. The computer doesn’t care how you structure your code and will blindly follow any instructions you give it. Programmers structure the code, reduce repetition and break code into small chunks purely to make our own job easier. Code is a lot simpler to manage when broken up into small tasks. Trying to find an error in a 1000-line PHP script that does a dozen different things is a lot more difficult than finding the same error in a 30-line file that only performs a single task.

Include Files

Even very simple PHP-based websites often need the same piece of code in several places. You’ve already learned to use the PHP include command to load PHP templates from inside your controllers. It turns out you can use the same feature to save yourself from having to write the same code again and again, just like you did with layout.html.php: you wrote some HTML code and re-used it for every page. Include files (also known just as includes) also contain snippets of PHP code that you can load into your other PHP scripts instead of having to retype them.

Including HTML Content

The concept of include files came long before PHP. If you’re an old codger like me (which, in the Web world, means you’re over 30), you may have experimented with server-side includes (SSIs). A feature of just about every web server out there, SSIs let you put commonly used snippets of HTML (and JavaScript, and CSS) into include files that you can then use in multiple pages. In PHP, include files most commonly contain either pure PHP code or, in the case of PHP templates, a mixture of HTML and PHP code. But you don’t have to put PHP code in your include files. If you like, an include file can contain strictly static HTML. This is most useful for sharing common design elements across your site, such as a copyright notice at the bottom of every page:

<footer>

The contents of this web page are copyright &copy; 2017

Example LLC. All Rights Reserved.

</footer>

This file is a template partial—an include file to be used by PHP templates. To distinguish this type of file from others in your project, I recommend giving it a name ending with .html.php, to differentiate from non-template pages. This naming convention is common in projects that use templates in this way. You can then use this partial in any of your PHP templates:

<!DOCTYPE html>

<html lang=”en”>

<head>

   <meta charset=”utf-8″>

    <title>A Sample Page</title>

</head>

<body>

<main>

    This page uses a static include to display a standard

   copyright notice below.

</main>

     <?php include ‘footer.html.php’; ?> </body>

</html>

Finally, here’s the controller that loads this template:

<?php

include ‘samplepage.tpl.php’;

?>

The completed page

The screenshot above shows what the page looks like in the browser. Now all you need to do to update your copyright notice is to edit footer.html.php. No more time-consuming, error-prone find-and-replace operations! Of course, if you really want to make your life easier, you can just let PHP do the work for you:

<p id=”footer”>

The contents of this web page are copyright &copy;

1998&ndash;<?php echo date(‘Y’); ?> Example LLC.

All Rights Reserved.

</p>

Including PHP Code

On most websites with a database, almost every controller script must establish a database connection as its first order of business, and finally include the layout.html.php file. Our controllers all follow this pattern:

<?php

try {

$pdo = new PDO(‘mysql:host=localhost;dbname=ijdb;

charset=utf8′, ‘ijdbuser’, ‘mypassword’);

$pdo->setAttribute(PDO::ATTR_ERRMODE,

PDO::ERRMODE_EXCEPTION);

// do something unique for this page

// setting the $title and $output variables

} catch (PDOException $e) {

     $title = ‘An error has occurred’;

    $output = ‘Unable to connect to the database server: ‘

    . $e->getMessage() . ‘ in ‘

   . $e->getFile() . ‘:’ . $e->getLine();

}

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

At some 12 lines long, it’s only a slightly cumbersome chunk of code, but having to repeat it in every controller script can quickly become annoying. Many new PHP developers will often omit essential error checking to save typing (for example, by leaving out the try … catch statement in this code), which can result in a lot of lost time looking for the cause when an error does occur. Others will make heavy use of the clipboard to copy pieces of code like this from existing scripts for use in new ones. Some even use features of their text editor software to store useful pieces of code as snippets for frequent use. But what happens when the database password or some other detail of the code changes? Suddenly you’re on a treasure hunt to find every occurrence of the code in your site to make the necessary change—a task that can be especially frustrating if you’ve used several variations of the code that you need to track down and update.

Files can help in this situation. Instead of repeating the code fragment in every file that needs it, write it just once in a separate file—known as the include file. That file can then be included in any other PHP files that need to use it. Let’s apply this technique to create the database connection in our joke list example to see how it works in detail. Include files are just like normal PHP files, but typically they contain snippets of code that are only useful within the context of a larger script. As such, as with templates, we don’t want people to be able to navigate directly to these files by typing the filename into their browser, as they only contain small snippets that won’t produce any meaningful output on their own.

We’ll solve problem the same way we did with templates: by creating a directory outside the public directory, so that any files placed in this new directory can only be accessed by other PHP scripts. We’ll call this directory includes, and use it to store our code snippets. Inside the new includes directory, create a file called DatabaseConnection.php and place the database connection code inside it:

<?php

$pdo = new PDO(‘mysql:host=localhost;dbname=ijdb;

charset=utf8′, ‘ijdbuser’, ‘mypassword’);

$pdo->setAttribute(PDO::ATTR_ERRMODE,

PDO::ERRMODE_EXCEPTION)

Now you can put this DatabaseConnection.php file to use in your controllers. Amend each of your controllers—addjoke.php, deletejoke.php and jokes.php—to include the new file.The updated jokes.php looks like this:

Structure-Include

<?php

try {

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

$sql = ‘SELECT `joke`.`id`, `joketext`, `name`, `email`

FROM `joke` INNER JOIN `author`

      ON `authorid` = `author`.`id`’;

$jokes = $pdo->query($sql);

$title = ‘Joke list’;

ob_start();

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

$output = ob_get_clean();

} catch (PDOException $e) {

$title = ‘An error has occurred’;

$output = ‘Database error: ‘ . $e->getMessage() . ‘ in ‘

. $e->getFile() . ‘:’ . $e->getLine();

}

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

Make the same change to addjoke.php and delete.php so they use an include statement rather than repeating the database connection code. As you can see, wherever our controller needs a database connection, we can obtain it simply by including the DatabaseConnection.php file with an include statement. And because the code to do this is a simple one-liner, we can make our code more readable by using a separate include statement just before each SQL query in our controller. When PHP encounters an include statement, it puts the current script on hold and runs the specified PHP script. When it’s finished, it returns to the original script and picks up where it left off.

Include files are the simplest way to structure PHP code. Because of their simplicity, they’re also the most widely used method. Even very simple web applications can benefit greatly from using include files. You’ve already seen how you can create a variable such as $title in the controller and that it’s available in included files such as layout.html.php. Here, you can see the inverse is also true. The $pdo variable is created in DatabaseConnection.php, but can be used in the controller.

An include statement can be thought of as an automated copy-and-paste. When PHP encounters the line include __DIR__ . ‘/../includes/ DatabaseConnection.php’; it effectively reads the code from the file and copies/ pastes it into the current code at the position of the include statement. After you’ve amended all your controllers to have an include statement, if the database password gets updated, you only need to edit DatabaseConnection.php, rather than having to update the password in each of your controllers. Aren’t you glad we made this change now instead of later when we’ve got more controllers?

Types of Includes

The include statement we’ve used so far is actually only one of four statements that can be used to include another PHP file in a currently running script:

  • include
  • require
  • include_once
  • require_once

The only difference between them is what happens when the specified file is unable to be included (that is, if it doesn’t exist, or if the web server doesn’t have permission to read it). With include, a warning is displayed and the script continues to run. With require, an error is displayed and the script stops. In general, you should use require whenever your application simply wouldn’t work without the required code being successfully loaded. I do recommend using include whenever possible, however. Even if the DatabaseConnection.php file for your site is unable to load, for example, you might still want to let the script for your front page continue to load. None of the content from the database will display, but the user might be able to use the Contact Us link at the bottom of the page to let you know about the problem!

include_once and require_once work just like include and require, respectively—but if the specified file has already been included at least once for the current page request (using any of the four statements described here), the statement will be ignored. This is handy for include files performing a task that only needs to be done once, like connecting to the database. include_once and require_once are also useful for loading function libraries, as we’ll see in the following section.

Custom Functions and Function Libraries

By this point, you’re probably quite comfortable with the idea of functions. A function is PHP code that you can invoke at will, where you’d usually provide one or more arguments for it to use, and often receive a return value as a result. You can use PHP’s vast library of functions to do just about anything a PHP script could ever be asked to do, from retrieving the current date (date) to generating graphics on the fly (using imagecreatetruecolor).

But what you may be unaware of is that you can create functions of your own! Custom functions, once defined, work just like PHP’s built-in functions, and they can do anything a normal PHP script can do.Let’s start with a really simple example. Say you had a PHP script that needed to calculate the area of a rectangle given its width (3) and height (5). Thinking back to your basic geometry classes in school, you should recall that the area of a rectangle is its width multiplied by its height:

$area = 3 * 5;

But it would be nicer to have a function called area that simply calculated the area of a rectangle given its dimensions:

$area = area(3, 5);

As it happens, PHP has no built-in area function, but clever PHP programmers like you and me can just roll up our sleeves and write the function ourselves:

<?php

function area($width, $height)

{

return $width * $height;

}

This include file defines a single custom function: area. The <?php marker is probably the only line that looks familiar to you in this code. What we have here is a function declaration. Let me break it down for you one line at a time:

function area($width, $height)

The keyword function tells PHP that we wish to declare a new function for use in the current script. Then, we supply the function with a name (in this case, area). Function names operate under the same rules as variable names—they must start with a letter or an underscore (_), and may contain letters, numbers, and underscores—except, of course, that there’s no dollar sign prefix. Instead, function names are always followed by a set of parentheses (()), which may or may not be empty. The parentheses that follow a function name enclose the list of arguments that the function will accept. You should already be familiar with this from your experience with PHP’s built-in functions. For example, when you use rand to generate a random number, you can provide it with a minimum and maximum number within the parentheses.

When declaring a custom function, instead of giving a list of values for the arguments, you give a list of variable names. In this example, we list two variables: $width and $height. When the function is called, it will therefore expect to be given two arguments. The value of the first argument will be assigned to $width, while the value of the second will be assigned to $height. Those variables can then be used to perform the calculation within the function.

{

Speaking of calculations, the rest of the function declaration is the code that performs the calculation, or does whatever else the function is supposed to do. That code must be enclosed in a set of braces ({ … }), so here’s the opening brace.

return $width * $height;

You can think of the code within those braces as a miniature PHP script. This function is a simple one, because it contains just a single statement: a return statement. A return statement can be used in the code of a function to jump back into the main script immediately. When the PHP interpreter hits a return statement, it immediately stops running the code of this function and goes back to where the function was called. It’s sort of an ejection seat for functions! In addition to breaking out of the function, the return statement lets you specify a value for the function to return to the code that called it. In this case, the value we’re returning is $width * $height—the result of multiplying the first parameter by the second.

}

The closing brace marks the end of the function declaration. Writing a function on its own does nothing. No code inside the function is run until the function is called. Like the rand function we used before, it just sits there waiting to be called. You can think of writing a function like installing an app on your computer or phone. You need it there to use it, but once installed, it’s dormant and available for use, but won’t actually do anything until you run it. In order to run the function, we must first include the file containing the function declaration:

include_once __DIR__ .

‘/../includes/area-function.inc.php’;

$area = area(3, 5);

include ‘output.html.php’;

Technically, you could write the function declaration within the controller script itself, but by putting it in an include file, you can reuse the function in other scripts much more easily. It’s tidier, too. To use the function in the include file, a PHP script need only include it with include_once (or require_once if the function is critical to the script). Avoid using include or require to load include files that contain functions. As explained in the “Types of Includes” section earlier in this chapter, that would risk defining the functions in the library more than once and covering the user’s screen with PHP warnings.

It’s standard practice (but not required) to include your function libraries at the top of the script, so that you can quickly see which include files containing functions are used by any particular script. What we have here are the beginnings of a function library—an include file that contains declarations for a group of related functions. If you wanted to, you could rename the include file to geometry.inc.php, add a whole bunch of functions to it, and perform various geometrical calculations.

Variable Scope

One big difference between custom functions and include files is the concept of variable scope. Any variable that exists in the main script will also be available and can be changed in the include file. While this is useful sometimes, more often it’s a pain in the neck. Unintentionally overwriting one of the main script’s variables in an include file is a common cause of error—and one that can take a long time to track down and fix! To avoid such problems, you need to remember the variable names in the script that you’re working on, as well as any that exist in the include files your script uses.

Functions protect you from such problems. Variables created inside a function (including any argument variables) exist only within that function, and disappear when the function has run its course. In addition, variables created outside the function are completely inaccessible inside it. The only variables a function has access to are the ones provided to it as arguments. In programmer-speak, the scope of these variables is the function; they’re said to have function scope. In contrast, variables created in the main script outside of any function are unavailable inside functions. The scope of these variables is the main script, and they’re said to have global scope.

Okay, but beyond the fancy names, what does this really mean for us? It means that you can have a variable called, say, $width in your main script, and another variable called $width in your function, and PHP will treat these as two entirely separate variables! Perhaps more usefully, you can have two different functions each using the same variable names, and they’ll have no effect on each other, because their variables are kept separate by their scope. On some occasions, you may actually want to use a global-scope variable (global variable for short) inside one of your functions. For example, the DatabaseConnection.php file creates a database connection for use by your script and stores it in the global variable $pdo. You might then want to use this variable in a function that needed to access the database. Let’s create a function that queries the database and returns to us the number of jokes that are currently held in the joke table. Disregarding variable scope, here’s how you might expect such a function to work:

include_once __DIR__ .

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

function totalJokes() {

     $query = $pdo->prepare(‘SELECT COUNT(*) FROM `joke`’);

     $query->execute();

     $row = $query->fetch();

      return $row[0];

}

echo totalJokes();

Careful Where You Place Your Files

Note that the first line of this controller script uses a shared copy of the DatabaseConnection.php file in the includes directory. Make sure you’ve placed a copy of this file in the includes directory; otherwise, PHP will complain that it’s unable to find the DatabaseConnection.php file. The file DatabaseConnection.php creates the variable $pdo, which we’re referencing inside the function.

The problem here is that the global variable $pdo is unavailable within the scope of the function. If you attempt to call this function as it is, you’ll receive the errors shown in the following image:

The totaljokes function cannot access $pdo

The reason for this error is scope. The $pdo variable was created outside the function, so is not available for use inside it. Although there is a way to make the same $pdo variable available in the function3, it’s a very bad idea. If, for example, the function changed the $pdo by accident to a string—$pdo = ‘select * from joke’;—the $pdo variable would now be a string everywhere else in the PHP script. Global variables are a very bad idea and lead to problems that are very difficult to track down and fix. You should avoid global variables at any cost. To avoid this, you can use $pdo as an argument and pass in the required variables to your function:

function totalJokes($pdo) {

$query = $pdo->prepare(‘SELECT COUNT(*) FROM `joke`’);

$query->execute();

$row = $query->fetch();

return $row[0];

}

And then, when the function is called, you pass in the $pdo object created in DatabaseConnection.php:

include_once __DIR__ .

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

echo totalJokes($pdo);

This makes the $pdo variable available in the totalJokes function by passing it in as an argument. It’s worth taking a moment to understand exactly what’s happening here. The $pdo variable is created in global scope by DatabaseConnection.php and then passed in to the function totalJokes. The object stored in the global $pdo variable is then copied to the local variable called $pdo inside the function. The $pdo variable needs to be passed in as an argument, because functions only have access to data they are given; they cannot access variables from the global scope.

Importantly, if there’s a mistake in the function and the $pdo variable is overwritten with a string, it will only be a string inside the function, not throughout the rest of the script. What this means is that there are actually two different variables called $pdo. We could rename the $pdo variable inside the function to $database and the script would still work:

function totalJokes($database) {

   $query = $database->prepare(‘SELECT COUNT(*)

   FROM `joke`’);

   $query->execute();

   $row = $query->fetch();

return $row[0];

}

Here, the $database variable stores the same PDO connection that is stored in the $pdo variable in global scope. Even though the function would still be called using totalJokes($pdo), the content of the $pdo variable outside the function call is copied to the $database variable inside the function. Despite having different names, they reference the same database connection. If you want to get technical, this process is called dependency injection, but all you need to know is that in practical terms, it’s a method for making a single variable available in multiple locations. It’s a good idea to move functions into their own file. Place the totalJokes function inside a file called DatabaseFunctions.php in the includes directory, and you can then use the function like this:

Structure-TotalJokes

// Include the file that creates the $pdo variable and connects to the database

include_once __DIR__ .

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

// Include the file that provides the `totalJokes` function

include_once __DIR__ .

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

// Call the function

echo totalJokes($pdo);

Save this as showtotaljokes.php in the public directory and navigate to it in your browser. You should see a page that’s mostly empty but displays the total number of jokes in your database. You may have realized that if the program is broken up into functions like this, you’d need to pass the $pdo variable into each function you wanted to use. As you’ve probably suspected, this isn’t the most efficient way of achieving this goal, as we’ll see later on when we get to objects and classes. Let’s use our new function inside the website. At the top of the list of jokes we can print “[number] jokes have been submitted to the Internet Joke Database”. Open up jokes.php and change it to the following:

<?php

try {

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

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

$sql = ‘SELECT `joke`.`id`, `joketext`, `name`, `email`

FROM `joke` INNER JOIN `author`

     ON `authorid` = `author`.`id`’;

$jokes = $pdo->query($sql);

$title = ‘Joke list’;

$totalJokes = totalJokes($pdo);

ob_start();

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

$output = ob_get_clean();

} catch (PDOException $e) {

    $title = ‘An error has occurred’;

     $output = ‘Database error: ‘ . $e->getMessage()

    . ‘ in ‘ .$e->getFile() . ‘:’ . $e->getLine();

}

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

This creates a $totalJokes variable that can be used in jokes.html.php:

 Structure-TotalJokeList

<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:<?php

    echo htmlspecialchars(

    $joke[’email’],

    ENT_QUOTES,

    ‘UTF-8’

); ?>”><?php

     echo htmlspecialchars(

        $joke[‘name’],

        ENT_QUOTES,

       ‘UTF-8’

); ?></a>)

<form action=”deletejoke.php” method=”post”>

       <input type=”hidden” name=”id”

              value=”<?=$joke[‘id’]?>”>

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

</form>

</p>

</blockquote>

<?php endforeach; ?>

You may be wondering what happens if the query inside the totalJokes function contains an error. We could put a try … catch statement inside the function, but because the function has been called inside the existing try … catch statement, we don’t need to. Exceptions bubble up. In practical terms, this means that, even if an exception is thrown inside a function, it will still be caught by a surrounding try … catch statement. It’s better to avoid handling errors inside functions. Imagine we have a try … catch statement inside the function like so:

function totalJokes($database) {

try {

     $query = $database->prepare(‘SELECT COUNT(*)

      FROM `joke`’);

     $query->execute();

     $row = $query->fetch();

     return $row[0];

}

catch {

    $title = ‘An error has occurred’;

        $output = ‘Database error: ‘ . $e->getMessage()

       . ‘ in ‘ . $e->getFile() . ‘:’

       . $e->getLine();

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

die();

      }

}

Not only do we have repeated code, but now regardless of where the totalJokes function is called from, the error will be handled in the same way. By keeping it out of the function, different error handling can be used in different places that the totalJokes function is used. For example, it’s possible that we would want to use the website to generate a list of jokes as a PDF or an Excel file rather than as an HTML document. If this were the case, we certainly wouldn’t want an error message displayed as HTML. By just letting the error happen, we can handle the error in different ways under different circumstances.

Breaking Up Your Code Into Reusable Functions

Now that you’re familiar with declaring your own functions, you can start writing functions to perform each task. For example, instead of writing out a SELECT query each time you want to read a specific joke from the database, you could write a reusable function to do that for you:

function getJoke($pdo, $id) {

$query = $pdo->prepare(‘SELECT FROM `joke`

WHERE `id` = :id’);

$query->bindValue(‘:id’, $id);

$query->execute();

return $query->fetch();

}

This works in the same way as the totalJokes function from earlier. The only difference is that there’s a second argument, $id, which is going to store the ID of the joke to be looked up. This allows us to look up a joke very quickly by its ID:

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

$joke1 = getJoke($pdo, 1);

echo $joke1[‘joketext’];

$joke2 = getJoke($pdo, 2);

echo $joke2[‘joketext’];

You’ve probably already noticed a lot of similarity between the getJoke function and the totalJokes function. Both functions perform a prepare and execute, then fetch a given record. Whenever you spot repeated code, it’s usually a good idea to take the repeated code and place it in its own function. This is commonly referred to as the DRY (don’t repeat yourself) principle. If you find yourself copying and pasting blocks of code, it’s a good sign that the code belongs inside its own function. Here’s one of our functions:

function totalJokes($pdo) {

    $query = $pdo->prepare(‘SELECT COUNT(*) FROM `joke`’);

    $query->execute();

     $row = $query->fetch();

return $row[0];

}

Here’s the other:

function getJoke($pdo, $id) {

$query = $pdo->prepare(‘SELECT FROM `joke`

WHERE `id` = :id’);

$query->bindValue(‘:id’, $id);

$query->execute();

return $query->fetch();

}

You can see that these are doing very similar jobs. These lines are identical or almost the same in both functions:

$query = $pdo->prepare(‘…’);

$query->execute();

return $query->fetch();

Here’s the code for executing a specific query. This code could easily be moved to its own function:

function query($pdo, $sql) {

   $query = $pdo->prepare($sql);

   $query->execute();

   return $query;

}

The interesting part here is the line $query = $pdo->prepare($sql);. Instead of writing the query that’s going to be executed as a string, we’ll use a variable—the $sql argument. This allows the totalJokes function to be simplified:

function totalJokes($pdo) {

  $query = query($pdo, ‘SELECT COUNT(*) FROM `joke`’);

  $row = $query->fetch();

  return $row[0];

}

Something quite clever is happening here. The totalJokes function is calling the query function. In fact, you can call any function from any other function, allowing you to break up tasks into smaller and smaller reusable chunks. It’s now possible to do the same with the getJoke function, removing the repetition by using the new query function:

function getJoke($pdo, $id) {

$query = query($pdo, ‘SELECT * FROM `joke` WHERE `id` = :id’);

return $query->fetch();

}

But wait! This isn’t quite going to work as we intend. The original getJoke function contained this line:

$query->bindValue(‘:id’, $id);

This binds the id SQL parameter to the variable $id, so the :id parameter is correctly set in the query SELECT FROM joke WHERE id = :id. The query function doesn’t do this yet, so it must be altered somehow to bind parameters. It would be possible to write the query function like this:

function query($pdo, $sql, $id) {

  $query = $pdo->prepare($sql);

  $query->bindValue(‘:id’, $id);

  $query->execute();

return $query;

}

Then we could call it using this:

function getJoke($pdo, $id) {

$query = query($pdo, ‘SELECT * FROM `joke`

WHERE `id` = :id’, $id);

return $query->fetch();

}

However, this is very inflexible; it will only work with queries that have an :id parameter. Instead, the query function should be able to take any number of parameters (including zero for the totalJokes function!). Luckily, there’s a simple way of doing this. We can create an array of parameters that need to be bound, and pass them as an argument to the query function.

function getJoke($pdo, $id) {

// Create the array of $parameters for use in the query function

$parameters = [‘:id’ => $id];

// call the query function and provide the $parameters array $query = query($pdo, ‘SELECT * FROM `joke`

WHERE `id` = :id’, $parameters);

return $query->fetch();

}

I’ve created a variable called $parameters to store the query parameters, and passed the entire array to the query function. Being able to pass arrays into functions is a nice trick when you don’t always know how many arguments there will be. Obviously the query function will need to be amended to use the new parameters:

function query($pdo, $sql, $parameters) {

   $query = $pdo->prepare($sql);

foreach ($parameters as $name => $value ) {

             $query->bindValue($name, $value);

}

$query->execute();

return $query;

}

The clever part here is the foreach loop:

foreach ($parameters as $name => $value ) {

   $query->bindValue($name, $value);

}

This loops over each of the supplied parameters, and binds them to the query. One problem this is going to cause is that the totalJokes function doesn’t send a list of parameters because there are none. The query doesn’t need a WHERE statement or any parameters to be replaced:

function totalJokes($pdo) {

$query = query($pdo, ‘SELECT COUNT(*) FROM `joke`’);

$row = $query->fetch();

return $row[0];

}

Because the query function is expecting three arguments and only two are being sent, this will cause an error. One way of avoiding this is to re-write the totalJokes function to send an empty array of parameters to the query function:

function totalJokes($pdo) {

// Create an empty array for sending to the query function $parameters = [];

// Call the query function and pass it the

// empty $parameters array

$query = query($pdo, ‘SELECT COUNT(*)

FROM `joke`’, $parameters);

$row = $query->fetch();

return $row[0];

}

However, PHP has a nice, built-in way of handling this. Whenever you declare an argument, you can give it a “default value”—that is, a value that’s used if none are supplied, like so:

function myFunction($argument1 = 1, $argument2 = 2) {

If the function is called without any arguments—for example, myFunction()—then the $argument1 variable is set to 1 and the $argument2 variable is set to 2This feature can be used with the query function to set the $parameters variable to an empty array if no value is supplied:

function query($pdo, $sql, $parameters = []) {

      $query = $pdo->prepare($sql);

foreach ($parameters as $name => $value ) {

    $query->bindValue($name, $value);

}

$query->execute();

return $query;

}

With a default value set on the $parameters argument, the function can be called and provided with values for the $pdo and $sql arguments. If no third argument is provided, $parameters will use the default value, which we defined to be an empty array. Now that $parameters has a default value, the totalJokes function can be left as it was, and doesn’t need to provide the third argument to the query function:

function totalJokes($pdo) {

  $query = query($pdo, ‘SELECT COUNT(*) FROM `joke`’);

  $row = $query->fetch();

return $row[0];

}

When the query function is called, the third parameter is missing and PHP will automatically assign the value [] (an empty array) to it. When the function is run, the foreach loop will still be executed. However, because the array is empty, it will iterate zero times and bindValue will never be called:

foreach ($parameters as $name => $value ) {

     $query->bindValue($name, $value);

}

Now that you know how this works, we’ll show you a shortcut. This foreach statement that binds all the parameters can actually be removed. The execute method can optionally take an argument of parameters to be bound in exactly the same way, letting us reduce the code in the query function by passing the $parameters array directly to the execute method, rather than manually binding each, one by one:

function query($pdo, $sql, $parameters = []) {

    $query = $pdo->prepare($sql);

   $query->execute($parameters);

return $query;

}

Using Functions to Replace Queries

With the query function complete, you have the ability to query the database very quickly. The totalJokes function allows for easily querying the database for the number of jokes in the table. Let’s take the same approach and apply it to adding jokes to the database with an INSERT query:

function insertJoke($pdo, $joketext, $authorId) {

$query = ‘INSERT INTO `joke` (`joketext`, `jokedate`,

    `authorId`) VALUES (:joketext, CURDATE(), :authorId)’;

$parameters = [‘:joketext’ => $joketext, ‘:authorId’ => $authorId];

query($pdo, $query, $parameters);

}

The insertJoke function lets us very quickly insert a record into the joke table with a single line of code by providing it the database connection ($pdo), the text of the joke ($joketext) and the ID of the author ($authorId):

insertJoke($pdo, ‘Why did the programmer quit his job? He didn\’t get arrays’, 1);

Each of the columns in the database is an argument for the function, which can now be called repeatedly to quickly issue the relevant INSERT query with far less code than we’d have needed previously:

insertJoke($pdo, ‘Why was the empty array stuck outside? It didn\’t have any keys’, 1);

insertJoke($pdo, ‘An SQL query goes into a bar, walks up to two tables and asks “Can I join you?”‘, 2);

This approach is considerably quicker and easier than having to write all the code for running the query each time you insert a joke: preparing the query, binding the parameters and then finally executing the query. Now, finally, you could use the following controller code for adding a joke:

if (isset($_POST[‘joketext’])) {

try {

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

$sql = ‘INSERT INTO `joke` SET

   `joketext` = :joketext,

   `jokedate` = CURDATE()’;

$stmt = $pdo->prepare($sql);

$stmt->bindValue(‘:joketext’, $_POST[‘joketext’]);

$stmt->execute();

header(‘location: jokes.php’);

}

catch (PDOException $e) {

$title = ‘An error has occurred’;

$output = ‘Database error: ‘ . $e->getMessage() . ‘

in ‘ . $e->getFile() . ‘:’ . $e->getLine();

      }

}

But instead of this, you can put the insertJoke and query functions in the DatabaseFunctions.php file and then write this much simpler version:

Structure-AddJoke

if (isset($_POST[‘joketext’])) {

try {

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

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

insertJoke($pdo, $_POST[‘joketext’], 1);

header(‘location: jokes.php’);

}

catch (PDOException $e) {

$title = ‘An error has occurred’;

$output = ‘Database error: ‘ . $e->getMessage() . ‘

in ‘ . $e->getFile() . ‘:’ . $e->getLine(); }

}

You can see that we’ve put the query, totalJokes and insertJoke functions into a file called DatabaseFunctions.php and included it as required. For now, all jokes will have 1 set as the authorId. Later on,we’ll show you how to handle logins and associate the joke with the currently logged in user.

Updating Jokes

Along with inserting a joke, it’s useful to be able to update a record. Perhaps there was a spelling error in one of the jokes and it needs to be changed. An update function needs more information than the insert function. It needs to know the ID of the record being updated along with the new values for each column:

function updateJoke($pdo, $jokeId, $joketext, $authorId) {

$parameters = [‘:joketext’ => $joketext,

‘:authorId’ => $authorId, ‘:id’ => $jokeId];

query($pdo, ‘UPDATE `joke` SET `authorId` = :authorId,

`joketext` = :joketext WHERE `id` = :id’, $parameters);

}

The updateJoke function can be called anywhere a joke needs to be updated:

updateJoke($pdo, 1, ‘!false – It\’s funny because it\’s true’, 1);

The line above will update the joke with the id 1 with the supplied authorId and jokeText. The equivalent of running all the code:

$query = $pdo->prepare(‘UPDATE `joke`

SET `authorId` = :authorId, `joketext` = :joketext

WHERE id = :id’);

$query->bindValue(‘:id’, 1);

$query->bindValue(‘:authorId’, 1);

$query->bindValue(‘:joketext’, ‘!false – It\’s funny because it\’s true’);

 $query->execute();

This is a significant advantage: anywhere in the code that a joke needs to be updated, the single function call can do it, saving a lot of time.

Editing Jokes on the Website

Let’s make use of our new functions getJoke and updateJoke by adding a page that allows for editing the existing jokes. In essence, it’s the same as addjoke.php. It will display a form, and when the form is submitted it will send the data to the database.

However, there are two main differences:

  1. When the edit page loads, it needs to retrieve the current joke text from the database in order to pre-fill the <textarea> with the current joke. After all, if it’s a simple typo, you don’t want to make the user re-type the whole joke!
  2. When the form is submitted, it needs to run an UPDATE query rather than an

INSERT query.

In the public directory, create editjoke.php that looks like this:

<?php

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

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

try {

    if (isset($_POST[‘joketext’])) {

         updateJoke($pdo, $_POST[‘jokeid’], $_POST[‘joketext’], 1);

     header(‘location: jokes.php’);

} else {

    $joke = getJoke($pdo, $_GET[‘id’]);

    $title = ‘Edit joke’;

    ob_start();

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

     $output = ob_get_clean();

       }

} catch (PDOException $e) {

    $title = ‘An error has occurred’;

   $output = ‘Database error: ‘ . $e->getMessage() . ‘

    in ‘ . $e->getFile() . ‘:’ . $e->getLine();

}

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

There are a few main differences between this new editjoke.php page and the addjoke.php page. Firstly, you may have noticed that the two include files have been included at the very top of the page. This is so the database functions are available whether or not the form has been submitted. If the form hasn’t been submitted, we need to query the database for the current joke text. If it has been submitted, we’ll need to run the relevant update query. Secondly, the try statement surrounds the if rather than having the if surround the try. The reason for this is simple: there may be an error in either the if or else block. Speaking of the else block, that’s the next change. Instead of just loading the form, the else block has this line of code:

$joke = getJoke($pdo, $_GET[‘id’]);

This part of the code selects a joke from the database by its ID using the getJoke function from earlier. The ID of the joke being edited must be supplied using a GET variable—so that visiting editjoke.php?id=4, for example, will execute the query SELECT * FROMjokeWHEREid= 4 and store the resulting joke in the $joke array. The $joke variable can now be used in the corresponding template file editjoke.html.php:

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

<input type=”hidden” name=”jokeid”

value=”<?=$joke[‘id’];?>”>

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

</label>

<textarea id=”joketext” name=”joketext” rows=”3″

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

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

</form>

This template is slightly different from the template for adding jokes. The most obvious change is that the current joke text, from the $joke variable, is loaded into the <textarea> when the page loads. However, there’s another change: there’s also a hidden input that sends the ID of the joke being edited back to the page when the submit button is pressed. In editjoke.php, there are now two $_POST variables available when the form has been submitted—$_POST[‘jokeid’], representing the ID of the joke being edited, and $_POST[‘joketext’], containing the new text for the joke. These are then passed into our updateJoke function:

updateJoke($pdo, $_POST[‘jokeid’], $_POST[‘joketext’], 1);

Before you can test this example for yourself, you’ll need to add a link next to each joke in the joke list that gives us a link to the editjoke.php controller with the ID of the joke being edited. If you go directly to editjoke.php, you’ll see an error, as editjoke.php needs the ID of the joke being edited in order for the current value in the joketext column to be displayed in the <textarea>Open up jokes.html.php from the templates directory and add a link to editjoke.php for each joke:

<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:<?php

  echo htmlspecialchars(

  $joke[’email’],

  ENT_QUOTES,

     ‘UTF-8’

); ?>”><?php

  echo htmlspecialchars(

       $joke[‘name’],

       ENT_QUOTES,

  ‘UTF-8’

); ?></a>)

<a href=”editjoke.php?id=<?=$joke[‘id’]?>”>

Edit</a>

<form action=”deletejoke.php” method=”post”>

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

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

</form>

</p>

</blockquote>

<?php endforeach; ?>

Each joke is linked to editjoke.php?id=, followed by the ID of the joke you wish to edit. Try this example (Structure-EditJoke) and load up the jokes list. Each joke will have an Edit link after the author’s name, and by clicking the link, the joke text will be loaded into the form. When you press Save, it will trigger the update query by calling the updateJoke function.

Delete Function

The same thing can now be done with delete to allow deletion of jokes in the same quick and easy way with a single line of code:

function deleteJoke($pdo, $id) {

$parameters = [‘:id’ => $id];

query($pdo, ‘DELETE FROM `joke`

WHERE `id` = :id’, $parameters);

}

Then call the function to delete a joke with a specific ID from the database:

// Delete a joke with the ID of 2

deleteJoke($pdo, 2);

Add the function to DatabaseFunctions.php and amend deletejoke.php to use the new function:

<?php

try {

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

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

deleteJoke($pdo, $_POST[‘id’]);

header(‘location: jokes.php’);

} catch (PDOException $e) {

   $title = ‘An error has occurred’;

   $output = ‘Unable to connect to the database server: ‘

       . $e->getMessage() . ‘ in ‘ .

       $e->getFile() . ‘:’ . $e->getLine();

}

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

Select Function

We can apply the same logic to fetching all the jokes from the joke table by adding an allJokes function to DatabaseFunctions.php and amending jokes.php accordingly:

function allJokes($pdo) {

$jokes = query($pdo, ‘SELECT `joke`.`id`, `joketext`,

`name`, `email`

        FROM `joke` INNER JOIN `author`

       ON `authorid` = `author`.`id`’);

return $jokes->fetchAll();

}

In this instance, the query is more complex than the one-liners used for deleteJoke and updateJoke. It’s the same query we already had in jokes.php for selecting the jokes and the information about the author. Here we’ve used PDO’s fetchAll function. This returns an array of all records that were retrieved by the query. You can use the new allJokes function like this:

$jokes = allJokes($pdo);

echo ‘<ul>’;

foreach ($jokes as $joke) {

echo ‘<li>’ . $joke . ‘</li>’;

}

echo ‘</ul>’;

This will print out all the jokes from the database in a list. Update the jokes.php controller to use the new function:

 Structure-AddJoke

try {

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

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

$jokes = allJokes($pdo);

$title = ‘Joke list’;

$totalJokes = totalJokes($pdo);

ob_start();

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

$output = ob_get_clean();

}

catch (PDOException $e) {

   $title = ‘An error has occurred’;

   $output = ‘Database error: ‘ . $e->getMessage() . ‘

    in ‘ . $e->getFile() . ‘:’ . $e->getLine();

}

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

We’ve now got a set of reusable functions that can be used anywhere we need to interact with the database table. Whether it’s finding all the jokes, a single joke or issuing an INSERT, UPDATE or DELETE query, it’s possible to run the query without actually typing out all the code each time. Anywhere on the website we need to interact with the database table jokes, it’s quick and easy using our new set of functions.

The Best Way

We’ve helped you to rise above the basic questions of what PHP can do for you, and begin to look for the best way to code a solution. Sure, you can approach many simple scripts as lists of actions you want PHP to do for you, but when you tackle site-wide issues such as database connections, shared navigation elements, visitor statistics, and access control systems, it really pays to be able to structure your code carefully. We’ve now explored a couple of simple but effective devices for writing structured PHP code. Include files let you reuse a single piece of code across multiple pages of your site, greatly reducing the burden when you need to make changes. Writing your own functions to put in these include files lets you build powerful libraries of functions that can perform tasks as needed and return values to the scripts that call them. 

Improving the Insert and Update Functions

WE showed you how to break the code up into easily reusable functions. This has several advantages:

  • the code where the function is called is easier to read
  • you can re-use the same function from anywhere

Now, we’ll take this a step further and show you how to make a function that could be used for any database table, and then show you how object-oriented programming can simplify this task even further.

Improving the Update Function

updateJoke($pdo, 1, ‘Why did the programmer quit his job? He didn\’t get arrays’, 1);

To run this function, all of the arguments for the function must be provided:

  •   joke ID
  •   joke text
  •   author ID

What if you just wanted to update the joke text, rather than the joke text and author ID? Or just update the joke’s author? With the updateJoke function above, all the information needs to be provided each time. A better way of doing this is to take the field values as an array, with the keys representing the field names and the array contents representing the data to be stored in the database. For example:

updateJoke($pdo, [

   ‘id’ => 1,

   ‘joketext’ => ‘Why did the programmer quit his job?

   He didn\’t get arrays’]

);

Another example:

updateJoke($pdo, [

    ‘id’ => 1,

    ‘authorId’ => 4

]);

This is a lot nicer, as only the information being updated (and the primary key) need to be sent to the function. It also has the advantage of being easier to read: you can read this code and see exactly what each field is being set to. With the earlier version, you have to know which parameter represents which field. If you applied that to a table with 10 or 20 fields, you’d have to get the order of the arguments exactly right and remember which field was at each position. To make the function take an array, it needs to be updated. Currently it looks like this, and takes an argument for each field:

function updateJoke($pdo, $jokeId, $joketext, $authorId) {

$parameters = [‘:joketext’ => $joketext, ‘:authorId’ => $authorId, ‘:id’ => $jokeId];

query($pdo, ‘UPDATE `joke`

SET `authorId` = :authorId, `joketext` = :joketext

WHERE `id` = :id’, $parameters);

}

Changing the function so that it can take an array as a second argument and run the query is less than straightforward, as the query above is expecting parameters for :authorid, :joketext and :primaryKey. What will happen if they’re not all provided? If you try it, you’ll get an error. To work around this error, we need to dynamically generate the query so it only contains the relevant fields (only the ones we actually want to update). The foreach loop can loop over an array. Take the following code:

$array = [

‘id’ => 1,

‘joketext’ => ‘Why was the empty array stuck outside?

It didn\’t have any keys’

];

foreach ($array as $key => $value) {

echo $key . ‘ = ‘ . $value . ‘,’;

}

This will print the following:

id = 1, joketext = Why was the empty array stuck outside? It didn’t have any keys

It’s possible to use a foreach loop to produce the UPDATE query:

$array = [

    ‘id’ => 1,

    ‘joketext’ => ‘!false – it\’s funny because it\’s true’

];

$query = ‘ UPDATE `joke` SET ‘;

foreach ($array as $key => $value) {

     $query .= ‘`’ . $key . ‘` = :’ . $key . ‘,’

}

$query .= ‘ WHERE `id` = :primaryKey’;

echo $query;

The .= Operator

Note the use of the .= operator above. It adds to the end of the existing string, rather than overwriting it!

The code above will print this query:

UPDATE `joke` SET `id` = :id, `joketext` = :joketext,

                       WHERE `id` = :primaryKey

The query is generated dynamically based on the contents of $array, and only the field names from the array will appear in the query. You’ll notice that the id field is included in the statement. This isn’t needed, but having it here will simplify things later on. The following code can be used to generate the query with just the authorId:

$array = [

  ‘id’ => 1,

  ‘authorid’ => 4

];

$query = ‘UPDATE `joke` SET ‘;

foreach ($array as $key => $value) {

    $query .= ‘`’ . $key . ‘` = :’ . $key . ‘,’

}

$query .= ‘ WHERE `id` = :primaryKey’;

echo $query;

The code above will print the following query:

UPDATE `joke` SET `id` = :id, `authorId` = :authorId,

              WHERE `id` = :id

This lets us generate almost the exact query that’s needed to update any field, or set of fields, in the joke table. We say almost, because if you sent this query to the database you’d get an error. Unfortunately, there’s a subtle problem with this generated query. You may have noticed that each time the loop runs, it adds a comma to each part of the SET clause, generating this:

SET `id` = :id, `authorId` = :authorId,

In the following section, We’ll show what to do about this.

Stripping the Trailing Comma

The problem with this part of the query is that there’s an extra comma at the end of the clause between authorId and WHERE:

UPDATE `joke` SET `id` = :id, `authorId` = :authorId,

            WHERE `id` = :id

To be a valid query, it should actually be this:

UPDATE `joke` SET `id` = :id, `authorId` = :authorId

           WHERE `id` = :id

This is a subtle, single character difference. However, it’s the difference between a valid query and an invalid one! It would be possible to tweak the foreach loop to omit the comma on the last iteration, but it’s simpler just to remove the comma after the string has been generated. The rtrim function can be used to remove (or trim) specific characters from the end of a string. In our case, we want to remove the comma from the $query string before appending the WHERE clause:

$array = [

  ‘id’ => 1,

  ‘authorid’ => 4

];

$query = ‘UPDATE `joke` SET ‘;

foreach ($array as $key => $value) {

    $query .= ‘`’ . $key . ‘` = :’ . $key . ‘,’

}

$query = rtrim($query, ‘,’);

$query .= ‘ WHERE `id` = :primaryKey’;

echo $query;

The line $query = rtrim($query, ‘,’); will remove the trailing comma from the end of the $query string, giving us a valid SQL UPDATE query. This will display the entire query without the extra comma:

UPDATE `joke` SET `id` = :id, `joketext` = :joketext

WHERE `id` = :primaryKey

By placing this code into the function, the improved version of updateJoke can now be used:

function updateJoke($pdo, $fields) {

  $query = ‘ UPDATE `joke` SET ‘;

foreach ($array as $key => $value) {

    $query .= ‘`’ . $key . ‘` = :’ . $key . ‘,’

}

    $query = rtrim($query, ‘,’);

   $query .= ‘ WHERE `id` = :primaryKey’;

// Set the :primaryKey variable

$fields[‘primaryKey’] = $fields[‘id’];

query($pdo, $query, $fields);

}

You’ll notice we set the primaryKey key manually with this line:

// Set the :primaryKey variable

$fields[‘primaryKey’] = $fields[‘id’];

This is so that the WHERE clause in the query is provided with the relevant ID to update. :id can’t be used, because it has already been used in the query, and each parameter needs a unique name. With this version of the updateJoke function, it’s now possible to run it as we designed earlier:

updateJoke($pdo, [

‘id’ => 1,

‘joketext’ => ‘!false – it\’s funny because it\’s true’]

);

Or we could do this:

updateJoke($pdo, [

‘id’ => 1,

‘authorId’ => 4]

);

Writing Functions

When you write a function, it’s usually easier to write some examples of how you think it should be called before writing the code inside the function itself. This gives you a target to work towards, and some code you can run to see whether it’s working correctly or not.

Improving the Insert Function

Using this knowledge, we can do the same thing for the insertJoke function. The INSERT query can use a syntax different from UPDATE’s, and it works like this:

$query = ‘INSERT INTO `joke` (`joketext`, `jokedate`, `authorId`)

VALUES (:joketext, CURDATE(), :authorId)’;

There are two parts to this—the field names and the values. Firstly, let’s handle the column names. As we did with the updateJoke function, we can use a loop and rtrim to create the list of fields for the first line of the query:

function insertJoke($pdo, $fields) {

$query = ‘INSERT INTO `joke` (‘

foreach ($fields as $key => $value) {

$query .= ‘`’ . $key . ‘`,’;

}

$query = rtrim($query, ‘,’);

$query .= ‘) VALUES (‘;

}

This will generate the first part of the query, and $query will store the following:

INSERT INTO `joke` (`authorId`, `joketext`) VALUES (

… when called with this array:

[

‘authorId’ => 4,

‘joketext’ => ‘!false – it\’s funny because it\’s true’

]

The next part of the query should be the placeholders for the values:

VALUES (:authorId, :joketext)

These are the keys prefixed with a colon (:). Once again, we can use foreach to loop through the column names and add the placeholders before sending the query to the database:

function insertJoke($pdo, $fields) {

$query = ‘INSERT INTO `joke` (‘

foreach ($fields as $key => $value) {

    $query .= ‘`’ . $key . ‘`,’;

}

  $query = rtrim($query, ‘,’);

  $query .= ‘) VALUES (‘;

foreach ($fields as $key => $value) {

   $query .= ‘:’ . $key . ‘,’;

}

  $query = rtrim($query, ‘,’);

  $query .= ‘)’;

  query($pdo, $query);

}

The insertJoke function can now be used to insert data into any of the fields:

insertJoke($pdo, [

   ‘authorId’ => 4,

   ‘joketext’ => ‘!false – it\’s funny because it\’s true’

    ]

);

Of course, it will error if the wrong column names are used, but it’s clearer to anyone seeing this code which data is being placed into each of the columns. As we’ll see later, it also makes it easier to use the function with forms. For now, amend your website to use the two new functions by replacing the existing ones in DatabaseFunctions.php:

function insertJoke($pdo, $fields) {

     $query = ‘INSERT INTO `joke` (‘

foreach ($fields as $key => $value) {

     $query .= ‘`’ . $key . ‘`,’;

}

  $query = rtrim($query, ‘,’);

  $query .= ‘) VALUES (‘;

foreach ($fields as $key => $value) {

      $query .= ‘:’ . $key . ‘,’;

}

$query = rtrim($query, ‘,’);

$query .= ‘)’;

query($pdo, $query);

}

function updateJoke($pdo, $fields) {

    $query = ‘ UPDATE `joke` SET ‘;

 foreach ($fields as $key => $value) {

      $query .= ‘`’ . $key . ‘` = :’ . $key . ‘,’;

}

$query = rtrim($query, ‘,’);

$query .= ‘ WHERE `id` = :primaryKey’;

// Set the :primaryKey variable

$fields[‘primaryKey’] = $fields[‘id’];

      query($pdo, $query, $fields);

}

Amend editjoke.php to use the new updateJoke function:

updateJoke($pdo, [

‘id’ => $_POST[‘jokeid’],

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

‘authorId’ => 1

]);

You might try amending addjoke.php to use the new function:

insertJoke($pdo, [‘authorId’ => 1, ‘jokeText’ => $_POST[‘joketext’]]);

This example can be found in Structure2-ArrayFunctions-ErrorIf you try this, you’ll get an error:

Database error: SQLSTATE[HY000]: General error: 1364 Field
 ‘jokedate’ doesn’t have a default value in
/home/vagrant/Code/Project/includes/DatabaseFunctions.php:5

The reason for the error is that we haven’t supplied a value for the jokedate column.

Handling Dates

The cause of the above error is that we’ve lost the ability to supply a date when inserting a joke. Previously, we used the CURDATE() function. Using the new insertJoke function, you may attempt to set the joke like so:

insertJoke($pdo, [‘authorId’ => 1,

‘jokeText’ => $_POST[‘joketext’],

‘jokedate’ => ‘CURDATE()’

]);

Although the logic here is sound (we want to insert the current date into the jokedate column), this approach won’t work as intended. Just as whatever text is stored in the $_POST[‘joketext’] variable will be inserted into the joketext column, the above code would try to write the value CURDATE() into the jokedate column, rather than executing the function and retrieving the date. To solve this, we can read the current date using PHP—rather than from MySQL—using the PHP DateTime class. The PHP DateTime class can be used to represent dates in PHP and format them in any way. For example:

$date = new DateTime();

echo $date->format(‘d/m/Y H:i:s’);

By default, a new instance of a DateTime, without a provided argument, will represent today’s date. So if you were reading this on the 8th September 2019 at around 7.00 p.m., this would print something like:

08/09/2019 19:12:34

You can give PHP a string to convert to a date and then format the date in any way you like. For example:

$date = new DateTime(‘5th March 2019’);

echo $date->format(‘d/m/Y’);

The string d/m/Y here represents day/month/year, which would print 05/03/ 2019. The PHP DateTime class is very powerful and incredibly useful for handling dates in PHP. However, the only format we need to use is the format MySQL understands. Dates and times in MySQL are always stored using the format YYYY-MM-DD HH:MM:SS. For example, MySQL would store the date of the 13th of July 2019 as 2019-07-13. The DateTime class can be used to represent any date. Today’s date could be induced in this format, like so:

// Don’t give it a date so it uses the current date/time

$date = new DateTime();

echo $date->format(‘Y-m-d H:i:s’);

The above will print something like this:

2019-09-08 19:16:34

This is the format that’s needed when you insert into a DATETIME field in MySQL. The insert function can already handle this. We can just pass the current date as one of the array keys to the function:

$date = new DateTime();

insertJoke($pdo, [

‘authorId’ => 4,

‘joketext’ => ‘Why did the chicken cross the road? To get to the other side’,

‘jokedate’ => $date->format(‘Y-m-d H:i:s’)

]

);

This will work fine. However, every time we want to use the function, we need to remember the date format used by MySQL. This can be a bit of a pain, as it requires repeating the code for formatting the date (and remembering the string Y-m-d H:i:s) each time a date needs to be inserted. Instead, a better approach would be to have the function format the date for us, saving some work each time it’s called:

function insertJoke($pdo, $fields) {

       $query = ‘INSERT INTO `joke` (‘;

foreach ($fields as $key => $value) {

      $query .= ‘`’ . $key . ‘`,’;

}

$query = rtrim($query, ‘,’);

$query .= ‘) VALUES (‘;

foreach ($fields as $key => $value) {

     $query .= ‘:’ . $key . ‘,’;

}

$query = rtrim($query, ‘,’);

$query .= ‘)’;

foreach ($fields as $key => $value) {

       if ($value instanceof DateTime) {

         $fields[$key] = $value->format(‘Y-m-d’);

    }

}

query($pdo, $query, $fields);

}

Using the above version of the function, a date object can be passed into the function without formatting it:

insertJoke($pdo, [

‘authorId’ => 4,

‘joketext’ => ‘!false – it\’s funny because it\’s true’,

‘jokedate’ => new DateTime() ]

);

The function will look for any date objects it has been given and automatically format them to the format needed by MySQL. When the function is called, you don’t need to remember the exact format needed by MySQL; it’s done for you inside the function. The function will automatically convert any DateTime object it comes across into a string that MySQL can understand:

// Loop through the array of fields

   foreach ($fields as $key => $value) {

           //If any of the values are a DateTime object

if ($value instanceof DateTime) {

                   // Then replace the value in the array with the date

                 //in the format Y-m-d H:i:s

    $fields[$key] = $value->format(‘Y-m-d H:i:s’);

   }

}

One operator you haven’t come across yet is the instanceof operator. This is a comparison operator like == or !=. However, instead of checking to see if the two values are the same, it checks to see whether the variable on the left ($value) is the same kind of object as the one on the right (DateTime). One important distinction is that the value is not being checked, only the type. It’s like saying “Is it a car?” rather than “Is it an E-Type Jaguar?” Wherever a DateTime object is found in the array, it’s replaced with the equivalent date string in the format MySQL needs. This allows us to completely forget about the format MySQL actually needs and just supply DateTime objects to the insertJoke function. Let’s also add the automatic date formatting to the updateJoke function:

function updateJoke($pdo, $fields) {

$query = ‘ UPDATE joke SET ‘;

foreach ($fields as $key => $value) {

      $query .= ‘`’ . $key . ‘` = :’ . $key . ‘,’;

}

$query = rtrim($query, ‘,’);

$query .= ‘ WHERE id = :primarykey’;

foreach ($fields as $key => $value) {

     if ($value instanceof DateTime) {

           $fields[$key] = $value->format(‘Y-m-d’);

        }

}

// Set the :primaryKey variable

$fields[‘primaryKey’] = $fields[‘id’];

    query($pdo, $query, $fields);

}

This will take the same approach we used in insertJoke and apply it to the update function. Any DateTime object that is passed to the function in the $fields array will be converted to a string that MySQL understands. As we have copied/pasted code, it’s good practice to move the duplicated code into its own function to save repetition and follow the DRY principle:

function processDates($fields) {

     foreach ($fields as $key => $value) {

          if ($value instanceof DateTime) {

               $fields[$key] = $value->format(‘Y-m-d’);

      }

}

return $fields;

}

Let’s put the function in DatabaseFunctions.php and change the updateJoke and insertJoke functions to make use of it:

function insertJoke($pdo, $fields) {

        $query = ‘INSERT INTO `joke` (‘;

foreach ($fields as $key => $value) {

       $query .= ‘`’ . $key . ‘`,’;

}

$query = rtrim($query, ‘,’);

$query .= ‘) VALUES (‘;

foreach ($fields as $key => $value) {

     $query .= ‘:’ . $key . ‘,’;

}

  $query = rtrim($query, ‘,’);

  $query .= ‘)’;

$fields = processDates($fields);

query($pdo, $query, $fields);

}

function updateJoke($pdo, $fields) {

     $query = ‘ UPDATE `joke` SET ‘;

foreach ($fields as $key => $value) {

      $query .= ‘`’ . $key . ‘` = :’ . $key . ‘,’;

}

  $query = rtrim($query, ‘,’);

  $query .= ‘ WHERE `id` = :primaryKey’;

// Set the :primaryKey variable

$fields[‘primaryKey’] = $fields[‘id’];

$fields = processDates($fields);

query($pdo, $query, $fields);

}

Finally, amend addjoke.php to provide the DateTime() object for the value of the jokedate column:

insertJoke($pdo, [‘authorId’ => 1,

‘jokeText’ => $_POST[‘joketext’],

‘jokedate’ => new DateTime()

]);

This example can be found in tructure2-ArrayFunctions-Dates.

Displaying the Joke Date

While we’re dealing with dates, let’s display the date on which the joke was posted alongside the author’s name in the template. The page will now display something like “By Tom Butler on 2019-08-04”. Firstly, we’ll need to amend the allJokes function to retrieve the date from the database:

function allJokes($pdo) {

     $jokes = query($pdo, ‘SELECT `joke`.`id`, `joketext`,

         `jokedate`, `name`, `email`

                FROM `joke` INNER JOIN `author`

             ON `authorid` = `author`.`id`’);

return $jokes->fetchAll();

}

Now we can reference the date column in the template file jokes.html.php:

<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[’email’],

   ENT_QUOTES,

    ‘UTF-8’

); ?>”>

<?=htmlspecialchars(

            $joke[‘name’],

           ENT_QUOTES,

          ‘UTF-8’

); ?></a> on <?=$joke[‘jokedate’]; ?>)

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

<form action=”deletejoke.php” method=”post”>

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

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

</form>

</p>

</blockquote>

<?php endforeach; ?>

If you run the code above, you’ll see the date printed on the page. Unfortunately, it will be printed in the format that MySQL uses—that is, 2019-08-04, rather than a nicer format that people who are viewing the website would prefer. It’s possible to use the DateTime class to do this for us by amending the template file to create an instance of the DateTime class and formatting the date in a nicer way:

(by <a href=”mailto:<?=htmlspecialchars(

     $joke[’email’],

    ENT_QUOTES,

     ‘UTF-8’

); ?>”>

      <?=htmlspecialchars(

                 $joke[‘name’],

                  ENT_QUOTES,

          ‘UTF-8’

           ); ?></a> on

<?php

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

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

?>)

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

The DateTime class can take an argument of a date and, luckily for us, it understands the format MySQL uses—allowing us to quickly format the data in any way we like. The line echo $date->format(‘jS F Y’); formats the date in a much nicer way, and will display something like 4th August 2019This example can be found in Structure2-ArrayFunctions-Dates2.

Making Your Own Tools

Blacksmiths are unique in that they make their own tools. Carpenters don’t make their saws and hammers, tailors don’t make their scissors and needles, and plumbers don’t make their wrenches, but blacksmiths can make their hammers, tongs, anvils, and chisels — Daniel C. Dennet, Intuition Pumps and Other Tools for Thinking

This is an interesting observation—and we’ll explain why we’ve included in in a moment-but it implies a small paradox. If a blacksmith is needed to make blacksmith’s tools, where did the first blacksmith’s tools come from? As it turns out, there’s actually a simple explanation: the first tools were incredibly crude—just some rocks used to bash out some metal rods. But the blacksmith can fuse two of those rods together to make a basic hammer, and then use that hammer to make an even better hammer, gradually making newer and better equipment. By improving the tools to be more precise and easier to use, the blacksmith can make products faster, create higher-quality items, produce a wider variety of products, make other specialist tools, and let lesser-skilled workers such as apprentices produce products beyond their skill level.

It does take time to create a tool, but once the tool is created, the blacksmith can use it to make thousands of products. Over the long term, the time spent making the tool quickly pays off. You’re probably wondering what any of this has to do with PHP programming. We are going to amend Dennet’s quote slightly and say that blacksmiths are almost unique in being able to make their own tools—because programmers also possess this ability. Every opportunity we listed for blacksmiths to create their own tools exists for programmers as well. In fact, everything you use on your computer is a tool written by another programmer. Even the PHP programming language you’re using is a tool originally written by a developer called Rasmus Lerdorf. Programming languages don’t just suddenly exist. Computers don’t even understand PHP at all! They only understand binary code.

Like blacksmiths, programmers started with very crude tools: punch cards that had a hole or no hole to represent a one or a zero. These were then manually fed into the computer to program it. Writing and understanding the code took an incredible amount of skill. As computers developed, so did the way we program them. Instead of representing everything as ones and zeros with punch cards, programmers invented tools that take human-readable and easier-to-understand code and convert it (or “compile” it, if you want to get technical) into the binary code that the computer understands. It’s easy to forget that programming languages exist for humans, not for computers.

When you sit down in front of your computer with your favorite code editor, you can be forgiven for thinking you’re using the most refined hammer that can be made and that you have all the tools available to pick from. A programming language is just a tool, and you can use it to make your own tools. Every time you write a function, you’re creating a new tool. You can either make tools that have many uses, which you can use over and over again, or tools with limited use that can only be used for one very specific job.

In your kitchen you have many tools. You probably need a knife to prepare nearly every meal, but how often do you use a garlic press? A tool is more useful if it can be used for a variety of different tasks. In the kitchen, the more recipes the tool can help make, the more useful it is. When writing a function in PHP (or any programming language), strive to write functions that are more like the knife than the garlic press. Write functions that can be used over and over again on any website, rather than functions that only apply to a very specific case on a single website. So far in this chapter, we’ve taken functions that need an exact number of arguments in a very specific order and rewritten them as functions that allow arguments to be specified in (almost) any order, with values optionally omitted entirely if they don’t need updating.

The problem with the function now is that it’s more like the garlic press than the knife. Just as the garlic press is only useful for recipes that have garlic, the updateJoke function is only useful when we want to update a record in the joke table. We can’t take this function and use it on another website, because the next website you build probably won’t even have a table called jokeThe next step in refining our tools is to make our functions more like the knife, able to work with any database table. After all, a knife can be used to chop garlic anyway.

Generic Functions

Before we make any large-scale changes, let’s expand the website and add a function for retrieving all the authors from the database in the same manner we used for the allJokes function:

function allAuthors($pdo) {

  $authors = query($pdo, ‘SELECT * FROM `author`’);

   return $authors->fetchAll();

}

Let’s also add functions for inserting and deleting authors from the author table:

function deleteAuthor($pdo, $id) {

    $parameters = [‘:id’ => $id];

    query($pdo, ‘DELETE FROM `author`

     WHERE `id` = :id’, $parameters);

}

function insertAuthor($pdo, $fields) {

       $query = ‘INSERT INTO `author` (‘;

foreach ($fields as $key => $value) {

       $query .= ‘`’ . $key . ‘`,’;

}

$query = rtrim($query, ‘,’);

$query .= ‘) VALUES (‘;

foreach ($fields as $key => $value) {

      $query .= ‘:’ . $key . ‘,’;

}

$query = rtrim($query, ‘,’);

$query .= ‘)’;

$fields = processDates($fields);

query($pdo, $query, $fields);

}

These deleteAuthor and insertAuthor functions are almost identical to the corresponding joke functions, deleteJoke and insertJoke. It would be better to create generic functions that could be used with any database table. That way, we can write the function once and use it for any database table. If we continued down the route of having five different functions for each database table, we’d very quickly end up with a lot of very similar code. The differences between the functions are just the names of the tables. By replacing the table name with a variable, the function can be used to retrieve all the records from any database table. That way, it’s no longer necessary to write a different function for each table:

function findAll($pdo, $table) {

$result = query($pdo, ‘SELECT * FROM `’ . $table . ‘`’);

return $result->fetchAll();

}

Once the function has been written, this new tool can be used to retrieve all the records from any database table:

// Select all the jokes from the database

$allJokes = findAll($pdo, ‘joke’);

// Select all the authors from the database

$allAuthors = findAll($pdo, ‘author’);

The same thing can be done with delete:

function delete($pdo, $table, $id) {

    $parameters = [‘:id’ => $id];

    query($pdo, ‘DELETE FROM `’ . $table . ‘`

    WHERE `id` = :id’, $parameters);

}

This allows deleting a record from any table based on its ID:

// Delete author with the ID of 2

delete($pdo, ‘author’, 2);

// Delete joke with the id of 5

delete($pdo, ‘joke’, 5);

This function works, but it’s still a little inflexible: it assumes that the primary key field in the table is called id. This function can only work with tables that have a field named id for their primary key, which isn’t always the case. A table that stored information about books, for example, might have isbn as the primary key. In order for our function to work with any database table structure, the primary key can also be replaced with a variable:

function delete($pdo, $table, $primaryKey, $id ) {

   $parameters = [‘:id’ => $id];

  query($pdo, ‘DELETE FROM `’ . $table . ‘`

  WHERE `’ . $primaryKey . ‘` = :id’, $parameters);

}

Whenever the delete function is called, it’s now supplied with four arguments:

  • the $pdo database connection
  • the name of the table to delete a record from
  • the ID of the record to delete
  • the field that acts as the primary key

And it can be called like this:

// Delete author with the ID of 2

delete($pdo, ‘author’, ‘id’, 2);

// Delete joke with the id of 5

delete($pdo, ‘joke’, ‘id’, 5);

// Delete the book with the ISBN 978-3-16-148410-0

delete($pdo, ‘book’, ‘978-3-16-148410-0’, ‘isbn’);

Along with the delete and select functions, let’s do the same thing with the update and insert functions by replacing the table name with a function argument:

function insert($pdo, $table, $fields) {

    $query = ‘INSERT INTO `’ . $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 = processDates($fields);

     query($pdo, $query, $fields);

}

function update($pdo, $table, $primaryKey, $fields) {

     $query = ‘ UPDATE `’ . $table .’` SET ‘;

foreach ($fields as $key => $value) {

     $query .= ‘`’ . $key . ‘` = :’ . $key . ‘,’;

}

$query = rtrim($query, ‘,’);

$query .= ‘ WHERE `’ . $primaryKey . ‘` = :primaryKey’;

// Set the :primaryKey variable

$fields[‘primaryKey’] = $fields[‘id’];

$fields = processDates($fields);

query($pdo, $query, $fields);

}

Notice that in the update function, I’ve also created a variable called primaryKey. That’s because we can’t assume that the primary key (used in the WHEREid= :primaryKey part of the query) will always be idWe created a function called getJoke, which found a specific joke by its ID. That function, too, can be made to work with any database table. This findById function can be used to find a single record from any table using the primary key:

function findById($pdo, $table, $primaryKey, $value) {

      $query = ‘SELECT * FROM `’ . $table . ‘`

     WHERE `’ . $primaryKey . ‘` = :value’;

$parameters = [

            ‘value’ => $value

];

$query = query($pdo, $query, $parameters);

return $query->fetch();

}

We now have a set of functions that can be used to interact quickly and easily with any database table and use PHP’s DateTime class for dates:

// Add a new record to the database

$record = [

        ‘joketext’ => ‘!false – it\’s funny because it\’s true’,

         ‘authorId’ => 2,

         ‘jokedate’ => new DateTime()

];

insert($pdo, ‘joke’, $record);

// Delete from the author table where `id` is `2`

delete($pdo, ‘author’, ‘id’, 2);

$jokes = findAll($pdo, $joke);

Finally, for the sake of completeness, let’s also make the same change to the totalJokes function, allowing us to get the number of records in any table:

function total($pdo, $table) {

   $query = query($pdo, ‘SELECT COUNT(*)

    FROM `’ . $table . ‘`’);

   $row = $query->fetch();

   return $row[0];

}

Using These Functions

Now that we’ve got the functions, let’s put them into our controllers. Firstly, we’ll change the the addjoke.php controller to use the new generic insert function. It currently looks like this:

<?php

if (isset($_POST[‘joketext’])) {

      try {

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

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

insertJoke($pdo, [‘authorId’ => 1,

   ‘jokeText’ => $_POST[‘joketext’],

    ‘jokedate’ => new DateTime()]);

      header(‘location: jokes.php’);

// …

To replace the insertJoke function with the generic insert function, we just need to change the name and supply the name of the table:

<?php

if (isset($_POST[‘joketext’])) {

try {

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

insert($pdo, ‘joke’, [‘authorId’ => 1, ‘jokeText’ => $_POST[‘joketext’],

‘jokedate’ => new DateTime()]);

header(‘location: jokes.php’);

// …

Now amend editjoke.php and deletejoke.php files in the same way. Firstly editjoke.php:

<?php

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

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

try {

    if (isset($_POST[‘joketext’])) {

           update($pdo, ‘joke’, ‘id’, [‘id’ => $_POST[‘jokeid’],

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

                               ‘authorId’ => 1]);

   header(‘location: jokes.php’);

} else {

     $joke = findById($pdo, ‘joke’, ‘id’, $_GET[‘id’]);

    $title = ‘Edit joke’;

    ob_start();

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

    $output = ob_get_clean();

      }

}

// …

And here’s the updated deletejoke.php:

<?php

try {

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

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

delete($pdo, ‘joke’, ‘id’, $_POST[‘id’]);

// …

The next part is the list of jokes. Currently, it uses the allJokes function, which also retrieves information about the author of each joke. There’s no simple way to write a generic function that retrieves information from two tables. It would have to know which fields were used to join the tables and which tables were being joined. It would be possible to do this with a function with lots of arguments, but the function would be difficult to use and overly complex. Instead, we can use the generic findAll and findById functions to achieve this:

$result = findAll($pdo, ‘joke’);

$jokes = [];

foreach ($result as $joke) {

    $author = findById($pdo, ‘author’, ‘id’, $joke[‘authorId’]);

$jokes[] = [

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

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

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

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

   ];

}

The complete jokes.php looks like this:

<?php

try {

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

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

      $result = findAll($pdo, ‘joke’);

$jokes = [];

foreach ($result as $joke) {

        $author = findById($pdo, ‘author’, ‘id’,

                           $joke[‘authorId’]);

$jokes[] = [

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

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

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

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

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

     ];

}

$title = ‘Joke list’;

$totalJokes = total($pdo, ‘joke’);

ob_start();

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

$output = ob_get_clean();

} catch (PDOException $e) {

    $title = ‘An error has occurred’;

     $output = ‘Database error: ‘ . $e->getMessage() . ‘

     in ‘ . $e->getFile() . ‘:’ . $e->getLine();

}

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

This example can be found in Structure2-GenericFunctionsThis works by fetching the list of jokes (without the author information), then looping over each joke and finding the corresponding author by their id, then writing the complete joke with the information from both tables into the $jokes array. This is essentially what an INNER JOIN does in MySQL. Each joke is now made up of values from the $author variable, which is a record from the author table and values from the joke table. You may have realized that this method is going to be slower, because more queries are sent to the database. This is a common issue with these kinds of generic functions, and it’s called the N+1 problem1 There are several methods for reducing this performance issue2, but for smaller sites, where we’re dealing with hundreds or thousands of records rather than millions, it’s unlikely to cause any real problems. The difference will likely be in the region of milliseconds.

Repeated Code Is the Enemy

Whenever you’re writing software, you need to be vigilant about repeated code. Any time you find yourself with two or more copies of identical or very similar code, it’s always worth taking a step back and looking to see if there’s a way of merging the code into one reusable block. By creating the generic functions insert, update, delete, findAll and findById, it’s now very quick and easy for us to create a website that deals with any kind of database operation. The functions can be used to interact very simply with any database table we might ever need. But there’s still room for improvement. The files addjoke.php and editjoke.php do very similar jobs: they display a form, and when the form is submitted, they send the submitted data off to the database.

Similarly, the template files addjoke.html.php and editjoke.html.php are almost identical. There’s very little difference between them. The only real difference is that the edit page displays a pre-filled form where the add page presents an empty form. The problem with repeated code is that, if something has to change, you have to make the same change in multiple locations. What if we wanted to add a category to a joke, so that when a joke was added or edited the user could choose Knock knock jokes, Programming Jokes, Puns, One liners, etc? We could achieve this by adding a <select> box to the add joke page, but we’d also need to make the same change to the edit joke page. Each time we made a change to addjoke.php, we’d need to make the corresponding change in editjoke.phpIf you ever find yourself in a situation like this, where you have to make similar changes in multiple files, it’s a good sign that you should combine both sets of code into one. Of course, the new code needs to handle both cases. There are a couple of differences between addjoke.php and editjoke.php:

  1. addjoke.php issues an INSERT query, while editjoke.php issues an UPDATE query.
  2. editjoke.php’s template file has a hidden input that stores the ID of the joke being edited.

But everything else is almost the same. Let’s merge both pieces of code together, so that editjoke.php can handle both editing an existing joke and adding a new one. The script will be able to tell whether we’re adding or editing based on whether or not an ID is supplied in the URI. Visiting editjoke.php?id=12 will load the joke with the ID 12 from the database and allow us to edit it. When the form is submitted, it will issue the relevant UPDATE query, while just visiting editjoke.php—without an ID specified—will display an empty form and, when submitted, perform an INSERT query.

Creating a Page for Adding and Editing

Let’s handle the form first—which either loads the joke into the fields or displays a blank form. Currently, editjoke.php assumes there’s the id GETvariable set, and loads the joke accordingly before loading the template file:

else {

$joke = findById($pdo, ‘joke’, ‘id’, $_GET[‘id’]);

$title = ‘Edit joke’;

ob_start();

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

$output = ob_get_clean();

}

This can be replaced with an if statement to only load the joke from the database if an id has actually been provided:

else {

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

$joke = findById($pdo, ‘joke’, ‘id’, $_GET[‘id’]);

}

$title = ‘Edit joke’;

ob_start();

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

$output = ob_get_clean();

}

If you try the above code and visit editjoke.php without supplying an ID as a GET variable, it won’t quite work as intended. You’ll actually see some strange errors appearing inside the <textarea>. That’s because when editjoke.html.php is loaded, it references a variable called $joke which, because of the new if statement, is only created when the ID is supplied. One solution would be to load the addjoke.html.php file if there’s no ID set and editjoke.html.php if there is. Unfortunately, this doesn’t solve the initial problem: to add a new field to the form using this approach, we’d still need to edit two files. Instead, let’s amend editjoke.html.php so it only tries to print the existing data into the textarea and hidden input if the joke variable is set:

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

    <input type=”hidden” name=”jokeid” value=”<?php if (isset($joke)): ?>

    <?=$joke[‘id’]?>

     <?php endif; ?>

?>”>

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

</label>

<textarea id=”joketext” name=”joketext” rows=”3″ cols=”40″>

     <?php if (isset($joke)): ?>

    <?=$joke[‘joketext’]?>

<?php endif; ?></textarea>

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

</form>

Now the joketext and id will only be written to the page if the $joke variable has been set, and it’s only set when we’re editing an existing record. Before continuing, let’s tidy up the code a little. Previously, to print the ID of the joke into the hidden input, it required a simpler piece of code:

<input type=”hidden” name=”jokeid”

value=”<?=$joke[id]?>”>

Now that we’re using the if statement, we need the full <?php opening and several lines of code. A nice feature introduced in PHP 7 is the Null coalescing operator. It’s a very confusing name, but is actually just a shorthand for this:

if (isset($something)) {

echo $something;

}

else {

echo ‘variable not set’;

}

This can be expressed using the null coalescing operator (??) like so:

echo $something ?? ‘variable not set’;

On the left-hand side of the ?? operator is the variable being checked, and on the right is the output that’s used if it’s not set. In the case above, if the $something variable is set, it will print the contents of the variable. If the variable isn’t set, it will print variable not set, and even better, it also works with arrays. Let’s make use of it in our templates, rather than having to write out the entire if statement for each field:

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

<input type=”hidden” name=”jokeid”

value=”<?=$joke[‘id’] ?? ”?>”>

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

</label>

<textarea id=”joketext” name=”joketext” rows=”3″

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

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

</form>

In this instance, the right-hand part is an empty string. Either the text of the loaded joke will be displayed in the text box, or if the $joke variable is not set, it will display an empty string in the box. To complete this page, we need to change what happens when the form is submitted. Either an update or insert query will need to be run. The simplest way would be to look to see if the ID field has been supplied:

if (isset($_POST[‘id’]) && $_POST[‘id’] != ”) {

   update(…);

}

else ;

    insert(…)

}

Although this would work, once again there’s an opportunity to make this more generic. This logic for if the ID is set, update, otherwise insert is going to be the same for any form. If we had a form for authors, the same logic would be useful: if there’s no ID, perform an insert; if there is, perform an update. Indeed, this would apply anywhere we have a form that will be used for add or edit. The code above would need to be repeated for any form we implemented in this way. Another problem with this approach is that it wouldn’t work in cases where the primary key was a normal field on the form, such as ISBNs for books. Regardless of whether an UPDATE or INSERT query is required, the ISBN would be provided. Instead, we can try to insert a record, and if it’s unsuccessful, update instead using a try … catch statement:

try {

       insert(…);

}

catch(PDOException $e) {

       update(…);

}

Now an INSERT will be sent to the database, but it may cause an error—“Duplicate key”—when a record with the supplied ID is already set. If an error does occur, an UPDATE query is issued instead to update the existing record. To stop us needing to repeat this logic for every form, we could make another function, called save, which performs an insert or an update using the try … catch above:

function save($pdo, $table, $primaryKey, $record) {

    try {

          if ($record[$primaryKey] == ”) {

                   $record[$primaryKey] = null;

     }

    insert($pdo, $table, $record);

}

catch (PDOException $e) {

      update($pdo, $table, $primaryKey, $record);

     }

}

This will work for any record in any table. If there’s an error when trying to insert, it will issue the corresponding update query instead. The save function here needs all the arguments required by both the insert and update functions in order to call them. There’s no need to repeat any of the logic from those functions; they can just be called from within the relatively short save function. The line if ($record[$primaryKey] == ”) { is used here so that the INSERT function never tries to insert an empty string into the ID column. Most of the time, the primary key will be an INT column that only accepts numbers. By replacing the empty string with NULL, it will trigger MySQL’s auto_increment feature and generate a new ID. Now editjoke.php can be modified to use the new save function:

try {

    if (isset($_POST[‘joketext’])) {

      save($pdo, ‘joke’, ‘id’, [‘id’ => $_POST[‘jokeid’],

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

         ‘jokedate’ => new DateTime(),

        ‘authorId’ => 1]);

  header(‘location: jokes.php’);

}

Finally, you can delete the controller addjoke.php and the template addjoke.html.php, as they’re no longer needed. Both add and edit are now handled by editjoke.php. Change the link in the menu in layout.html.php to go to editjoke.php instead of addjoke.phpThis example can be found in Structure2-GenericFunction-Save.

Further Polishing

The new save function can be used to add records to the database. It will automatically insert or update depending on whether the id field is set to an existing ID. For the add form, where the ID field isn’t set, there’s still an id key in the $_POST array, because the hidden input still exits on the form. This allows us to treat add and edit identically, saving us a lot of work. Without this generic save function, we’d need different HTML forms and different controller logic for processing the submissions. Using this approach, there’s less code to write initially, and the lack of HTML or PHP code repetition is an extra bonus. To add a new field, all we need to do is add the field to the database, amend the template and supply the new value to the save function.

Adding new fields is a very simple process. Because the save function handles both INSERT and UPDATE queries, and the insert and update functions both dynamically generate the queries, adding a field to the query is now as easy as adding an entry to an array! This updated code is incredibly easy to manage compared to the code we started with at the beginning of the  chapter. However—and you’re probably anticipating me saying this by now—it’s always worth asking whether it can be simplified further. Once again, the answer is yes. There’s a little repetition here:

[

  ‘id’ => $_POST[‘jokeid’],

  ‘authorId’ => 1,

  ‘jokedate’ => new DateTime(),

  ‘joketext’ => $_POST[‘joketext’]

];

Each field in the $_POST array is mapped to a key in the $joke array with the same name. Take a look at the line ‘joketext’ => $_POST[‘joketext’],. All we’re really doing here is creating a key in the $joke array with the name joketext and the value from the $_POST array’s joketext element. Really, we’re just copying data from the $_POST array exactly into the $joke array. If the form had more fields, we’d need to copy those too. It would be possible to do something similar using this code:

$joke = $_POST;

$joke[‘authorId’] = 1;

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

save($pdo, ‘joke’, ‘id’, $joke);

This will automatically include all the fields from the form in the $joke array without needing to manually copy them. Now, if we added a new field to the form, there are only two steps: add the column in the database and add the form. As long as the column name in the database is the same as the field name on the form, it’s possible to add new fields to the form without ever opening up the controller! As neat as that sounds, if you do try the code above, you’ll find you get an error. That’s because the $_POST array also contains the submit button. When the INSERT query is generated, it would actually generate this query:

INSERT INTO `joke` (`joketext`, `jokedate`, `authorid`, `submit`)

This is obviously a problem: there’s no submit column in the database table joke. A quick and dirty way of fixing this is to remove the submit button from the array using unset:

$joke = $_POST;

// Remove the submit element from the array

unset($joke[‘submit’]);

$joke[‘authorId’] = 1;

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

save($pdo, ‘joke’, ‘id’, $joke);

Although this works, the problem with this approach is that you’d have to remove any form elements you don’t want inserted into the database. For example, if you have a checkbox on the form that sends an email when checked, you’d also remove this checkbox from the $_POST array prior to calling the save function. As usual, there’s a better way. When using HTML forms, you can actually send an array as post data by changing the field names. Change editjoke.html.php to this:

<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>

Each of the fields that represents some data for the joke has been changed slightly. The name attribute of each form field has been updated to represent an array: jokeid is now joke[id] and joketext is now joke[joketext]This tells PHP to treat these fields like an array when the form is submitted.If you submit the form, the $_POST array will store two values: submit and joke. $_POST[‘joke’] is itself an array from which you can read the id value using $_POST[‘joke’][‘id’]For our purposes, we can read all of the information for the joke using $joke = $_POST[‘joke’] and it won’t contain the submit button entry, or any other array keys/values that we don’t want to send to the save function.

$joke = $_POST[‘joke’];

$joke[‘authorId’] = 1;

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

The $joke array will contain all the values from $_POST[‘joke’] and any we want to add that don’t come from the form—in this case, authorId and jokedate. Following this approach, however, it’s important that the field names on the form directly match up to column names in the database, so I’ve used joke[id] instead of joke[jokeid]. The latter would try to write to a column in the database called jokeid, which doesn’t exist. Finally, you’ll need to update the if statement that detects whether the form has been submitted to look for the new joke key, rather than joketext which no longer exits:

if (isset($_POST[‘joke’])) {

The complete controller code now looks like this:

 Structure2-GenericFunctions-SaveArray

<?php

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

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

try {

   if (isset($_POST[‘joke’])) {

        $joke = $_POST[‘joke’];

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

       $joke[‘authorId’] = 1;

       save($pdo, ‘joke’, ‘id’, $joke);

   header(‘location: jokes.php’);

} else {

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

         $joke = findById($pdo, ‘joke’, ‘id’, $_GET[‘id’]);

}

  $title = ‘Edit joke’;

  ob_start();

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

  $output = ob_get_clean();

    }

} catch (PDOException $e) {

         $title = ‘An error has occurred’;

        $output = ‘Database error: ‘ . $e->getMessage() . ‘ in ‘

       . $e->getFile() . ‘:’ . $e->getLine();

}

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

If we wanted add a field to the joke table and alter the form now, it would only require two changes: adding the field to the database and then editing the HTML form. A single update to editjoke.html.php will let us add a form field that works for both the edit and add pages.

Moving Forward

We’ve showed you how to reduce repeated code and write functions for use with any database table. We’ve moved from some very specific functions to functions that can be used in several different situations. You now have a set of tools you can use to extend this website or even write a completely different one. None of the functions are tied to concepts like jokes or authors, which means we could use them on a website that dealt with entirely different concepts—for example, books, products, blogs, or anything you can think of. Now that you’ve written the tools, the hard part is done, and in your next project you can save a lot of time by reusing the tools you’ve created.

This Is A Custom Widget

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

This Is A Custom Widget

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