PHP CHAPTER 2 2019-01-02T12:19:19+00:00

PHP Chapter 2

Publishing MySQL Data on the Web

This is it—the stuff you signed up for!You’ll learn how to take information stored in a MySQL database and display it on a web page for all to see. So far, you’ve written your first PHP code and learned the basics of MySQL, a relational database engine, and PHP, a server-side scripting language. Now you’re ready to learn how to use these tools together to create a website where users can view data from the database and even add their own.

The Big Picture

Before we leap forward, it’s worth taking a step back for a clear picture of our ultimate goal. We have two powerful tools at our disposal: the PHP scripting language and the MySQL database engine. It’s important to understand how these will fit together. The purpose of using MySQL for our website is to allow the content to be pulled dynamically from the database to create web pages for viewing in a regular browser. So, at one end of the system you have a visitor to your site using a web browser to request a page. That browser expects to receive a standard HTML document in return. At the other end you have the content of your site, which sits in one or more tables in a MySQL database that only understands how to respond to SQL queries (commands).

Relationships between the web server, browser, PHP and MySQL

As shown in the image above, the PHP scripting language is the go-between that speaks both languages. It processes the page request and fetches the data from the MySQL database (using SQL queries just like those you used to create a table of jokes . It then spits it out dynamically as the nicely formatted HTML page that the browser expects. Just so it’s clear and fresh in your mind, this is what happens when there’s a visitor to a page on your website:

  1. The visitor’s web browser requests the web page from your web server.
  2.  The web server software (typically Apache or NGINX) recognizes that the requested file is a PHP script, so the server fires up the PHP interpreter to execute the code contained in the file.
  3. Certain PHP commands connect to the MySQL database and request the content that belongs in the web page.
  4. The MySQL database responds by sending the requested content to the PHP script.
  5. The PHP script stores the content into one or more PHP variables, then uses echo statements to output the content as part of the web page.
  6. The PHP interpreter finishes up by handing a copy of the HTML it has created to the web server.
  7. The web server sends the HTML to the web browser as it would a plain HTML file, except that instead of coming directly from an HTML file, the page is the output provided by the PHP interpreter. The browser has no way of knowing this, however. As far as the browser is concerned, it’s requesting and receiving a web page like any other.

Creating a MySQL User Account

In order for PHP to connect to your MySQL database server, it will need to use a username and password. So far, all that your joke database contains is a number of pithy bon mots, but before long it may contain sensitive information like email addresses and other private details about the users of your website. For this reason, MySQL is designed to be very secure, giving you tight control over what connections it will accept and what those connections are allowed to do. You could connect to the database from your PHP script using the same username (homestead) and password (secret), but it’s useful to create a new account—because if you have a web server, you may want to use it to host more than one website. By giving each website its own user account, you’ll have more control over who has access to the data for any given site. If you’re working with other developers, you can give them access to the sites they’re working on, but no more. You should create a new user account with only the specific privileges it needs to work on the ijdb database that your website depends upon. Let’s do that now:To create a user, open up MySQL Workbench and connect to your server.

In the panel on the left-hand side of the window, there’s an option labeled Users and Privileges.

User management

After clicking on the link, you’re able to add a user. There’s already a few listed in the middle panel, including the homestead user you used to log in.

To add a new user, click the Add Account button near the bottom of the window. This will enable the fields that were visible on the right-hand side. You’ll notice there are four tabs, offering quite a few options. Luckily, you can leave almost all of them with their defaults values.

The main thing you need to add are the username and password you want to use to log in. Fill in the first form using the following information, leaving everything else at its defaults:

  Login name | ijdbuser

———- | ———- |

If you prefer, you can just name the user ijdb. It’s common to give an account restricted to accessing a single database the name of that database. We’ve chosen to name it ijdbuser to help clarify the distinction between the name of the database (ijdb) and the user account that’s allowed to access it (ijdbuser).

Limit to Hosts Matching | localhost

———- | ———- |

This option allows you to add extra security by only allowing connections from a specific location, instead of allowing anyone to connect to your database from anywhere on the internet. By entering localhost into this box, you’ll only be able to connect from the computer running MySQL. Even if someone did manage to get hold of your database username and password, they wouldn’t be able to log in without access to the server it’s running from.

Password | mypassword

———- | ———- |

This is just the password we are going to use. You should probably have your own unique password, and remember it for later use in the PHP scripts you’re going to write.

Confirm Password | mypassword

———- | ———- |

Enter your password again, just to verify that you typed it correctly the first time.

Press Apply, and your new user will appear in the central panel. Click on the user and then select the Schema Privileges. By default, a user doesn’t have access to read or write to any schemas. All they’ll be able to do is log in. In order to allow your new user to access the jokes database, you need to click Add Entry, which opens a new window. Select Selected Schema and choose the ijdb schema from this list. This will give your new user access to the ijdb schema and no others. If someone did manage to gain access to the database with the ijdbuser account, they wouldn’t be able to see data from any other website that you might have running on the server.

When you click OK, you’ll see a lot of checkboxes. Some of these are SQL commands you saw in the last chapter. Give your user full access by clicking Select All and finally press Apply again. Your user is now set up, and you’ll see the privileges in the box above the checkboxes:

Users and privileges

Now that the user ijdbuser has been created, we can use it to connect to the database. It’s possible to set up a connection in MySQL Workbench with this user, but since the permissions are limited, it’s better to keep MySQL Workbench using the homestead account. Instead, we’re going to use the new user when connecting from a PHP script.

Connecting to MySQL with PHP

Before you can retrieve content from your MySQL database for inclusion in a web page, you must know how to establish a connection to MySQL from inside a PHP script. So far, you’ve used an application called MySQL Workbench to connect to your database. Just as the MySQL Workbench can connect directly to a running MySQL server, so too can your own PHP scripts. There are three methods of connecting to a MySQL Server from PHP:

  • the MySQL library
  • the MySQLi library
  • the PDO library

These all essentially do the same job, connecting to the database and sending queries to it, but they use different code to achieve it. The MySQL library is the oldest method of connecting to the database and was introduced in PHP 2.0. The features it contains are minimal, and it was superseded by MySQLi as of PHP 5.0 (released in 2004). To connect and query the database using the old MySQL library, functions such as mysql_connect() and mysql_query() are used. These functions have been deprecated—meaning they should be avoided—since PHP 5.5, and have been removed from PHP entirely since PHP 7.0.

Although most developers saw the reason for the change as soon as PHP 5.0 was released, there are still hundreds of articles and code examples on the Web using these now non-existent mysql_* functions—despite the fact that MySQLi has effectively been the preferred library for over ten years. If you come across a code example that contains the line mysql_connect(), check the date of the article. It’s probably from the early 2000s, and in programming, you should never trust anything that old. 

In PHP 5.0, the MySQLi library, standing for “MySQL Improved”, was released to address some of the limitations in the original MySQL library. You can identify the use of MySQLi, because the code will use functions such as mysqli_connect() and mysqli_query()Shortly after the release of the MySQLi library in PHP 5.0, PHP 5.1 was released, with a significant number of changes that helped shape the way we write PHP today (mostly to do with object-oriented programming, which you’ll see plenty of later ). One of the major changes in PHP 5.1 was that it introduced a third library, PDO (PHP Data Objects) for connecting to MySQL databases.

There are a few differences between PDO and MySQLi, but the main one is that you can use the PDO library to connect to almost any database server—such as an Oracle server, or a Microsoft SQL Server. For developers, the biggest advantage of this generic approach is that once you’ve learned how to use the library to interact with a MySQL database, it’s very simple to interact with another database server. Arguably, it’s simpler to write code for PDO, and there are some nuances that can make PDO code more readable—named parameters in prepared statements being the main benefit. After that little history lesson, you’re probably eager to get back to writing code. Here’s how you use PDO to establish a connection to a MySQL server:

new PDO(‘mysql:host=hostname;dbname=database’, ‘username’, ‘password’)

For now, think of new PDO as a built-in function, just like the rand function . If you’re thinking “Hey, functions can’t have spaces in their names!”, you’re smarter than the average bear, and I’ll explain exactly what’s going on here in a moment. In any case, it takes three arguments:

  1. a string specifying the type of database (mysql:), the hostname of the server (host=hostname;), and the name of the database (dbname=database)
  2. the MySQL username you want PHP to use
  3. the MySQL password for that username

You may remember that PHP functions usually return a value when they’re called. This new PDO “function” returns a value called a PDO object that identifies the connection that’s been established. Since we intend to make use of the connection, we should hold onto this value by storing it in a variable. Here’s how that looks, with the necessary values filled in to connect to your database:

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

‘ijdbuser’,

‘mypassword’);

As described, the exact values of the three function parameters may differ for your MySQL server. At the very least, you’ll need to substitute in the password you set for your ijdbuser user (assuming you used a password other than mypassword, the one I chose). What’s important to see here is that the value returned by new PDO is stored in a variable named $pdo.

The MySQL server is a completely separate piece of software from the web server. Therefore, we must consider the possibility that the server may be unavailable or inaccessible due to a network outage, or because the username/password combination you provided is rejected by the server, or because you just forgot to start your MySQL server! In such cases, new PDO won’t run, and will throw a PHP exception. If you’re wondering what it means to “throw a PHP exception,” brace yourself! You’re about to discover some more features of the PHP language. A PHP exception is what happens when you tell PHP to perform a task and it’s unable to do it. PHP will try to do what it’s told, but will fail; and in order to tell you about the failure, it will throw an exception at you. An exception is little more than PHP just crashing with a specific error message. When an exception is thrown, PHP stops. No lines of code after the error will be executed. As a responsible developer, it’s your job to catch that exception and do something about it so the program can continue.

Uncaught Exceptions

If you don’t catch an exception, PHP will stop running your PHP script and display a spectacularly ugly error message. That error message will even reveal the code of your script that threw the error. In this case, that code contains your MySQL username and password, so it’s especially important to avoid the error message being seen by users!

To catch an exception, you should surround the code that might throw an exception with a try … catch statement:

try {

   ⋮ do something risky

}

catch (ExceptionType $e) {

handle the exception

}

You can think of a try … catch statement like an if … else statement, except that the second block of code is what happens if the first block of code fails to run.It will make more sense if we put it all together and show you what we have:

try {

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

      ‘idjbuser’, ’mypassword’);

$output = ‘Database connection established.’;

}

catch (PDOException $e) {

    $output = ‘Unable to connect to the database server.’;

}

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

As you can see, this code is a try … catch statement. In the try block at the top, we attempt to connect to the database using new PDO. If this succeeds, we store the resulting PDO object in $pdo so that we can work with our new database connection. If the connection is successful, the $output variable is set to a message that will be displayed later. Importantly, inside a try … catch statement, any code after an exception has been thrown will not get executed. In this case, if connecting to the database throws an exception (maybe the password is wrong or the server isn’t responding), the $output variable will never get set to “Database connection established”.

If our database connection attempt fails, PHP will throw a PDOException, which is the type of exception that new PDO throws. Our catch block, therefore, says that it will catch a PDOException (and store it in a variable named $e). Inside that block, we set the variable $output to contain a message about what went wrong. However, this error message isn’t particularly useful. All it tells us is that PDO could not connect to the database server. It would be better to have some information about why that was—for example, because the username and password were invalid. The $e variable contains details about the exception that occurred, including an error message describing the problem. We can add this to the output variable using concatenation:

try {

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

  ‘idjbuser’, ‘mypassword’);

$output = ‘Database connection established.’;

}

catch (PDOException $e) {

   $output = ‘Unable to connect to the database server: ‘ . $e;

}

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

The $e Variable

The $e variable isn’t actually a string, but an object. We’ll come onto what that means shortly. For now, though, all you need to know is that it’s possible to treat the $e variable as a string and use it to print a more descriptive error message.

Like an if … else statement, one of the two branches of a try … catch statement is guaranteed to run. Either the code in the try block will execute successfully or the code in the catch block will run. Regardless of whether the database connection was successful, there will be a message in the $output variable—either the error message, or the message saying the connection was successful. Finally, regardless of whether the try block was successful, or the catch block runs, the template output.html.php is included. This is a generic template that just displays some text to the page:

<!doctype html>

<html>

<head>

<meta charset=”utf-8″>

<title>Script Output</title>

</head>

<body>

<?php echo $output; ?>

</body>

</html>

The complete code can be found in Example: PHPMySQL-ConnectWhen the template is included, it will display either the error message or the “Database connection established” message. We hope the aforementioned code is now making some sense to you. Feel free to go back to the start of this section and read it all again if you’re lost, as there were some tricky concepts in there. Once you have a firm grip on the code, however, you’ll probably realize that I’ve still left one mystery unexplained: PDOs. Just what exactly is new PDO, and when I said it returns a “PDO object”, just what exactly is an object?

Schemas

All downloaded sample code includes a schema called ijdb_sample and a user called ijdb_sample, so that you’re able to run it regardless of what you called your schema and user. A file containing the database is provided as database.sql, which you can import. If you use the web-based sample code viewer provided, the idbj_sample database will be created as you load a sample, but any changes to this schema will be lost when you view another sample. (You can mess things up, and switching to another sample and back will reset it, but if you want to keep any changes you make, make them in the schema which you created.) If you want to load the sample data into your schema using MySQL Workbench, import database.sql from the project directory by selecting Data Import/ Restore, select Import from self-contained file, browse to database.sql and select your schema name in default target schema. If you have created any tables with the same name, they’ll be overwritten and all records lost.

A Crash Course in Object-oriented Programming

You may have noticed the word “object” beginning to creep into my vocabulary in the previous section. PDO is the PHP Data Objects extension, and new PDO returns a PDO object. In this section, we’d like to explain what objects are all about. Perhaps you’ve come across the term object-oriented programming (OOP) in your own explorations of PHP or of programming in general. OOP is an advanced style of programming that’s especially suited to building really complex programs with a lot of parts. Most programming languages in active use today support OOP. Some of them even require you to work in an OOP style. PHP is a little more easygoing about it, and leaves it up to the developer to decide whether or not to write their scripts in the OOP style.

So far, we’ve written our PHP code in a simpler style called procedural programming, and we’ll continue to do so for now, with a more detailed look at objects later on. Procedural style is well suited to the relatively simple projects we’ll tackle at the moment. However, almost all complex projects you’ll come across use OOP. That said, the PDO extension that we’ll use to connect to and work with a MySQL database is designed in the object-oriented programming style. What this means is that, rather than simply calling a function to connect to MySQL and then calling other functions that use that connection, we must first create a PDO object that will represent our database connection, and then use the features of that object to work with the database. Creating an object is a lot like calling a function. In fact, you’ve already seen how to do it:

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

‘ijdbuser’, ‘mypassword’);

The new keyword tells PHP that you want to create a new object. You then leave a space and specify a class name, which tells PHP what type of object you want to create. A class is a set of instructions that PHP will follow to create an object. You can think of a class as being a recipe, such as for a cake, and an object being the actual cake that’s produced from following the recipe. Different classes can produce different objects, just as different recipes can produce different dishes. Just as PHP comes with a bunch of built-in functions that you can call, PHP comes with a library of classes that you can create objects from. new PDO, therefore, tells PHP to create a new PDO object—that is, a new object of the built-in PDO class.

In PHP, an object is a value, just like a string, number, or array. You can store an object in a variable or pass it to a function as an argument—all the same stuff you can do with other PHP values. Objects, however, have some useful additional features. First of all, an object behaves a lot like an array, in that it acts as a container for other values. As we saw before, you can access a value inside an array by specifying its index (for example, $birthdays[‘Kevin’]). When it comes to objects, the concepts are similar but the names and code are different. Rather than accessing the value stored in an array index, we say that we’re accessing a property of the object. Instead of using square brackets to specify the name of the property we want to access, we use arrow notation (->)—for instance, $myObject->someProperty:

$myObject = new SomeClass();    // create an object
$myObject->someProperty = 123;  // set a property's value
echo $myObject->someProperty;   // get a property's value

Whereas arrays are normally used to store a list of similar values (such as an array of birthdays), objects are used to store a list of related values (for example, the properties of a database connection). Still, if that’s all objects did, there wouldn’t be much point to them: we might just as well use an array to store these values, right? Of course, objects do more. In addition to storing a collection of properties and their values, objects can contain a group of functions designed to bring us more useful features. A function stored in an object is called a method (one of the more confusing names in the programming world, if you ask me). A method is just a function inside a class. More confusingly, when we get onto writing our own classes, methods are defined using the function keyword! Even experienced developers often wrongly use function and method interchangeably. To call a method, we again use arrow notation—$myObject->someMethod():

$myObject = new SomeClass();    // create an object
$myObject->someMethod();        // call a method

Just like standalone functions, methods can take arguments and return values. At this stage, this is probably all sounding a little complicated and pointless, but trust me: pulling together collections of variables (properties) and functions (methods) into little bundles called objects results in much tidier and easier-to-read code for certain tasks—working with a database being just one of them. One day, you may even want to develop custom classes that you can use to create objects of your own devising. For now, however, we’ll stick with the classes that come included with PHP. Let’s keep working with the PDO object we’ve created, and see what we can do by calling one of its methods.

Configuring the Connection

So far, I’ve shown you how to create a PDO object to establish a connection with your MySQL database, and how to display a meaningful error message when something goes wrong:

<?php

try {

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

   ‘ijdbuser’, ‘mypassword’);

$output = ‘Database connection established.’;

} catch (PDOException $e) {

$output = ‘Unable to connect to the database server: ‘ . $e;

}

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

Assuming the connection succeeds, though, you need to configure it before use. You can configure your connection by calling some methods of your new PDO object. Our first task is to configure how our PDO object handles errors. You’ve already learned how to use a try … catch statement to handle any problems PHP mightrun into when connecting to your database. However, by default, PDO switches to a “silent failure” mode after establishing a successful connectionThis “silent failure” mode makes it more difficult for us to find out when something goes wrong and handle it gracefully. Most of the time, we’d just see a blank page with no indication that anything was wrong (other than the information we’re expecting to see on the page not appearing). We’d like our PDO object to throw a PDOException any time it fails to do what we ask. We can configure it do to so by calling the PDO object’s setAttribute method:

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

The two values we’re passing as arguments are constants, just like the ENT_QUOTES constant that you learned to pass to the htmlspecialchars function. Don’t be thrown by the PDO:: at the start of their names; that just indicates that these constants are part of the PDO class that we’re using, rather than constants built into the PHP language itself. Essentially, what we’re saying with this line is that we want to set the PDO attribute that controls the error mode (PDO::ATTR_ERRMODE) to the mode that throws exceptions (PDO::ERRMODE_EXCEPTION).

Next, we need to configure the character encoding of our database connection. You should use UTF-8 encoded text in your websites to maximize the range of characters users have at their disposal when filling in forms on your site. By default, when PHP connects to MySQL, it uses the simpler ISO-8859-1 (or Latin-1) encoding instead of UTF-8. If we left it as is, we wouldn’t easily be able to insert Chinese, Arabic or most non-English characters. Even if you’re 100% sure that your website will only be used by English speakers, there are other problems caused by not setting the character set. If your web page is not set to UTF-8 you’ll run into problems when people write certain characters such as curly quotes into a text box, because they’ll appear in the database as a different character.

Therefore, we now need to set our new PDO object to use the UTF-8 encoding. We can instruct PHP to use UTF-8 when querying the database by appending ;charset=utf8 to the connection string. There are no downsides to doing this, provided your PHP script is also being sent to the browser as utf8 (which is the default in recent PHP versions).

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

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

Other Ways to Set the Charset

If you go searching, you’ll find different ways to set the charset :

$pdo->exec(‘SET NAMES “utf8″‘);

This is because until PHP 5.3.6, the charset option was not correctly applied by PHP. Since this is fixed in any PHP version you’re actually going to be using, setting the charset as part of the connection string is the preferred option.

The complete code we use to connect to MySQL and then configure that connection, therefore, is this:

MySQL-Connect-Complete

<?php

try {

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

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

$pdo->setAttribute(PDO::ATTR_ERRMODE,

PDO::ERRMODE_EXCEPTION);

$output = ‘Database connection established.’;

} catch (PDOException $e) {

$output = ‘Unable to connect to the database server: ‘ . $e;

}

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

Fire up this example in your browser. (If you’ve placed your database code in index.php inside the public directory and the output.html.php file in the templates directory, the URL for the page will be http://192.168.10.10/.) If your server is up and running, and everything is working properly, you should see a message indicating success like this:

A successful connection

If PHP is unable to connect to your MySQL server, or if the username and password you provided are incorrect, you’ll instead see a similar screen to that shown below. To make sure your error-handling code is working properly, you might want to misspell your password intentionally to test it out.

 

A connection failure

Thanks to our catch block, the error message from the database has been included on the page:

catch (PDOException $e) {

$output = ‘Unable to connect to the database server: ‘ . $e;

}

Remember we said that $e is actually an object? Some objects can be converted to a string. That is, if they’re included in an echo statement, the object will provide a string. Not all objects support this behavior (in fact, most don’t!). The PDOException class also has a getMessage() method that contains the error message. If you’d like to be more explicit, you can change the catch block to this:

catch (PDOException $e) {

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

$e->getMessage();

}

There are some other methods—including getFile() and getLine()—for returning the file name and line number that the exception was thrown on. You can generate a very detailed error message like this:

catch (PDOException $e) {

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

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

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

}

This is incredibly useful if you have a large website with dozens of include files. The error message will tell you exactly which file to look in and which line the error occurred on. If you’re curious, try inserting some other mistakes in your database connection code (for example, a misspelled database name) and observe the detailed error messages that result. When you’re done, and your database connection is working correctly, go back to the simple error message. This way your visitors won’t be bombarded with technical gobbledygook if a genuine problem emerges with your database server. With a connection established and a database selected, you’re ready to begin using the data stored in the database.

What Happens After the Script Has Finished?

You might be wondering what happens to the connection with the MySQL server after the script has finished executing. If you really want to, you can force PHP to disconnect from the server by discarding the PDO object that represents your connection. You do this by setting the variable containing the object to null:

$pdo = null; // disconnect from the database server

That said, PHP will automatically close any open database connections when it finishes running your script, so you can usually just let PHP clean up after you.

Sending SQL Queries with PHP

We connected to the MySQL database server using MySQL Workbench, which allowed us to type SQL queries (commands) and view the results of those queries immediately. The PDO object offers a similar mechanism—the exec method:

$pdo->exec($query)

Here, $query is a string containing whatever SQL query you want to execute. As you know, if there’s a problem executing the query (for instance, if you made a typing mistake in your SQL query), this method will throw a PDOException for you to catch. Consider the following example, which attempts to produce the joke table we created before:

MySQL-Create

try {

$pdo = new PDO(‘mysql:host=localhost;dbname=ijdb; charset=utf8’, ‘ijdbuser’, ‘mypassword’); $pdo->setAttribute(PDO::ATTR_ERRMODE,

PDO::ERRMODE_EXCEPTION);

$sql = ‘CREATE TABLE joke (

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

joketext TEXT,

jokedate DATE NOT NULL

) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB’; $pdo->exec($sql);

$output = ‘Joke table successfully created.’;

}

catch (PDOException $e) {

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

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

}

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

Note once again that we use the same try … catch statement technique to handle possible errors produced by the query. It would be possible to use multiple try _catch blocks to display different error messages, one for the connection and one for the query, but this can result in a considerable amount of extra code. Instead, we’ve opted to use the same try statement to contain both the connection and the query. The try … catch block will stop executing code once an error occurs, so if an error occurs during the database connection, the $pdo->exec($run) line will never run, ensuring that if a query is sent to the database a connection must have been established.

This approach gives us a little less control over the error message that is displayed, but saves typing a try … catch statement for each database operation. Later , we’ll break these up into different blocks, but for now, keep all the database operations in the same try block. This example also uses the getMessage method to retrieve a detailed error message from the MySQL server. The following image shows the error that’s displayed when, for example, the joke table already exists:

The CREATE TABLE query fails because the table already exists

For DELETE, INSERT, and UPDATE queries (which serve to modify stored data), the exec method returns the number of table rows (entries) that were affected by the query. Consider the following SQL command, which we used  to set the dates of all jokes that contained the word “chicken”:

MySQL-Update

try {

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

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

$pdo->setAttribute(PDO::ATTR_ERRMODE,

  PDO::ERRMODE_EXCEPTION);

$sql = ‘UPDATE joke SET jokedate=”2012-04-01″

   WHERE joketext LIKE “%programmer%”‘;

$affectedRows = $pdo->exec($sql);

$output = ‘Updated ‘ . $affectedRows .’ rows.’;

}

catch (PDOException $e) {

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

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

}

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

By storing the value returned from the exec method in $affectedRows, we can use the variable in the $output variable for printing in the template. The image below shows the output of this example, assuming there’s only one “programmer” joke in your database.

The number of database records updated is displayed

If you refresh the page to run the same query again, you should see the message change as shown in the following image. It indicates that no rows were updated, since the new date being applied to the jokes is the same as the existing date:

MySQL lets you know when you’re wasting its time

SELECT queries are treated a little differently, as they can retrieve a lot of data, and PHP provides ways to handle that information.

Handling SELECT Result Sets

For most SQL queries, the exec method works just fine. The query does something to your database, and you get the number of affected rows (if any) from the method’s return value. SELECT queries, however, require something a little fancier than exec. You’ll recall that SELECT queries are used to view stored data in the database. Instead of only affecting the database, SELECT queries have results—and we need a method to return them. The query method looks just like exec in that it accepts an SQL query as an argument to be sent to the database server. What it returns, however, is a PDOStatement object, which represents a result set containing a list of all the rows (entries) returned from the query.

<?php

try {

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

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

$pdo->setAttribute(PDO::ATTR_ERRMODE,

PDO::ERRMODE_EXCEPTION);

$sql = ‘SELECT `joketext` FROM `joke`’;

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

} catch (PDOException $e) {

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

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

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

}

Provided that no error was encountered in processing the query, this code will store a result set (in the form of a PDOStatement object) into the variable $result. This result set contains the text of all the jokes stored in the joke table. As there’s no practical limit on the number of jokes in the database, the result set can be quite big. The while loop is a useful control structure when we need to loop but don’t know how many times. We can’t use a for loop because we don’t know how many records the query returned. Indeed, you could use a while loop here to process the rows in the result set one at a time:

while ($row = $result->fetch()) {

process the row

}

The condition for the while loop is probably different from the conditions you’re used to, so let me explain how it works. Consider the condition as a statement all by itself:

$row = $result->fetch();

The fetch method of the PDOStatement object returns the next row in the result set as an array . When there are no more rows in the result set, fetch returns false instead. Now, the above statement assigns a value to the $row variable, but, at the same time, the statement as a whole takes on that same value. This is what lets you use the statement as a condition in the while loop. Since a while loop will keep looping until its condition evaluates to false, this loop will occur as many times as there are rows in the result set, with $row taking on the value of the next row each time the loop executes. All that’s left to figure out is how to retrieve the values out of the $row variable each time the loop runs.

Rows of a result set returned by fetch are represented as associative arrays, with the indices named after the table columns in the result set. If $row is a row in our result set, $row[‘joketext’] is the value in the joketext column of that row. Our goal in this code is to store away the text of all the jokes so that we can display them in a PHP template. The best way to do this is to store each joke as a new item in an array, $jokes:

while ($row = $result->fetch()) {

$jokes[] = $row[‘joketext’];

}

With the jokes pulled out of the database, we can now pass them along to a PHP template jokes.html.phpTo summarize, here’s the code of the controller for this example so far:

<?php

try {

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

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

$pdo->setAttribute(PDO::ATTR_ERRMODE,

PDO::ERRMODE_EXCEPTION);

$sql = ‘SELECT `joketext` FROM `joke`’;

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

while ($row = $result->fetch()) {

   $jokes[] = $row[‘joketext’];

}

} catch (PDOException $e) {

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

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

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

}

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

The $jokes variable is an array that stores a list of jokes. If you wrote out the contents of the array in PHP it would look something like this:

$jokes = [];

$jokes[0] = ‘A programmer was found dead in the shower. The  instructions read: lather, rinse, repeat.’;

$jokes[1] = ‘!false – it\’s funny because it\’s true’;

$jokes[2] = ‘A programmer\’s wife tells him to go to the store and “get a gallon of milk, and if they have eggs, get a dozen.” He returns with 13 gallons of milk.’;

However, the data has been retrieved from the database rather than being typed out manually in the code. You’ll have noticed that there are two different variables being set, $jokes and $error, depending on whether the try block executed successfully. In the jokes.html.php template, we need to display the contents of the $jokes array or the error message contained in the $error variable. To check whether a variable has been assigned a value, we can use the isset function that we used earlier for checking if a form has been submitted. The template can include an if statement to determine whether to display the error or the list of jokes.

if (isset($error)) {

  ?>

  <p>

  <?php

  echo $error;

  ?>

  </p>

}

else {

    : display the jokes

}

There’s nothing new here, but to display the jokes, we need to display the contents of the $jokes array. Unlike other variables we’ve used up to this point, the $jokes array contains more than just a single value. The most common way to process an array in PHP is to use a loop. We’ve already seen while loops and for loops. The foreach loop is particularly helpful for processing arrays:

foreach (array as $item) {

⋮ process each $item

}

Instead of a condition, the parentheses at the top of a foreach loop contain an array, followed by the keyword as, and then the name of a new variable that will be used to store each item of the array in turn. The body of the loop is then executed once for each item in the array. Each time that item is stored in the specified variable, so that the code can access it directly. It’s common to use a foreach loop in a PHP template to display each item of an array in turn. Here’s how this might look for our $jokes array:

<?php

 foreach ($jokes as $joke) {

?>

⋮ HTML code to output each $joke

<?php

}

?>

With this blend of PHP code to describe the loop and HTML code to display it, the code looks rather untidy. Because of this, it’s common to use an alternative way of writing the foreach loop when it’s used in a template:

foreach (array as $item):

⋮ process each $item

endforeach;

The two pieces of code are functionally identical, but the latter looks more friendly when mixed with HTML code. Here’s how this form of the code looks in a template:

<?php foreach ($jokes as $joke): ?>

⋮ HTML code to output each $joke

<?php endforeach; ?>

The same thing can be done with the if statement, making it nicer to look at inside HTML templates by avoiding the braces:

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

<p>

<?php echo $error; ?>

</p>

<?php else: ?>

: display the jokes

<?php endif; ?>

With these new tools in hand, we can write our template to display the list of jokes:

MySQL-ListJokes

<!doctype html>

<html>

<head>

<meta charset=”utf-8″>

<title>List of jokes</title>

</head>

<body>

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

<p>

<?php echo $error; ?>

</p>

<?php else: ?>

<?php foreach ($jokes as $joke): ?>

<blockquote>

<p>

<?php echo htmlspecialchars($joke,

ENT_QUOTES, ‘UTF-8’) ?>

</p>

</blockquote>

<?php endforeach; ?>

<?php endif; ?>

</body>

</html>

Either the $error text is displayed on the page or each joke is displayed in a paragraph (<p>) contained within a block quote (<blockquote>), since we’re effectively quoting the author of each joke in this page. Because jokes might conceivably contain characters that could be interpreted as HTML code (for example, <, >, or &), we must use htmlspecialchars to ensure they’re translated into HTML character entities (that is, &lt;, &gt;, and &amp;) so that they’ll be displayed correctly. The following image shows what this page looks like once you’ve added a couple of jokes to the database.

A list of jokes from the database

Using foreach

Remember how we used a while loop in our controller to fetch the rows out of the PDOStatement result set one at a time?

while ($row = $result->fetch()) {

$jokes[] = $row[‘joketext’];

}

It turns out PDOStatement objects are designed to behave just like arrays when you pass them to a foreach loop. You can therefore slightly simplify your database processing code using a foreach loop instead of a while loop:

foreach ($result as $row) {

$jokes[] = $row[‘joketext’];

}

Another neat tool PHP offers is a shorthand way to call the echo command—which, as you’ve already seen, we need to use frequently. Our echo statements look like this:

<?php echo $variable; ?>

Instead, you can use this:

<?=$variable?>

This does exactly the same thing. <?= means echo and gives you a slightly shorter way to print variables. There’s a limitation to this, though: if you use <?=, you can only print; you can’t include if statements, for statements, etc., although you can use concatenation, and it can be followed by a function call. Here’s an updated template using the shorthand echo:

MySQL-ListJokes-Shorthand

<!doctype html>

<html>

<head>

<meta charset=”utf-8″>

<title>List of jokes</title>

</head>

<body>

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

<p>

<?=$error?>

</p>

<?php else: ?>

<?php foreach ($jokes as $joke): ?>

<blockquote>

<p>

<?=htmlspecialchars($joke, ENT_QUOTES, ‘UTF-8’)?> </p>

</blockquote>

<?php endforeach; ?>

<?php endif; ?>

</body>

</html>

I’ll be using the shorthand notation when it’s applicable from this point on.

Using the Shorthand

In versions of PHP prior to 5.4, this shorthand notation required a fairly uncommon PHP setting to be enabled, so it was discouraged for compatibility reasons. Using the shorthand notation may have caused your code to stop working when moving from a server that had it enabled to one that didn’t. As of PHP 5.4 (so any version you’re realistically going to encounter these days), the shorthand echo works regardless of PHP settings, so you can safely use it without worrying that it might not work on all servers.

Thinking Ahead

In the example we just looked at, we created a template, jokes.html.php, which contains all the HTML required to display the page. However, as our website grows, we’ll add more pages. We’ll certainly want a page for people to be able to add jokes to the website, and we’ll also need a home page with some introductory text, a page with the owner’s contact details, and, as the site grows, perhaps even a page where people can log in to the website.

We are jumping ahead a quite a bit here, but it’s always worth considering how a project will grow. If we apply the approach we just used for jokes.html.php to the rest of the templates—addjoke.html.php, home.html.php, contact.html.php, login.html.php and so on—we’ll end up with a lot of repeated code. Every template will look something like this:

<!doctype html>
<html>
    <head>
        <meta charset="utf-8">
        <title>IJDB - Internet Joke Database</title>
        </head>
    <body>
        <?php if (isset($error)): ?>
        <p>
           <?=$error?>
        </p>
        <?php else: ?>
            : do whatever is required for this page: show text,
            : show a form, list records from the database, etc.
        <?php endif; ?>
    </body>

</html>

As a programmer, repeating code is one of the worst things you can do. In fact, programmers often refer to the DRY principle, which stands for “Don’t repeat yourself”. If you find yourself repeating sections of code, there is almost certainly a better solution. All the best programmers are lazy, and repeating code means repeating work. Using this copy/paste approach for templates makes the website very difficult to maintain. Let’s imagine there’s a footer and a navigation section that we want to appear on each page. Our templates would now look like this:

<!doctype html>
<html>
   <head>
        <meta charset="utf-8">
        <title>IJDB - Internet Joke Database</title>
    </head>
    <body>

<nav> <ul>

<li><a href="index.php">Home</a></li>

<li><a href="jokes.php">Jokes List</a></li>

</ul> </nav>
       <main>
            <?php if (isset($error)): ?>
            <p>
               <?=$error?>
            </p>
           <?php else: ?>
                : do whatever is required for this page: show text,
                : show a form, list jokes, etc.
           <?php endif; ?>
        </main>
       <footer>
            &copy; IJDB 2017
       </footer>
    </body>

We’ll run into a problem in 2018! If the templates for all the pages on the website—for example, jokes.html.php addjoke.html.php, home.html.php, contact.html.php andlogin.html.php—contain code in the structure above, to update the year in the copyright notice to “2018” you’d need to open each of the templates and change the date. We could be clever and have the date dynamically read from the server’s clock (echo date(‘Y’); if you’re curious!) to avoid this issue, but what if we wanted to add a <script> tag that was included on every page? Or add a new link to the menu? We’d still need to open every template file and change it!

Changing five or six templates may be slightly annoying, but it’s not going to pose much of a problem. However, what if the website grows to dozens or hundreds of pages? Each time you wanted to add a link to the menu you’d have to open every single template and change it. This problem could be solved with a series of include statements. For example:

<!doctype html>
<html>
   <head>
        <meta charset="utf-8">
        <title>IJDB - Internet Joke Database</title>
   </head>
    <body>
        <nav>
            <?php include 'nav.html.php'; ?>

</nav>
       <main>
            <?php if (isset($error)): ?>
            <p>
              <?=$error?>
            </p>
           <?php else: ?>

: do whatever is required for this page: show text,
                : show a form, list jokes, etc.
            <?php endif; ?>

</main>
        <footer>
            <?php include 'footer.html.php'; ?>
       </footer>
    </body>

</html>

But this method requires clairvoyance: we need to anticipate exactly what changes might need to be made in the future and use relevant include statements in the places we foresee changes will happen. In the example above, for example, it’s easy to add new menu entries by adding them to nav.html.php, but adding a <script> tag to every page, or even something as trivial as adding a CSS class to the nav element, still means opening every template to make the change. There’s no way to accurately predict all the changes that might be needed over the lifetime of the website, so instead the approach we showed you at the beginning of this chapter is actually better:

<!doctype html>

<html>

<head>

<meta charset=”utf-8″>

<link rel=”stylesheet” href=”jokes.css”> <title><?=$title?></title>

</head>

<body>

<nav>

<header>

<h1>Internet Joke Database</h1>

</header>

<ul>

<li><a href=”index.php”>Home</a></li>

<li><a href=”jokes.php”>Jokes List</a></li>

<li><a href=”addjoke.php”>Add a new Joke</a></li>

</ul>

</nav>

<main>

<?=$output?>

</main>

<footer>

&copy; IJDB 2017

</footer>

</body>

</html>

If we always include this template, which we’ll call layout.html.php, it’s possible to set the $output variable to some HTML code and have it appear on the page with the navigation and footer. We’ve also snuck in a $title variable so each controller can define a value that appears between the <title> and </title> tag along with some CSS (available as jokes.css in the sample code) to make the page a little prettier.

IJDB – Now with CSS styles

Any controller can now use include __DIR__ . ‘/../templates/ layout.html.php’; and provide values for $output and $titleOur jokes.php using layout.html.php looks like this:

MySQL-ListJokes-Layout-1

<?php
try {
    $pdo = new PDO('mysql:host=localhost;dbname=ijdb;
    charset=utf8', 'ijdbuser', 'mypassword');
    $pdo->setAttribute(PDO::ATTR_ERRMODE,
      PDO::ERRMODE_EXCEPTION);
    $sql = 'SELECT `joketext` FROM `joke`';
    $result = $pdo->query($sql);
    while ($row = $result->fetch()) {
        $jokes[] = $row['joketext'];
    }
    $title = 'Joke list';
    $output = '';
   foreach ($jokes as $joke) {
        $output .= '<blockquote>';
        $output .= '<p>';
        $output .= $joke;
       $output .= '</p>';
     $output .= '</blockquote>';
    }
} catch (PDOException $e) {
    $title = 'An error has occurred';
    $output = 'Database error: ' . $e->getMessage() . '
     in ' .$e->getFile() . ':' . $e->getLine();

include  __DIR__ . '/../templates/layout.html.php';

But wait! What’s going on with $output in the try block? The $output variable actually contains some HTML code: the loop builds a string containing the HTML code for the jokes list. In principle, this is what we want to happen: the $output variable contains the HTML code that’s going to be inserted between the navigation and the footer in layout.html.php, but the code is incredibly ugly. We already showed you how to avoid mixing HTML and PHP code via the include statement. Like we did earlier, it would be good to move the HTML for displaying the jokes to its own file, but this time, only the HTML code that is unique to the joke list page. jokes.html.php in the templates directory should contain this code:

<?php foreach ($jokes as $joke): ?>

<blockquote>

<p>

<?=htmlspecialchars($joke, ENT_QUOTES, ‘UTF-8’)?> </p>

</blockquote>

<?php endforeach; ?>

Importantly, this is only the code for displaying the jokes. It doesn’t contain the navigation, footer, <head> tag or anything we want repeated on every page; it’s only the HTML code that’s unique to the joke list page. To use this template, you might try the following:

while ($row = $result->fetch()) {

       $jokes[] = $row[‘joketext’];

 }

$title = ‘Joke list’;

include ‘jokes.html.php’;

}

Or if you’re very clever:

while ($row = $result->fetch()) {

$jokes[] = $row[‘joketext’];

}

$title = ‘Joke list’;

$output = include ‘jokes.html.php’;

}

With this approach, your logic would be entirely sound. We need to include the jokes.html.php. Unfortunately, the include statement just executes the code from the included file at the point it’s called. If you run the code above, the output will actually be something like this:

<blockquote>
    <p>

A programmer was found dead in the shower. The instructions read: lather, rinse, repeat.
    </p>
</blockquote>
<blockquote>
    <p>
   !false - it's funny because it's true
   </p>
</blockquote>
<!doctype html>
<html>
   <head>
        <meta charset="utf-8">
        <title>Joke List</title>
   </head>
    <body>
    ...

Because jokes.html.php is included first, it’s sent to the browser first. What we need to do is load jokes.html.php, but instead of sending the output straight to the browser, we need to capture it and store it in the $output variable so that it can be used later by layout.html.phpThe include statement doesn’t return a value, so $output = include ‘jokes.html.php’; does not have the desired effect, and PHP doesn’t have an alternative statement to do that. However, that doesn’t mean that it’s not possible. PHP does have a useful feature called “output buffering”. It might sound complicated, but the concept is actually very simple: when you use echo to print something, or include to include a file that contains HTML, usually it’s sent directly to the browser. By making use of output buffering, instead of having the output being sent straight to the browser, the HTML code is stored on the server in a “buffer”, which is basically just a string containing everything that’s been printed so far. Even better, PHP lets you turn on the buffer and read its contents at any time. There are two functions we need:

  • ob_start(), which starts the output buffer. After calling this function, anything printed via echo or HTML printed via include will be stored in a buffer rather than sent to the browser.
  •  ob_get_clean(), which returns the contents of the buffer and clears it.

As you’ve probably guessed, “ob” in the function names stands for “output buffer”. To capture the contents of an included file, we just need to make use of these two functions:

while ($row = $result->fetch()) {
    $jokes[] = $row['joketext'];
}
$title = 'Joke list';
// Start the buffer
ob_start();

Publishing MySQL Data on the Web 159

// Include the template. The PHP code will be executed,
// but the resulting HTML will be stored in the buffer
// rather than sent to the browser.
include  __DIR__ . '/../templates/jokes.html.php';
// Read the contents of the output buffer and store them
// in the $output variable for use in layout.html.php
$output = ob_get_clean();
}

When this code runs, the $output variable will contain the HTML that was generated in the jokes.html.php template. We’ll use this approach from now on. Each page will be made up of two templates:

  • layout.html.php, which contains all of the common HTML needed by every page
  • a unique template that contains only the HTML code that’s unique to that particular page

The complete jokes.php looks like this:

try {
    $pdo = new PDO('mysql:host=localhost;dbname=ijdb;
    charset=utf8', 'ijdbuser', 'mypassword');
    $pdo->setAttribute(PDO::ATTR_ERRMODE,
       PDO::ERRMODE_EXCEPTION);
   $sql = 'SELECT `joketext` FROM `joke`';
    $result = $pdo->query($sql);
    while ($row = $result->fetch()) {
        $jokes[] = $row['joketext'];

}

$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';

Let’s make the “Home” link work by adding an index.php. We could put anything on this page: the latest jokes, the best joke of the month or whatever we like. For now, though, we’ll keep it simple and just have a message that says “Welcome to the Internet Joke Database”. Create a file called home.html.php in the templates folder:

<h2>Internet Joke Database</h2>

<p>Welcome to the Internet Joke Database</p>

Our index.php is considerably simpler than jokes.html.php. It doesn’t get any information from the database, so it doesn’t need a database connection and we don’t need a try … catch statement, so we’ll just load the two templates and set the $title and $output variables: MySQL-ListJokes-Layout-3

<?php

$title = ‘Internet Joke Database’;

ob_start();

include __DIR__ . ‘/../templates/home.html.php’; $output = ob_get_clean();

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

Only Connect to the Database Where Necessary

It’s good practice to only connect to the database if you need to. Databases are the most common performance bottleneck on most websites, so making as few connections as possible is preferred.

Test that both pages work in your browser. You should have a list of jokes visible when you visit http://192.168.10.10/jokes.php and the welcome message on http://192.168.10.10. Both pages should contain the navigation and the footer. Try amending layout.html.php. The changes you make will appear on both pages. If the site had dozens of pages, changes to the layout would affect every page.

Inserting Data into the Database

In this section, we’ll demonstrate how to use the tools at your disposal to enable site visitors to add their own jokes to the database. If you want to let your site visitors enter new jokes, you’ll obviously need a form. Here’s a template for a form that will fit the bill:

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

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

</label>

<textarea id=”joketext” name=”joketext”

         rows=”3″ cols=”40″>

</textarea>

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

</form>

Save this as addjoke.html.php in the templates directory. The most important part of the <form> element is the action attribute. The action attribute tells the browser where to send the data once the form is submitted. This can be the name of a file, such as “addjoke.php”However, if you leave the attribute empty by setting it to “”, the data provided by the user will be sent back to the page you’re currently viewing. If the browser’s URL shows the page as addjoke.php, that’s were the data will be sent when the user presses the submit button. Let’s tie this form into the preceding example, which displayed the list of jokes in the database. Open up layout.html.php and add a link to Add a new Joke which goes to addjoke.php:

<!doctype html>
<html>
    <head>
        <meta charset="utf-8">
        <link rel="stylesheet" href="jokes.css">
        <title><?=$title?></title>
   </head>
    <body>
        <nav>
            <header>
                <h1>Internet Joke Database</h1>
            </header>

<ul> <li><a href="index.php">Home</a></li>

<li><a href="jokes.php">Jokes List</a></li>

<li><a href="addjoke.php">Add a new Joke</a></li>

</ul> </nav>
       <main>
            <?=$output?>

</main>
       <footer>
            &copy; IJDB 2017
       </footer>
    </body>

</html>

While you have layout.html.php open, include the form.css stylesheet  as we have above. Now, any form displayed inside the layout will have the styles we used before. When this form is submitted, the request will include a variable, joketext, that contains the text of the joke as typed into the text area. This variable will then appear in the $_POST array created by PHP. Let’s create addjoke.php in the public directory. The basic logic for this controller is:

  • If no joketext POST variable is set, display a form.
  • Otherwise, insert the supplied joke into the database.

Create this skeleton addjoke.php:

<?php
if (isset($_POST[‘joketext’])) {
try {
$pdo = new PDO(‘mysql:host=localhost;dbname=ijdb;
charset=utf8’, ‘ijdbuser’, ‘mypassword’);
$pdo->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
$title = ‘An error has occurred’;
$output = ‘Database error: ‘ . $e->getMessage() . ‘ in ‘
. $e->getFile() . ‘:’ . $e->getLine();
}
} else {
$title = ‘Add a new joke’;

ob_start();
include __DIR__ . ‘/../templates/addjoke.html.php’;
$output = ob_get_clean();
}
include __DIR__ . ‘/../templates/layout.html.php’;

This opening if statement checks if the $_POST array contains a variable called joketext. If it’s set, the form has been submitted; otherwise, the form from addjoke.html.php is loaded into the $output variable for displaying in the browser. If you do open addjoke.php in your browser at this point, you’ll see the form, but typing in a joke and pressing submit won’t work, because we haven’t yet done anything with the data contained in $_POST[‘joketext’]To insert the submitted joke into the database, we must execute an INSERT query using the value stored in $_POST[‘joketext’] to fill in the joketext column of the joke table. This might lead you to write some code like this:

$sql = ‘INSERT INTO `joke` SET

    `joketext` =”‘ . $_POST[‘joketext’] . ‘”,

    `jokedate` =”2017-02-04″‘;

$pdo->exec($sql);

There is a serious problem with this code, however: the contents of $_POST[‘joketext’] are entirely under the control of the user who submitted the form. If a malicious user were to type some nasty SQL code into the form, this script would feed it to your MySQL server without question. This type of attack is called an SQL injection attack, and in the early days of PHP it was one of the most common security holes that hackers found and exploited in PHP-based websites. If the user types How many programmers does it take to screw in a lightbulb? None, it’s a hardware problem. into the text box, the query sent to the database will be:

INSERT INTO `joke` SET

`joketext` =”How many programmers does it take to screw in a lightbulb? None, it’s a hardware problem.”,

`jokedate` =”2017-02-04

But what if the user types in the joke: A programmer’s wife tells him to go to the store and “get a gallon of milk, and if they have eggs, get a dozen.” He returns with 13 gallons of milk. In this case, the query sent to the database will be:

INSERT INTO `joke` SET
    `joketext`="A programmer's wife tells him to go to the store
    and "get a gallon of milk, and if they have eggs, get
    a dozen."
    He returns with 13 gallons of milk.",
    `jokedate`="2017-02-04

Because the joke contains a quote character, MySQL will return an error, as it will see the quote before get as the end of the string. To make this a valid query, we need to escape all quotes in the text so that the query sent to the database becomes:

INSERT INTO `joke` SET
    `joketext`="A programmer's wife tells him to go to the store

and \"get a gallon of milk, and if they have eggs, get a dozen.\"
     He returns with 13 gallons of milk.",
    `jokedate`="2017-02-04

Data not being inserted if it contains a quote is an annoying problem for users. They’ll lose whatever they typed in. But malicious users are able to abuse this. In older versions of PHP, it was possible to run multiple queries from PHP by separating them with a semicolon (;). Imagine if the user typed this into the box:

“; DELETE FROM `joke`; —

This would send the following queries to the database:

INSERT INTO `joke` SET

`joketext`=””;

DELETE FROM `joke`;

–`jokedate`=”2017-02-04

— is a single line comment in MySQL, so the last line would be ignored, and the INSERT query would run, followed by the DELETE query the user had typed into the box. In fact, the user could type any query they like into the box and it will be run on the database! In the early days of PHP, these attacks were so feared that the team behind PHP added some built-in protections against SQL injections to the language. Firstly, they disabled the ability to send multiple queries at once. Secondly, they added something called magic quotes. This protective feature of PHP automatically analyzed all values submitted by the browser and inserted backslashes (\) in front of any “dangerous” characters like apostrophes—which can cause problems if they’re included in an SQL query inadvertently. The problem with the magic quotes feature is that it causes as many problems as it prevents. First of all, the characters that it detects and the method it uses to sanitize them (prefixing them with a backslash) are only valid in some circumstances. Depending on the character encoding of your site and the database server you’re using, these measures may be completely ineffective.

Second, when a submitted value is used for some purpose other than creating an SQL query, those backslashes can be really bothersome.In short, the magic quotes feature was a bad idea, so much so that it’s been removed from PHP since version 5.4. However, due to PHP’s age and the amount of code out there, you might come across some references to it, so it’s worth having a basic understanding of what it was supposed to do. Once magic quotes was identified as a bad idea, the advice from PHP developers was to turn it off. However, this meant that there were some web servers with it turned off and others with it turned on. This was a headache for developers: they either had to instruct everyone who was ever going to use their code to turn it off—which wasn’t possible on some shared servers—or write extra code to account for it. Most developers chose the latter, and you may come across some code like this:

if (get_magic_quotes_gpc()) {

// code here

}

If you see an if statement like this in legacy code you’ve been given to work with, you can safely delete the entire block, as no code inside the if statement will ever be executed on recent PHP versions. If you do see code like this, it means the original developer understood the problems with magic quotes and was doing their best to prevent it. As of PHP 5.4 (which you should never come across, as it’s no longer supported) get_magic_quotes_gpc() will always return false and the code will never be executed.

All you really need to know about magic quotes is that it was a bad solution to the problem at hand. Of course, without magic quotes, you need to find a different solution to the problem. Luckily, the PDO class can do all the hard work for you using something called prepared statementsA prepared statement is a special kind of SQL query that you’ve sent to your database server ahead of time, giving the server a chance to prepare it for execution—but not actually execute it. Think of it like writing a .php script. The code is there, but doesn’t actually get run until you visit the page in your web browser. The SQL code in prepared statements can contain placeholders that you’ll supply the values for later, when the query is to be executed. When filling in these placeholders, PDO is smart enough to guard against “dangerous” characters automatically. Here’s how to prepare an INSERT query and then execute it safely with $_POST[‘joketext’] as the text of the joke:

$sql = ‘INSERT INTO `joke` SET

     `joketext` = :joketext,

    `jokedate` = “today’s date”‘;

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

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

$stmt->execute();

Let’s break this down one statement at a time. First, we write our SQL query as a PHP string and store it in a variable ($sql) as usual. What’s unusual about this INSERT query, however, is that no value is specified for the joketext column. Instead, it contains a placeholder for this value (:joketext). Don’t worry about the jokedate field just now—we’ll circle back to it in a moment. Next, we call the prepare method of our PDO object ($pdo), passing it our SQL query as an argument. This sends the query to the MySQL server, asking it to prepare to run the query. MySQL can’t run it yet—there’s no value for the joketext column. The prepare method returns a PDOStatement object (yes, the same kind of object that gives us the results from a SELECT query), which we store in $stmt.

Now that MySQL has prepared our statement for execution, we can send it the missing value(s) by calling the bindValue method of our PDOStatement object ($stmt). We call this method once for each value to be supplied (in this case, we only need to supply one value—the joke text), passing as arguments the placeholder that we want to fill in (‘:joketext’) and the value we want to fill it with ($_POST[‘joketext’]). Because MySQL knows we’re sending it a discrete value, rather than SQL code that needs to be parsed, there’s no risk of characters in the value being interpreted as SQL code. Using prepared statements, SQL injection vulnerabilities simply aren’t possible! Finally, we call the PDOStatement object’s execute method to tell MySQL to execute the query with the value(s) we’ve supplied.

One interesting thing you’ll notice about this code is that we never placed quotes around the joke text. :joketext exists inside the query without any quotes, and when we called bindValue we passed it the plain joke text from the $_POST array. When using prepared statements, you don’t need quotes because the database (in our case, MySQL) is smart enough to know that the text is a string and it will be treated as such when the query is executed. The lingering question in this code is how to assign today’s date to the jokedate field. We could write some fancy PHP code to generate today’s date in the YYYY-MM-DD format that MySQL requires, but it turns out that MySQL itself has a function to do this: CURDATE:

$sql = ‘INSERT INTO `joke` SET

   `joketext` = :joketext,

   `jokedate` = CURDATE()’;

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

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

$stmt->execute();

The MySQL CURDATE function is used here to assign the current date as the value of the jokedate column. MySQL actually has dozens of these functions, but I’ll introduce them only as required. Now that we have our query, we can complete the if statement we started earlier to handle submissions of the “Add Joke” form:

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

try {

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

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

$pdo->setAttribute(PDO::ATTR_ERRMODE,

         PDO::ERRMODE_EXCEPTION);

$sql = ‘INSERT INTO `joke` SET

      `joketext` = :joketext,

     `jokedate` = CURDATE()’;

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

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

$stmt->execute();

}

catch (PDOException $e) {

$title = ‘An error has occurred’;

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

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

      }

}

But wait! This if statement has one more trick up its sleeve. Once we’ve added the new joke to the database, instead of displaying the PHP template as previously, we want to redirect the user’s browser back to the list of jokes. That way they’re able to see the newly added joke among them. That’s what the two lines at the end of the if statement above do. In order to achieve the desired result, your first instinct might be to allow the controller to simply fetch the list of jokes from the database after adding the new joke and displaying the list using the jokes.html.php template as usual. The problem with doing this is that the list of jokes, from the browser’s perspective, would be the result of having submitted the “Add Joke” form. If the user were then to refresh the page, the browser would resubmit that form, causing another copy of the new joke to be added to the database! This is rarely the desired behavior.

Instead, we want the browser to treat the updated list of jokes as a normal web page that’s able to be reloaded without resubmitting the form. The way to do this is to answer the browser’s form submission with an HTTP redirect4—a special response that tells the browser to navigate to a different page. The PHP header function provides the means of sending special server responses like this one, by letting you insert specific headers into the response sent to the browser. In order to signal a redirect, you must send a Location header with the URL of the page to which you wish to direct the browser:

header(‘Location: URL’);

In this case, we want to send the browser to jokes.php. Here are the two lines that redirect the browser back to our controller after adding the new joke to the database:

header(‘Location: jokes.php’);

Here’s the complete code of the addjoke.php controller:

MySQL-AddJoke

<?php if (isset($_POST[‘joketext’])) { try { $pdo = new PDO(‘mysql:host=localhost;dbname=ijdb; charset=utf8’, ‘ijdbuser’, ‘mypassword’); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $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(); } } else { $title = ‘Add a new joke’; ob_start(); include __DIR__ . ‘/../templates/addjoke.html.php’; $output = ob_get_clean(); } include __DIR__ . ‘/../templates/layout.html.php’;

As you review this to ensure it all makes sense to you, note that the code that connects to the database by creating a new PDO object must come before any of the code that runs database queries. But a database connection isn’t required for displaying the “Add Joke” form. The connection is only made when the form has been submitted. Load this up and add a new joke or two to the database via your browser. There you have it: you’re able to view existing jokes in—and add new jokes to—your MySQL database.

Deleting Data from the Database

In this section, we’ll make one final enhancement to our joke database site. Next to each joke on the jokes page (jokes.php), we’ll place a button labeled Delete. When clicked, it will remove that joke from the database and display the updated joke list. If you like a challenge, you might want to take a stab at writing this feature yourself before you read on to see my solution. Although we’re implementing a brand new feature, we’ll mainly be using the same tools as employed in the previous examples in this chapter. Here are a few hints to start you off:

  • You’ll need a new controller (deletejoke.php).
  • The SQL DELETE command will be required.
  • To delete a particular joke in your controller, you’ll need to identify it uniquely. The id column in the joke table was created to serve this purpose. You’re going to have to pass the ID of the joke to be deleted with the request to delete a joke. The easiest way to do this is to use a hidden form field.

At the very least, take a few moments to think about how you’d approach this. When you’re ready to see the solution, read on! To begin with, we need to modify the SELECT query that fetches the list of jokes from the database. In addition to the joketext column, we must also fetch the id column so that we can identify each joke uniquely:

try {
    $pdo = new PDO('mysql:host=localhost;dbname=ijdb;
    charset=utf8',  'ijdbuser', 'mypassword');
    $pdo->setAttribute(PDO::ATTR_ERRMODE,

        PDO::ERRMODE_EXCEPTION);

    $sql = 'SELECT `id`, `joketext` FROM `joke`';
    $result = $pdo->query($sql);
    // ...

We also have to modify the while loop that stores the database results into the $jokes array. Instead of simply storing the text of each joke as an item in the array, we store both the ID and text of each joke. One way to do this is to make each item in the $jokes array an array in its own right:

while ($row = $result->fetch()) {

$jokes[] = [‘id’ => $row[‘id’], ‘joketext’ => $row[‘joketext’]];

}

Using a foreach Loop Instead

If you’ve already switched to using a foreach loop to process your database result rows, that will work just fine too:

foreach ($result as $row) {

$jokes[] = array(‘id’ => $row[‘id’], ‘joketext’ => $row[‘joketext’]);

}

Once this loop runs its course, we’ll have the $jokes array, each item of which is an associative array with two items: the ID of the joke and its text. For each joke ($jokes[n]), we can therefore retrieve its ID ($jokes[n][‘id’]) and its text ($jokes[n][‘text’]). Our next step is to update the jokes.html.php template to retrieve each joke’s text from this new array structure, as well as provide a Delete button for each joke:

<?php foreach ($jokes as $joke): ?>
<blockquote>
  <p>
    <?=htmlspecialchars($joke['joketext'],
        ENT_QUOTES, 'UTF-8')?>
    <form action="deletejoke.php" method="post">

<input type="hidden" name="id"  value="<?=$joke['id']?>">
       <input type="submit" value="Delete">
    </form>
    </p>
</blockquote>
><?php endforeach; ?>

Here are the highlights of this updated code:

  • Each joke will be displayed with a form, which, if submitted, will delete that joke. We signal this to a new controller, deletejoke.php, using the form’s action attribute.
  • Since each joke in the $jokes array is now represented by a two-item array instead of a simple string, we must update this line to retrieve the text of the joke. We do this using $joke[‘text’] instead of just $joke.
  • When we submit the form to delete this joke, we send along the ID of the joke to be deleted. To do this, we need a form field containing the joke’s ID, but we’d prefer to keep this field hidden from the user; that’s why we use a hidden form field (input type=”hidden”). The name of this field is id, and its value is the ID of the joke to be deleted ($joke[‘id’]).

Unlike the text of the joke, the ID is not a user-submitted value, so there’s no need to worry about making it HTML-safe with htmlspecialchars. We can rest assured it will be a number, since it’s automatically generated by MySQL for the id column when the joke is added to the database.

  • The submit button (input type=”submit”) submits the form when clicked. Its value attribute gives it a label of Delete.
  • Finally, we close the form for this joke.

Why Aren’t the Form and Input Tags Outside the Blockquote?

If you know your HTML, you’re probably thinking the form and input tags belong outside of the blockquote element, since they aren’t a part of the quoted text (the joke). Strictly speaking, that’s true: the form and its inputs should really be either before or after the blockquote. Unfortunately, making that tag structure display clearly requires a little CSS code. If you plan to use this code in the real world, you should invest some time into learning CSS (or at least secure the services of a CSS guru). That way, you can take complete control of your HTML markup without worrying about the CSS required to make it look nice. 

Add the following CSS to jokes.css to make the buttons appear to the right of the jokes and draw a line between them:

blockquote {display: table; margin-bottom: 1em;

        border-bottom: 1px solid #ccc; padding: 0.5em;}

blockquote p {display: table-cell; width: 90%; vertical-align: top;}

blockquote form {display: table-cell; width: 10%;}

The following image shows what the joke list looks like with the Delete buttons added:

Each button can delete its respective joke

But wait! Before we move on to making the Delete button work, let’s briefly step back and take a careful look at this line:

$jokes[] = [‘id’ => $row[‘id’], ‘joketext’ => $row[‘joketext’]];

Here we’re looping over the PDOStatement object, which gives us a $row variable containing the keys id and joketext along with corresponding values, and we’re using that to build another array with the same keys and values. You may have already realized this is terribly inefficient. We could achieve the same thing using this code:

while ($row = $result->fetch()) {

$jokes[] = $row;

}

But as we know, this can also be achieved with a foreach loop:

foreach ($result as $row) {

$jokes[] = $row;

}

In this instance, we’re using foreach to iterate over the records from the database and build an array. We’re then looping over the array with another foreach loop in the template. We could just write:

$jokes = $result;

Now, when $jokes is iterated over in the template, it’s not an array but a PDOStatement object. However, that has no effect on the output and saves us some code. In fact, we can omit the $result variable altogether and load the PDOStatement object directly into the $jokes variable. The complete jokes.php controller now looks like this:

try {
    $pdo = new PDO('mysql:host=localhost;dbname=ijdb;
    charset=utf8', 'ijdbuser', 'mypassword');
    $pdo->setAttribute(PDO::ATTR_ERRMODE,
    PDO::ERRMODE_EXCEPTION);
    $sql = 'SELECT `joketext`, `id` FROM joke';
    $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';

Now we don’t even have a while loop iterating over the records in the controller, but just iterate over the records directly in the template, saving some code and making the page execute slightly faster, as it now only loops over the records once. Back to our new Delete button: all that remains to make this new feature work is to add a relevant deletejoke.php to issue a DELETE query to the database:

try {
 $pdo = new PDO('mysql:host=localhost;dbname=ijdb;charset=utf8', 'ijdbuser', 'mypassword');

$pdo->setAttribute(PDO::ATTR_ERRMODE,

PDO::ERRMODE_EXCEPTION);
   $sql = 'DELETE FROM `joke` WHERE `id` = :id';
    $stmt = $pdo->prepare($sql);
   $stmt->bindValue(':id', $_POST['id']);
    $stmt->execute();
   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';

The complete code for the updated jokes.php and deletejoke.php is available as MySQL-DeleteJokeThis chunk of code works exactly like the one we added to process the “Add Joke” code earlier . We start by preparing a DELETE query with a placeholder for the joke ID that we wish to delete. We then bind the submitted value of $_POST[‘id’] to that placeholder and execute the query. Once that query is achieved, we use the PHP header function to ask the browser to send a new request to view the updated list of jokes.

Don’t Use Hyperlinks to Perform Actions

If you tackled this example yourself, your first instinct might have been to provide a Delete hyperlink for each joke, instead of going to the trouble of writing an entire HTML form containing a Delete button for each joke on the page. Indeed, the code for such a link would be much simpler:

<?php foreach ($jokes as $joke): ?>

<blockquote>

<p>

<?=htmlspecialchars($joke[‘joketext’], ENT_QUOTES, ‘UTF-8’)?>

<a href=”deletejoke.php&amp;

id=<?=$joke[‘id’]?>”>Delete</a>

</p>

</blockquote>

<?php endforeach; ?>

In short, hyperlinks should never be used to perform actions (such as deleting a joke); they must only be used to provide a link to some related content. The same goes for forms with method=”get”, which should only be used to perform queries of existing data. Actions must only ever be performed as a result of a form with method=”post” being submitted. The reason why is that forms with method=”post” are treated differently by browsers and related software. If you were to submit a form with method=”post” and then click the Refresh button in your browser, for example, the browser would ask if you’re certain you wish to resubmit the form. Browsers have no similar protection against resubmission when it comes to links and forms with method=”get”Similarly, web accelerator software (and some modern browsers) will automatically follow hyperlinks present on a page in the background, so that the target pages will be available for immediate display if the user clicks one of those links. Search engines will also follow all the links on your site in order to work out when to show your site’s pages in search results. If your site deleted a joke as a result of a hyperlink being followed, you could find your jokes being deleted automatically.

Mission Accomplished

You learned all about PHP Data Objects (PDO), a collection of built-in PHP classes (PDO, PDOException, and PDOStatement) that allow you to interface with a MySQL database server by creating objects and then calling the methods they provide. While you were at it, you also picked up the basics of object-oriented programming (OOP), no mean feat for a PHP beginner! Using PDO objects, you built your first database-driven website, which published the ijdb database online and allowed visitors to add and delete jokes. In a way, you could say this we achieved the stated mission: to teach you how to build a database-driven website. Of course, the example in this chapter contained only the bare essentials.  We will return to the SQL Query window in MySQL Workbench. We’ll learn how to use relational database principles and advanced SQL queries to represent more complex types of information, and give our visitors credit for the jokes they add!

Relational Database Design

We’ve worked with a very simple database of jokes, composed of a single table named (appropriately enough) joke. While this database has served us well as an introduction to MySQL databases, there’s more to relational database design than can be understood from this simple example.We’ll expand on this database and learn a few new features of MySQL, in an effort to realize and appreciate the real power that relational databases have to offer.nBe forewarned that we’ll cover several topics only in an informal, non-rigorous sort of way. As any computer science major will tell you, database design is a serious area of research, with tested and mathematically provable principles that, while useful, are beyond the scope of this text.

Giving Credit Where Credit Is Due

To start off, let’s recall the structure of our joke table. It contains three columns: id, joketext, and jokedate. Together, these columns allow us to identify jokes (id), and keep track of their text (joketext) and the date they were entered (jokedate). For your reference, here’s the SQL code that creates this table and inserts a couple of entries:

# Code to create a simple joke table

CREATE TABLE `joke` (

`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

`joketext` TEXT,

`jokedate` DATE NOT NULL

) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

# Adding jokes to the table

INSERT INTO `joke` SET

`joketext` = ‘Why was the empty array stuck outside? It didn\’t have any keys’,

 `jokedate` = ‘2017-04-01’;

INSERT INTO `joke`

(`joketext`, `jokedate`) VALUES (

‘!false – It\’s funny because it\’s true’,

“2017-04-01”

);

Now, let’s say we wanted to track another piece of information about our jokes: the names of the people who submitted them. It would be natural to add a new column to our joke table for this. The SQL ALTER TABLE command (which we’ve yet to see) lets us do exactly that. As we demonstrated earlier, you can either type out these queries yourself or have a tool such as MySQL Workbench do it for you. When you use the GUI of MySQL Workbench to interact with the database, it generates the queries for you. It even shows them to you before applying them to the database. If you’re not quite sure how to write a query, you can always get MySQL Workbench to generate it for you and then amend it to suit your needs.

SQL Queries fall into two categories:

  • Data definition language (DDL) queries. These are the queries that describe how the data will be stored. These are the CREATE TABLE and CREATE DATABASE queries that we showed you before along with the aforementioned ALTER TABLE query.
  • Data manipulation language (DML) queries. These are the queries that you use to manipulate the data in the database. You’ve seen some of these already: INSERT, UPDATE, DELETE and SELECT.

It’s worthwhile for a PHP developer to learn the syntax and different variations of the DML queries, as they regularly need to be typed out in PHP scripts. It’s also useful to know what’s going on behind the scenes. That said, it’s not going to be detrimental to your progress as a developer to have MySQL Workbench generate your DDL queries rather than typing them out yourself. Having the queries generated for you can be a time saver, as it gives you a lot less syntax learn, and the DDL query format can be considerably more difficult to follow than most of the DML queries. To add a new column to a database table, open up MySQL Workbench,, connect to your database, double-click on the schema we created called ijdb, and expand the tables entry. You’ll see the joke table.

May Need to Boot the Server

If you’ve logged out or rebooted your PC since the last chapter, you’ll need to boot your server as you did before using the vagrant up command.

To add a new column, right click on the table name and select “Alter Table”. This will bring up the familiar table editing screen. From here you can add columns in the same way you did beforeAdd a new column called authorname, which is going to store the name of the joke’s author along with each joke. Set the type to VARCHAR(255). The type declared is a variable-length character string of up to 255 characters, VARCHAR(255)—plenty of space for even very esoteric names. Let’s also add a column for the authors’ email addresses, set the column name to authoremail and the type to VARCHAR(255)Once you press Apply, you’ll see a confirmation dialog appear with the following DDL queries:

ALTER TABLE `joke` ADD COLUMN `authorname` VARCHAR(255)

ALTER TABLE `joke` ADD COLUMN `authoremail` VARCHAR(255)

You could have typed these in yourself, but the GUI in MySQL Workbench provides some useful error checks, and it will always generate valid queries. Just to make sure the two columns were added properly, right-click on the table name in the Schemas panel and select “Select Rows – Limit 1000”. You should see the two extra columns listed. Of course, at the moment, none of your jokes have values for either of these fields. This should give you a table of results like the one below:

Our joke table now contains five columns

Looks good, right? Obviously, to accommodate this expanded table structure, we’d need to make changes to the HTML and PHP form code we wrote before that allowed us to add new jokes to the database. Using UPDATE queries, we could now add author details to all the jokes in the table. But before we spend too much time on such changes, we should stop and consider whether this new table design was the right choice here. In this case, it turns out it wasn’t.

Rule of Thumb: Keep Entities Separate

As your knowledge of database-driven websites continues to grow, you may decide that a personal joke list is too limited. In fact, you might receive more submitted jokes than you have original jokes of your own. Let’s say you decide to launch a website where people from all over the world can share jokes with each other. Adding the author’s name and email address to each joke certainly makes a lot of sense, but the method we used above leads to potential problems:

  • What if a frequent contributor to your site named Joan Smith changed her email address? She might begin to submit new jokes using the new address, but her old address would still be attached to the jokes she’d submitted in the past. Looking at your database, you might simply think there were two people named Joan Smith who had submitted jokes. She might inform you of the change of address, and you may try to update all the old jokes with the new address, but if you missed just one joke, your database would still contain incorrect information. Database design experts refer to this sort of problem as an update anomaly.
  • It would be natural for you to rely on your database to provide a list of all the people who’ve ever submitted jokes to your site. In fact, you could easily obtain a mailing list using the following query:

SELECT DISTINCT `authorname`, `authoremail`

FROM `joke`

The word DISTINCT in the above query stops MySQL from outputting duplicate result rows. For example, if Joan Smith submits 20 jokes to your site, using the DISTINCT option would cause her name to only appear once in the list instead of 20 times. Then, if for some reason, you decided to remove all the jokes that a particular author had submitted to your site, you’d remove any record of this person from the database in the process, and you’d no longer be able to email him or her with information about your site! Database design experts call this a delete anomaly As your mailing list might be a major source of income for your site, it’s unwise to go throwing away an author’s email address just because you disliked the jokes that person submitted.

You have no guarantee that Joan Smith will enter her name the same way each time. Consider the variations: Joan Smith, J. Smith, Smith, Joan—you catch my drift. This makes keeping track of a particular author exceedingly difficult, especially if Joan Smith also has several email addresses she likes to use.

These problems—and more—can be dealt with very easily using established database design principles. Instead of storing the information for the authors in the same table as the jokes, let’s create an entirely new table for our list of authors. Just as we have an id column in our joke table to identify each joke with a unique number, we’ll use an identically named column in our new table to identify our authors. We can then use those author IDs in our joke table to associate authors with their jokes. The complete database layout will look like this:

The relationship between the joke and author tables

These tables show that there are three jokes and two authors. The authorid column of the joke table establishes a relationship between the two tables, indicating that Kevin Yank submitted jokes 1 and 2 and Joan Smith submitted joke 3. Notice that since each author now only appears once in the database, and independently of the jokes submitted, we’ve avoided all the potential problems just outlined.

What’s really important to note about this database design is that we’re storing information about two types of things (jokes and authors), so it’s most appropriate to have two tables. This is a rule of thumb that you should always keep in mind when designing a database: each type of entity (or “thing”) about which you want to be able to store information should be given its own table. To set up the aforementioned database from scratch is fairly simple (involving just two CREATE TABLE queries), but since we’d like to make these changes in a nondestructive manner (that is, without losing any of our precious jokes), we’ll use MySQL Workbench to remove the authorname and authoremail columns from the joke table. To do this, right-click on the joke table in the Schema list and select “Alter Table”. Once again, it will give you an editable grid containing all the columns in the table. To delete the two columns, right-click on the column name and select Delete Selected. You’ll need to do this for both columns. Once you click Apply, you’ll see MySQL Workbench has generated this query for you:

ALTER TABLE `ijdb`.`joke`

DROP COLUMN `authoremail`,

DROP COLUMN `authorname`;

This is a DDL ALTER TABLE query for removing columns. As with all of these DDL queries, you could have typed this into the query panel manually and executed it, but we’ve used the GUI to avoid having to remember all the different commands. Now, we need to create a new table to store the authors. To do this, follow the same procedure you used to create the joke table: right-click on the Tables entry in the Schemas panel and select “Create Table”. Set the table name to author and add the following fields:

  1. id, and check the PK, AI and NN boxes
  2. name, VARCHAR(255)
  3. email, VARCHAR(255)

Click Apply, and MySQL Workbench will generate a CREATE TABLE query similar to this:

CREATE TABLE `author` (

`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

`name` VARCHAR(255),

`email` VARCHAR(255)

) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB

Finally, we add the authorid column to our joke table. Edit the joke table and add a column called authorid with the type INTIf you prefer, here are the CREATE TABLE commands that will create the two tables from scratch:

# Code to create a simple joke table that stores an author ID

CREATE TABLE `joke` (

`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

`joketext` TEXT,

`jokedate` DATE NOT NULL,

`authorid` INT

) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

# Code to create a simple author table

CREATE TABLE `author` (

`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

`name` VARCHAR(255),

`email` VARCHAR(255)

) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

All that’s left to do is add some authors to the new table, and assign authors to all the existing jokes in the database by filling in the authorid column. Go ahead and do this now if you like, as it will give you some practice with INSERT and UPDATE queries. If you’re rebuilding the database from scratch, however, here’s a series of INSERT queries that will do the trick:

# Adding authors to the database

     # We specify the IDs so they’re known when we add the jokes below.

INSERT INTO `author` SET

`id` = 1,

`name` = ‘Kevin Yank’,

`email` = ‘thatguy@kevinyank.com’;

INSERT INTO `author` (`id`, `name`, `email`)

VALUES (2, ‘Tom Butler’, ‘tom@r.je’);

# Adding jokes to the database

INSERT INTO `joke` SET

`joketext` = ‘How many programmers does it take to screw in a lightbulb? None, it\’s a hardware problem.’,

`jokedate` = ‘2017-04-01’,

`authorid` = 1;

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

VALUES (

‘Why did the programmer quit his job? He didn\’t get arrays’,

‘2017-04-01’,

1

);

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

VALUES (

‘Why was the empty array stuck outside? It didn\’t have any keys’,

‘2017-04-01’,

2

);

Both Kinds of INSERT

I’ve used this opportunity to refresh your memory about both kinds of INSERT query syntax. They both do exactly the same job and have the same result, so it’s up to you which you use, and boils down mainly to personal preference rather than any practical reason.

SELECT with Multiple Tables

With your data now separated into two tables, it may seem that you’re complicating the process of data retrieval. Consider, for example, our original goal: to display a list of jokes with the name and email address of the author next to each joke. In the single-table solution, you could gain all the information needed to produce such a list using a single SELECT query in your PHP code:

try {

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

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

$pdo->setAttribute(PDO::ATTR_ERRMODE,

PDO::ERRMODE_EXCEPTION);

$sql = ‘SELECT `id`, `joketext` FROM `joke`’;

$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’;

With our new database layout, this would, at first, no longer seem possible. As the author details of each joke are no longer stored in the joke table, you might think that you’d have to fetch those details separately for each joke you wanted to display. The code required would involve a call to the PDO query method for each and every joke to be displayed. This would be messy and involve a considerable amount of extra code.

Taking all this into account, it would seem that the “old way” was the better solution, despite its weaknesses. Fortunately, relational databases like MySQL are designed to make it easy to work with data stored in multiple tables! Using a new form of the SELECT statement, called a join, you can have the best of both worlds. Joins allow you to treat related data in multiple tables as if they were stored in a single table. Here’s what the syntax of a simple join looks like:

SELECT columns

FROM `table1`

INNER JOIN `table2`

ON condition(s) for data to be related

In your case, the columns we’re interested in are id and joketext in the joke table, and name and email in the author table. The condition for an entry in the joke table to be related to an entry in the author table is that the value of the authorid column in the joke table is equal to the value of the id column in the author table. Let’s look at an example of a join. The first two queries show you what’s contained in the two tables; they’re unnecessary to perform the join. The third query is where the action’s at:

SELECT `id`, LEFT(`joketext`, 20), `authorid` FROM `joke`

This query should now give the following results:

Results of the joke table query

SELECT * FROM `author`

And this query, as you’d expect, will show all the authors:

“All of the authors from the authors table

It’s possible to query data from both tables by using the SQL JOIN statement:

SELECT `joke`.`id`, LEFT(`joketext`, 20), `name`, `email`

FROM `joke` INNER JOIN `author`

ON `authorid` = `author`.`id`

This will display all the data from both tables:

The results of your first join

See? The results of the third SELECT—a join—group the values stored in the two tables into a single table of results, with related data correctly appearing together. Even though the data is stored in two tables, you can still access all the information you need to produce the joke list on your web page with a single database query. Note in the query that, since there are columns named id in both tables, you must specify the name of the table when you refer to either id column. The joke table’s ID is referred to as joke.id, while the author table’s ID column is author.id. If the table name is unspecified, MySQL won’t know which id you’re referring to, and will produce the following error:

Error Code: 1052. Column ‘id’ in field list is ambiguous

Now that you know how to access the data stored in your two tables efficiently, you can rewrite the code for your joke list to take advantage of joins:

try {

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

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

$pdo->setAttribute(PDO::ATTR_ERRMODE,

PDO::ERRMODE_EXCEPTION);

$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’;

You can then update your template jokes.html.php to display the author information for each joke:

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

If you run this script, you’ll see the following result:

Jokes with authors

The more you work with databases, the more you’ll come to realize the power of combining data from separate tables into a single table of results. Consider, for example, the following query, which displays a list of all jokes written by Tom Butler:

SELECT `joketext`

FROM `joke` INNER JOIN `author`

ON `authorid` = `author`.`id`

WHERE `name` = “Tom Butler”

The results that are output from this query, shown below, come only from the joke table, but the query uses a join to let it search for jokes based on a value stored in the author table. There will be plenty more examples of clever queries like this throughout , but this example alone illustrates that the practical applications of joins are many and varied, and, in almost all cases, can save you a lot of work!

Tom Butler’s jokes

Simple Relationships

The type of database layout for a given situation is usually dictated by the form of relationship that exists between the data that it needs to store. In this section, I’ll examine the typical relationship types, and explain how best to represent them in a relational database. In the case of a simple one-to-one relationship, a single table is all you’ll need. An example of a one-to-one relationship is the email address of each author in our joke database. Since there will be one email address for each author, and one author for each email address, there’s no reason to split the addresses into a separate table.

A many-to-one relationship is a little more complicated, but you’ve already seen one of these as well. Each joke in our database is associated with just one author, but many jokes may have been written by that one author. This joke–author relationship is many-to-one. I’ve already covered the problems that result from storing the information associated with a joke’s author in the same table as the joke itself. In brief, it can result in many copies of the same data, which are difficult to keep synchronized and waste space. If we split the data into two tables and use an ID column to link them together (making joins possible as shown before), all these problems disappear.

A one-to-many relationship is simply a many-to-one relationship seen from the opposite direction. As the joke–author relationship is many-to-one, the author–joke relationship is one-to-many (there is potentially one author for many jokes). This is easy to see in theory, but when you’re coming at a problem from the opposite direction, it’s less obvious. In the case of jokes and authors, we started with a library of jokes (the many) and then wanted to assign an author to each of them (the one). Let’s now look at a hypothetical design problem where we start with the one and want to add the many. Say we wanted to allow each of the authors in our database (the one) to have multiple email addresses (the many). When an inexperienced person in database design approaches a one-to-many relationship like this one, often the first thought is to try to store multiple values in a single database field like so:

A table field overloaded with multiple values

This would work, but to retrieve a single email address from the database, we’d need to break up the string by searching for commas (or whatever special character you chose to use as a separator). It’s a not-so-simple, potentially time-consuming operation. Try to imagine the PHP code necessary to remove one particular email address from a specific author! In addition, you’d need to allow for much longer values in the email column, which could result in wasted disk space, because the majority of authors would have just one email address. Now take a step back, and realize that this one-to-many relationship is just the same as the many-to-one relationship we faced between jokes and authors. The solution, therefore, is also the same: split the new entities (in this case, email addresses) into their own table. The resulting database structure would be:

The authorid field associates each row of email with one row of author

Using a join with this structure, we can easily list the email addresses associated with a particular author:

SELECT `email`

FROM `author` INNER JOIN `email`

ON `authorid` = `author`.`id`

WHERE `name` = “Kevin Yank”

Many-to-many Relationships

Okay, you now have a steadily growing database of jokes published on your website. It’s growing so quickly, in fact, that the number of jokes has become unmanageable! Your site visitors are faced with a mammoth page that contains hundreds of jokes without any structure whatsoever. We need to make a change. You decide to place your jokes into the following categories: knock-knock jokes, crossing-the-road jokes, lawyer jokes, light bulb jokes, and political jokes. Remembering our rule of thumb from earlier, you identify joke categories as a new entity, and create a table for them, either through MySQL Workbench or by issuing a CREATE TABLE query.

CREATE TABLE `category` (

`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

`name` VARCHAR(255)

) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB

This table will store a name and an ID for each category in exactly the same way the joke table had an authorid column to attribute each joke to an author. We could add a categoryid table to the joke table to associate each joke with a category. It would even be possible to query all the jokes form a particular category using this:

SELECT `joketext`, `jokedate` FROM `joke` WHERE `categoryId` =2

Now you come to the daunting task of assigning categories to your jokes. It occurs to you that a political joke might also be a crossing-the-road joke, and a knock-knock joke might also be a lawyer joke. A single joke might belong to many categories, and each category will contain many jokes. This is a many-to-many relationship.

Once again, many inexperienced developers begin to think of ways to store several values in a single column, because the obvious solution is to add a category column to the joke table and use it to list the IDs of those categories to which each joke belongs. A second rule of thumb would be useful here: if you need to store multiple values in a single field, your design is probably flawed. The correct way to represent a many-to-many relationship is by using a lookup table. This is a table that contains no actual data, but lists pairs of entries that are related. The image below shows what the database design would look like for our joke categories:

Lookup table

The jokecategory table associates joke IDs (jokeid) with category IDs (categoryid). In this example, we can see that the joke that starts with “How many lawyers …” belongs to both the lawyers and light bulb categories. A lookup table is created in much the same way as is any other table. The difference lies in the choice of the primary key. Every table we’ve created so far has had a column named id that was designated to be the PRIMARY KEY when the table was created. Designating a column as a primary key tells MySQL to forbid two entries in that column from having the same value. It also speeds up join operations based on that column.

In the case of a lookup table, there’s no single column that we want to force to have unique values. Each joke ID may appear more than once, as a joke may belong to more than one category, and each category ID may appear more than once, as a category may contain many jokes. What we want to prevent is the same pair of values appearing in the table twice. And, since the sole purpose of this table is to facilitate joins, the speed benefits offered by a primary key would come in very handy. For this reason, we usually create lookup tables with a multicolumn primary key as follows:

CREATE TABLE `jokecategory` (

`jokeid` INT NOT NULL,

`categoryid` INT NOT NULL,

PRIMARY KEY (`jokeid`, `categoryid`)

) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB

The same can be done in MySQL Workbench by checking the PK checkbox for both columns. This creates a table in which the jokeid and categoryid columns together form the primary key. This enforces the uniqueness that’s appropriate to a lookup table, preventing a particular joke from being assigned to a specific category more than once, and speeds up joins that make use of this table. Now that your lookup table is in place and contains category assignments, you can use joins to create several interesting and practical queries. This query lists all jokes in the knock-knock category:

SELECT `joketext`

FROM `joke`

INNER JOIN `jokecategory`

ON `joke`.`id` = `jokeid`

INNER JOIN `category`

ON `categoryid` = `category`.`id`

WHERE name = “knock-knock”

As you can see, this query uses two joins. First, it takes the joke table and joins it to the jokecategory table. Then it takes that joined data and joins it to the category table. As your database structure becomes more complex, multijoin queries like this one become common. The following query lists the categories that contain jokes beginning with “How many lawyers …”:

SELECT `name`

FROM `joke`

INNER JOIN `jokecategory`

ON `joke`.`id` = `jokeid`

INNER JOIN `category`

ON `categoryid` = `category`.`id`

WHERE `joketext` LIKE “How many lawyers%”

And this query—which also makes use of our author table to join together the contents of four tables—lists the names of all authors who have written knock-knock jokes:

SELECT `author`.`name`

FROM `joke`

INNER JOIN `author`

ON `authorid` = `author`.`id`

INNER JOIN `jokecategory`

ON `joke`.`id` = `jokeid`

INNER JOIN `category`

ON `categoryid` = `category`.`id`

WHERE `category`.`name` = “knock-knock”

This is starting to get complicated! Although JOINs can be used, later on we’ll see some different approaches that can help reduce this complexity—though sometimes at the expense of efficiency. We won’t add categories to the website just yet, but you now at least have a basic understanding of how the database could be structured to do so.

One for Many, and Many for One

We’ve explained the fundamentals of good database design, and covered how MySQL and, for that matter, all relational database management systems provide support for the representation of different types of relationships between entities. From your initial understanding of one-to-one relationships, you should now have expanded your knowledge to include many-to-one, one-to-many, and many-to-many relationships.In the process, you’ve learned about some common SQL commands—in particular, how to use a SELECT query to join data spread across multiple tables into a single set of results.

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.