Publishing MySQL Data on the Web - PHP & MySQL: Novice to Ninja, 5th Edition (2012)

PHP & MySQL: Novice to Ninja, 5th Edition (2012)

Chapter 4. Publishing MySQL Data on the Web

This is it—the stuff you signed up for! In this chapter, 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 installed 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 true database driven website!

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 whole idea of a database driven website is to allow the content of the site to reside in a database, so that content may be pulled dynamically from the database to create web pages for viewing on 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). As shown in Figure 4.1, 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 in Chapter 2). It then spits it out dynamically as the nicely formatted HTML page that the browser expects.

PHP retrieves MySQL data to produce web pages

Figure 4.1. PHP retrieves MySQL data to produce web pages

Just so it’s clear and fresh in your mind, this is what happens when there’s a visitor to a page on your database driven website:

1. The visitor’s web browser requests the web page from your web server.

2. The web server software (typically Apache) 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 (which will be the focus of this chapter) 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. From its perspective, 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. In Chapter 1, we set the password for the root user of your MySQL database server. Now, you could use that username and password to connect your PHP scripts to your MySQL server, but you really shouldn’t. The root user is an all-powerful administration account; if the password for that account fell into the wrong hands, a malicious user could wreak serious havoc. In most cases, there will be other layers of security preventing this from happening (for example, a firewall that prevents connections to your database from outside your web host’s network), but it’s better to be safe than sorry. Instead, 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:

1. Open up phpMyAdmin as you did in Chapter 2:

o On Windows, open the XAMPP Control Panel and click the Admin… button next to MySql (sic) to launch phpMyAdmin in your browser.

o On Mac OS X, launch MAMP and click the Open start page button if the start page fails to open automatically. Click the phpMyAdmin tab at the top of the start page to load phpMyAdmin.

2. Click the ijdb database in the list on the left-hand side of the phpMyAdmin interface, as shown in Figure 4.2.

Select the ijdb database

Figure 4.2. Select the ijdb database

3. In the main part of the interface, above the list of tables in your database (of which there should be only one—joke), click the Privileges tab.

Click the Privileges tab

Figure 4.3. Click the Privileges tab

4. You should now be looking at the list of Users having access to “ijdb” shown in Figure 4.4.

Just who has access to ‘ijdb’?

Figure 4.4. Just who has access to ‘ijdb’?

As you can see, only the root user has access to the ijdb database at this point.[22]

5. Click the Add a new User link at the bottom of the list, and fill in the new user details as follows:

User name

(Use text field) 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. I’ve chosen to name it ijdbuser in this book to help clarify the distinction between the name of the database (ijdb) and the user account that is allowed to access it (ijdbuser).

Host

(Local) localhost Because your MySQL database server is running on the same computer as your web server, we can restrict this account to only accept connections from localhost. If you needed to accept connections from other computers too, you would leave the default option of Any host alone.[23]

Password

(Use text field) mypassword This is just the password I’m going to use in this book. You should probably have your own unique password, and remember it for later use in the PHP scripts you’re going to write.

6. Under Database for user, select Grant all privileges on database “ijdb”. This will give the account carte blanche to do anything it likes to the ijdb database, but only that database.

7. Under Global privileges, leave everything unchecked. The options here would enable the account to execute specific query types on any database. We want to keep this account restricted to our single database.

8. At the bottom of the form, click Go.

As shown in Figure 4.5, phpMyAdmin should confirm that you’ve added a new user, even showing you the SQL queries it sent to the database server to do it. Don’t worry about learning these queries; they’re documented in the MySQL manual if you ever need to look them up, but it’s usually much easier just to use phpMyAdmin to manage access to your MySQL server.

phpMyAdmin shows you what you’ve done

Figure 4.5. phpMyAdmin shows you what you’ve done

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 a PHP web application called phpMyAdmin to connect to your database. Just as the PHP scripts in phpMyAdmin can connect directly to a running MySQL server, so too can your own PHP scripts; support for connecting to databases like MySQL is provided by the PHP Data Objects (PDO) extension that is built into PHP. 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 date function we used in Chapter 3. If you’re thinking “Hey, functions can’t have spaces in their names!”, you are 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 from Chapter 3 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:

chapter4/connect/index.php (excerpt)

$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 different password to 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 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. As a responsible developer, it’s your job to catch that exception and do something about it.

Warning: Catch!

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. Confused yet? I know I’m throwing (no pun intended) a lot of new concepts at you, but let me put it all together and show you what we have; I think it will make more sense that way:

try

{

$pdo = new PDO('mysql:host=localhost;dbname=ijdb', 'idjbuser',

'mypassword');

}

catch (PDOException $e)

{

$output = 'Unable to connect to the database server.';

include 'output.html.php';

exit();

}

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. But 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. We then include the template output.html.php. This is a generic template that simply outputs the value of the $output variable:

chapter4/connect/output.html.php

<!DOCTYPE html>

<html lang="en">

<head>

<meta charset="utf-8">

<title>Script Output</title>

</head>

<body>

<p>

<?php echo $output; ?>

</p>

</body>

</html>

Finally, after outputting the message, the last statement in the catch block calls the built-in exit function. exit is the first example in this book of a function that can be called with no parameters. When called this way, all this function does is cause PHP to stop executing the script at this point. This ensures that the rest of the code in our controller (which in most cases will depend on a successful database connection) will not be executed if the connection has failed. I hope that 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—there were a number of 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?

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, I’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 moving 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 most of this book. Procedural style is well suited to the relatively simple projects we’ll tackle here. Some very complex and successful PHP projects are written in the procedural programming style (you’ve heard of WordPress, right?). 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:

chapter4/connect/index.php (excerpt)

$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. 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 additional useful 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 in Chapter 3, 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 PHP 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). 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');

}

catch (PDOException $e)

{

$output = 'Unable to connect to the database server.';

include 'output.html.php';

exit();

}

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 might run into when connecting to your database; however, by default, PDO switches to a “silent failure” mode after establishing a successful connection,[24] which makes it more difficult for us to find out when something goes wrong and handle it gracefully. 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:

chapter4/connect/index.php (excerpt)

$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 in Chapter 3. 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).[24] Next, we need to configure the character encoding of our database connection. As I mentioned briefly in Chapter 3, 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. Therefore, we now need to set our new PDO object to use the UTF-8 encoding. If you go searching, you’ll find several ways to set the character encoding of a MySQL connection, but the most reliable way is to run this SQL query: SET NAMES "utf8". The PDO object we have stored in $pdo has a method called exec that we can use to send SQL queries to the database to be executed. Here’s what that looks like:

chapter4/connect/index.php (excerpt)

$pdo->exec('SET NAMES "utf8"');

Although I fully expect the exec method to run just fine, if it did fail to execute the query for some reason (let’s say our MySQL server fell over immediately after we connected to it), we should be prepared to catch the PDOException that it would throw. The easiest way to do so is to tuck our configuration statements into the same try block where we first create our PDO object. The complete code that we use to connect to MySQL and then configure that connection, therefore, is this:

chapter4/connect/index.php (excerpt)

<?php

try

{

$pdo = new PDO('mysql:host=localhost;dbname=ijdb', 'ijdbuser',

'mypassword');

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

$pdo->exec('SET NAMES "utf8"');

}

catch (PDOException $e)

{

$output = 'Unable to connect to the database server.';

include 'output.html.php';

exit();

}

To polish off this example, let’s display a status message that indicates when everything has gone right. Here’s the complete code of our controller:

chapter4/connect/index.php

<?php

try

{

$pdo = new PDO('mysql:host=localhost;dbname=ijdb', 'ijdbuser',

'mypassword');

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

$pdo->exec('SET NAMES "utf8"');

}

catch (PDOException $e)

{

$output = 'Unable to connect to the database server.';

include 'output.html.php';

exit();

}

$output = 'Database connection established.';

include 'output.html.php';

Fire up this example in your browser (if you put the index.php and output.html.php files in a directory named connect on your web server, the URL will be along the lines of http://localhost/connect/). If your MySQL server is up and running, and everything is working properly, you should see the message indicating success in Figure 4.6.

A successful connection

Figure 4.6. 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 in Figure 4.7. 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

Figure 4.7. A connection failure

This error message might be fine for visitors to your site, but what if you see this message unexpectedly while working on your site? How are you supposed to fix it? Well, the first step should be to find out exactly what’s gone wrong. You can do that by tweaking the catch block that displays the error message:

chapter4/connect/index.php (excerpt)

catch (PDOException $e)

{

$output = 'Unable to connect to the database server.';

include 'output.html.php';

exit();

}

In order to diagnose and fix this error, we’d like to see some more detail about the problem by including it in the error message that’s displayed. We can do that using the exception we’ve just caught. If you look closely at the first line of the catch block, you can see that in addition to telling PHP that we’re willing to catch a PDOException, we’re asking it to store the exception in a variable called $e. When an exception is caught, the value stored in that variable is actually another PHP object; in fact, all exceptions are represented by PHP objects! Like the PDO object we have stored in $pdo, the PDOException object has properties we can access and methods we can call. In order to find out what caused the exception, we can ask for the error message stored in the exception:

catch (PDOException $e)

{

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

$e->getMessage();

include 'output.html.php';

exit();

}

As you can see, we’re calling the getMessage method on the object stored in $e, and tacking the value it returns onto the end of our error message using the string concatenation operator (.). With that change in place, Figure 4.8 shows what the error message will look like if you have the wrong password in your PHP code.

The detailed error message resulting from an incorrect password

Figure 4.8. The detailed error message resulting from an incorrect password

If you instead made a typing mistake when you specified the character set for the connection to use, you’ll receive the detailed error message seen in Figure 4.9.

The detailed error message resulting from an invalid character set

Figure 4.9. The detailed error message resulting from an invalid character set

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.

Note: PHP Automatically Disconnects

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

In Chapter 2, we connected to the MySQL database server using phpMyAdmin, 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. Indeed, we used exec in the section called “Configuring the Connection” to send the SET NAMES "utf8" query that establishes the character set to be used by the database connection. 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 in Chapter 2:

chapter4/createtable/index.php (excerpt)

try

{

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

}

catch (PDOException $e)

{

$output = 'Error creating joke table: ' . $e->getMessage();

include 'output.html.php';

exit();

}

$output = 'Joke table successfully created.';

include 'output.html.php';

Note once again that we use the same try-catch statement technique to handle possible errors produced by the query. This example also uses the getMessage method to retrieve a detailed error message from the MySQL server. Figure 4.10 shows the error that’s displayed when, for example, the joke table already exists.

The CREATE TABLE query fails because the table already exists

Figure 4.10. 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 SQL command following, which we used in Chapter 2 to set the dates of all jokes that contained the word “chicken”:

chapter4/updatechicken/index.php (excerpt)

try

{

$sql = 'UPDATE joke SET jokedate="2012-04-01"

WHERE joketext LIKE "%chicken%"';

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

}

catch (PDOException $e)

{

$output = 'Error performing update: ' . $e->getMessage();

include 'output.html.php';

exit();

}

By storing the value returned from the exec method in $affectedRows, we can display the number of rows affected by this update:

chapter4/updatechicken/index.php (excerpt)

$output = "Updated $affectedRows rows.";

include 'output.html.php';

Figure 4.11 shows the output of this example, assuming there’s only one “chicken” joke in your database.

The number of database records updated is displayed

Figure 4.11. 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 per Figure 4.12. 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

Figure 4.12. 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—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 aPDOStatement object, which represents a result set containing a list of all the rows (entries) returned from the query.

chapter4/listjokes/index.php (excerpt)

try

{

$sql = 'SELECT joketext FROM joke';

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

}

catch (PDOException $e)

{

$error = 'Error fetching jokes: ' . $e->getMessage();

include 'error.html.php';

exit();

}

Just as before, errors are displayed using a very simple PHP template:

chapter4/listjokes/error.html.php

<!DOCTYPE html>

<html lang="en">

<head>

<meta charset="utf-8">

<title>Script Error</title>

</head>

<body>

<p>

<?php echo $error; ?>

</p>

</body>

</html>

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. I mentioned back in Chapter 3 that the while loop is a useful control structure for dealing with large amounts of data. 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 to 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 (we discussed arrays in Chapter 3). When there are no more rows in the result set, fetch returns false instead.[25] 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:

chapter4/listjokes/index.php (excerpt)

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.php) for display. To summarize, here’s the complete code of the controller for this example:

chapter4/listjokes/index.php

<?php

try

{

$pdo = new PDO('mysql:host=localhost;dbname=ijdb', 'ijdbuser',

'mypassword');

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

$pdo->exec('SET NAMES "utf8"');

}

catch (PDOException $e)

{

$error = 'Unable to connect to the database server.';

include 'error.html.php';

exit();

}

try

{

$sql = 'SELECT joketext FROM joke';

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

}

catch (PDOException $e)

{

$error = 'Error fetching jokes: ' . $e->getMessage();

include 'error.html.php';

exit();

}

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

{

$jokes[] = $row['joketext'];

}

include 'jokes.html.php';

All that’s left to complete this example is to write the jokes.html.php template. In this template, we need to display the contents of an array for the first time, rather than just a simple variable. 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 in turn each item of an array. 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;

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

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

chapter4/listjokes/jokes.html.php

<!DOCTYPE html>

<html lang="en">

<head>

<meta charset="utf-8">

<title>List of Jokes</title>

</head>

<body>

<p>Here are all the jokes in the database:</p>

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

<blockquote>

<p><?php echo htmlspecialchars($joke, ENT_QUOTES, 'UTF-8');

?>

</p>

</blockquote>

<?php endforeach; ?>

</body>

</html>

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 usehtmlspecialchars to ensure that these are translated into HTML character entities (that is, <, >, and &) so that they’re displayed correctly. Figure 4.13 shows what this page looks like once you’ve added a couple of jokes to the database.

All my best material—in one place!

Figure 4.13. All my best material—in one place!

Tip: You Can Use foreach to Loop through a Result Set, Too!

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

chapter4/listjokes/index.php (excerpt)

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

}

I’ll be using this tidier foreach form in the rest of this book.

Inserting Data into the Database

In this section, I’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 visitors to your site enter new jokes, you’ll obviously need a form. Here’s a template for a form that will fit the bill:

chapter4/addjoke/form.html.php

<!DOCTYPE html>

<html lang="en">

<head>

<meta charset="utf-8">

<title>Add Joke</title>

<style type="text/css">

textarea {

display: block;

width: 100%;

}

</style>

</head>

<body>

<form action="?" method="post">

<div>

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

<textarea id="joketext" name="joketext" rows="3" cols="40">

</textarea>

</div>

<div><input type="submit" value="Add"></div>

</form>

</body>

</html>

Once submitted, this form will request the same PHP script that generated the form—the controller script (index.php)—as we’ve seen before. You’ll notice, however, that instead of leaving the action attribute empty (""), we set its value to ?. As we’ll see in a moment, the URL used to display the form in this example will feature a query string, and setting the action to ? strips that query string off the URL when submitting the form. Figure 4.14 shows what this form looks like in a browser.

Another nugget of comic genius is added to the database

Figure 4.14. Another nugget of comic genius is added to the database

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 and $_REQUEST arrays created by PHP. Let’s tie this form into the preceding example, which displayed the list of jokes in the database. Add a link to the top of the list that invites the user to add a joke:

chapter4/addjoke/jokes.html.php (excerpt)

<body>

<p><a href="?addjoke">Add your own joke</a></p>

<p>Here are all the jokes in the database:</p>

Like the form, this link points back to the very same PHP script used to generate this page, but this time it adds a query string (?addjoke), indicating the user’s intention to add a new joke. Our controller can detect this query string and use it as a signal to display the “Add Joke” form instead of the list of jokes. Let’s make the necessary changes to the controller now:

chapter4/addjoke/index.php (excerpt)

if (isset($_GET['addjoke']))

{

include 'form.html.php';

exit();

}

This opening if statement checks if the query string contains a variable named addjoke. This is how we detect that the user clicked the new link. Even though there is no value specified by the query string (?addjoke) for the addjoke variable, it does create it, which we can detect withisset($_GET['addjoke']). When we detect this variable, we display the form by including form.html.php, and then exit. Once the user fills out the form and submits it, that form submission results in another request to this controller. This we detect by checking if $_POST['joketext'] is set:

chapter4/addjoke/index.php (excerpt)

if (isset($_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="today’s date"';

$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.[26] These attacks were so feared, in fact, that the team behind PHP added some built-in protection against SQL injections to the language; it still remains enabled by default in many PHP installations today. Called magic quotes , this protective feature of PHP automatically analyzes all values submitted by the browser and inserts 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. I mentioned this briefly in Chapter 3 when, in the welcome message example, the magic quotes feature would insert a spurious backslash into the user’s last name if it contained an apostrophe. In short, magic quotes was a bad idea, so much so that it’s scheduled to be removed from PHP in version 6. In the meantime, however, you have to deal with the problems it creates in your code. The easiest way to do this is to detect if magic quotes is enabled on your web server and, if so, to undo the modifications it has made to the submitted values.[27] Thankfully, the PHP Manual provides a snippet of code that will do this:

chapter4/addjoke/index.php (excerpt)

if (get_magic_quotes_gpc())

{

$process = array(&$_GET, &$_POST, &$_COOKIE, &$_REQUEST);

while (list($key, $val) = each($process))

{

foreach ($val as $k => $v)

{

unset($process[$key][$k]);

if (is_array($v))

{

$process[$key][stripslashes($k)] = $v;

$process[] = &$process[$key][stripslashes($k)];

}

else

{

$process[$key][stripslashes($k)] = stripslashes($v);

}

}

}

unset($process);

}

Don’t try to understand the inner workings of this code. To keep it short, I’ve used several advanced PHP features that we’re yet to cover—and one or two others that are beyond the scope of this book. Just drop this code into the top of your controller—and indeed any other PHP script that will receive user input in the form of query variables or a form submission (or, as we’ll learn in Chapter 9, browser cookies). And be assured that from this point forward, I’ll remind you whenever this code is required by an example.[28] With the damage done by magic quotes reversed, you’re now free to use submitted values in your SQL queries the right way: using prepared statements. A prepared statement is an 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. 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"';

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

$s->bindValue(':joketext', $_POST['joketext']);

$s->execute();

Let’s break this down one statement at a time. First, we write out 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 $s. 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 ($s). 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.[29] 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()';

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

$s->bindValue(':joketext', $_POST['joketext']);

$s->execute();

The MySQL function CURDATE 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. Appendix C provides a reference describing all commonly used MySQL functions. Now that we have our query, we can complete the if statement we started earlier to handle submissions of the “Add Joke” form:

chapter4/addjoke/index.php (excerpt)

if (isset($_POST['joketext']))

{

try

{

$sql = 'INSERT INTO joke SET

joketext = :joketext,

jokedate = CURDATE()';

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

$s->bindValue(':joketext', $_POST['joketext']);

$s->execute();

}

catch (PDOException $e)

{

$error = 'Error adding submitted joke: ' . $e->getMessage();

include 'error.html.php';

exit();

}

header('Location: .');

exit();

}

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 are able to see the newly added joke among them. That’s what the two lines highlighted in bold 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 thejokes.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 redirect [30]—a special response that tells the browser “the page you’re looking for is over here.” 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 back to the very same page: our controller. We’re asking the browser to submit another request, this time without a form submission attached to it, rather than sending the browser to another location. Since we want to point the browser at our controller (index.php) using the URL of the parent directory, we can simply tell the browser to reload the current directory, which is expressed as a period (.). Here are the two lines that redirect the browser back to our controller after adding the new joke to the database:

chapter4/addjoke/index.php (excerpt)

header('Location: .');

exit();

}

Tip: $_SERVER['PHP_SELF'] is the URL of the Current Page

Another common means of obtaining the URL of the current page in PHP is with $_SERVER['PHP_SELF']. Like $_GET, $_POST, and $_REQUEST, $_SERVER is an array variable that’s automatically created by PHP. It contains a whole bunch of information supplied by your web server. In particular, $_SERVER['PHP_SELF'] will always be set to the URL of the PHP script that your web server used to generate the current page. Unfortunately, because the web server automatically translates a request for http://localhost/addjoke/ to a request forhttp://localhost/addjoke/index.php, $_SERVER['PHP_SELF'] will contain the latter URL. Redirecting the browser to . lets us preserve the shorter, more memorable form of the URL. For this reason, I’ve avoided using $_SERVER['PHP_SELF'] in this book; however, I thought you might like to know what it does, since it’s so commonly used in basic PHP examples around the Web.

The rest of the controller is responsible for displaying the list of jokes as before. Here’s the complete code of the controller:

chapter4/addjoke/index.php

<?php

if (get_magic_quotes_gpc())

{

$process = array(&$_GET, &$_POST, &$_COOKIE, &$_REQUEST);

while (list($key, $val) = each($process))

{

foreach ($val as $k => $v)

{

unset($process[$key][$k]);

if (is_array($v))

{

$process[$key][stripslashes($k)] = $v;

$process[] = &$process[$key][stripslashes($k)];

}

else

{

$process[$key][stripslashes($k)] = stripslashes($v);

}

}

}

unset($process);

}

if (isset($_GET['addjoke']))

{

include 'form.html.php';

exit();

}

try

{

$pdo = new PDO('mysql:host=localhost;dbname=ijdb', 'ijdbuser',

'mypassword');

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

$pdo->exec('SET NAMES "utf8"');

}

catch (PDOException $e)

{

$error = 'Unable to connect to the database server.';

include 'error.html.php';

exit();

}

if (isset($_POST['joketext']))

{

try

{

$sql = 'INSERT INTO joke SET

joketext = :joketext,

jokedate = CURDATE()';

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

$s->bindValue(':joketext', $_POST['joketext']);

$s->execute();

}

catch (PDOException $e)

{

$error = 'Error adding submitted joke: ' . $e->getMessage();

include 'error.html.php';

exit();

}

header('Location: .');

exit();

}

try

{

$sql = 'SELECT joketext FROM joke';

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

}

catch (PDOException $e)

{

$error = 'Error fetching jokes: ' . $e->getMessage();

include 'error.html.php';

exit();

}

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

{

$jokes[] = $row['joketext'];

}

include 'jokes.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. A database connection is unnecessary to display the “Add Joke” form, though, so that code can come at the very top of the controller script. Load this up and add a new joke or two as per Figure 4.15 to the database via your browser.

Look, Ma—no SQL!

Figure 4.15. Look, Ma—no SQL!

There you have it. With a single controller (index.php) pulling the strings, 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 page, 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 still be able to do it all with a single controller script (index.php).

· The SQL DELETE command will be required, which I introduced in Chapter 2.

· 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 idcolumn so that we can identify each joke uniquely:

chapter4/deletejoke/index.php (excerpt)

try

{

$sql = 'SELECT id, joketext FROM joke';

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

}

catch (PDOException $e)

{

$error = 'Error fetching jokes: ' . $e->getMessage();

include 'error.html.php';

exit();

}

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:

chapter4/deletejoke/index.php (excerpt)

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

{

$jokes[] = array('id' => $row['id'], 'text' => $row['joketext']);

}

Note: The foreach version

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'], 'text' =>

$row['joketext']);

}

Once this while 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:

chapter4/deletejoke/jokes.html.php (excerpt)

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

<form action="?deletejoke" method="post">

(1)

<blockquote>

<p>

<?php echo htmlspecialchars($joke['text'], ENT_QUOTES,(2)

'UTF-8'); ?>

<input type="hidden" name="id" value="<?php

echo $joke['id']; ?>">

(3)

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

(4)

</p>

</blockquote>

</form>

(5)

<?php endforeach; ?>

Here are the highlights of this updated code:

(1)

Each joke will be displayed in a form, which, if submitted, will delete that joke. We signal this to our controller using the ?deletejoke query string in the action attribute.

(2)

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.

(3)

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.

(4)

This submit button (<input type="submit">) submits the form when clicked. Its value attribute gives it a label of Delete.

(5)

Finally, we close the form for this joke.

Note: This Markup Could Be Better

If you know your HTML, you’re probably thinking that those <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 Cascading Style Sheets (CSS) code that’s really beyond the scope of this book. Rather than teach you CSS layout techniques in a book about PHP and MySQL, I’ve decided to go with this imperfect markup. 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.

Figure 4.16 shows what the joke list looks like with the Delete buttons added.

Each button can delete its respective joke

Figure 4.16. Each button can delete its respective joke

All that remains to make this new feature work is to update the controller. It can then process the form submission that results from clicking one of our new Delete buttons:

chapter4/deletejoke/index.php (excerpt)

if (isset($_GET['deletejoke']))

{

try

{

$sql = 'DELETE FROM joke WHERE id = :id';

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

$s->bindValue(':id', $_POST['id']);

$s->execute();

}

catch (PDOException $e)

{

$error = 'Error deleting joke: ' . $e->getMessage();

include 'error.html.php';

exit();

}

header('Location: .');

exit();

}

This chunk of code works exactly like the one we added to process the “Add Joke” code earlier in the chapter. We start by preparing a DELETE query with a placeholder for the joke ID that we wish to delete.[31] 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.

Important: Why Not a Link?

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>

<?php echo htmlspecialchars($joke['text'], ENT_QUOTES,

'UTF-8'); ?>

<a href="?deletejoke&id=<?php echo $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. If your site deleted a joke as a result of a hyperlink being followed, you could find your jokes being deleted automatically by your users’ browsers!

Here’s the complete code of the finished controller. If you have any questions, make sure to post them in the SitePoint Forums!

chapter4/deletejoke/index.php

<?php

if (get_magic_quotes_gpc())

{

$process = array(&$_GET, &$_POST, &$_COOKIE, &$_REQUEST);

while (list($key, $val) = each($process))

{

foreach ($val as $k => $v)

{

unset($process[$key][$k]);

if (is_array($v))

{

$process[$key][stripslashes($k)] = $v;

$process[] = &$process[$key][stripslashes($k)];

}

else

{

$process[$key][stripslashes($k)] = stripslashes($v);

}

}

}

unset($process);

}

if (isset($_GET['addjoke']))

{

include 'form.html.php';

exit();

}

try

{

$pdo = new PDO('mysql:host=localhost;dbname=ijdb', 'ijdbuser',

'mypassword');

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

$pdo->exec('SET NAMES "utf8"');

}

catch (PDOException $e)

{

$error = 'Unable to connect to the database server.';

include 'error.html.php';

exit();

}

if (isset($_POST['joketext']))

{

try

{

$sql = 'INSERT INTO joke SET

joketext = :joketext,

jokedate = CURDATE()';

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

$s->bindValue(':joketext', $_POST['joketext']);

$s->execute();

}

catch (PDOException $e)

{

$error = 'Error adding submitted joke: ' . $e->getMessage();

include 'error.html.php';

exit();

}

header('Location: .');

exit();

}

if (isset($_GET['deletejoke']))

{

try

{

$sql = 'DELETE FROM joke WHERE id = :id';

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

$s->bindValue(':id', $_POST['id']);

$s->execute();

}

catch (PDOException $e)

{

$error = 'Error deleting joke: ' . $e->getMessage();

include 'error.html.php';

exit();

}

header('Location: .');

exit();

}

try

{

$sql = 'SELECT id, joketext FROM joke';

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

}

catch (PDOException $e)

{

$error = 'Error fetching jokes: ' . $e->getMessage();

include 'error.html.php';

exit();

}

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

{

$jokes[] = array('id' => $row['id'], 'text' => $row['joketext']);

}

include 'jokes.html.php';

Mission Accomplished

In this chapter, 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 chapter achieved the stated mission of this book: to teach you how to build a database driven website. Of course, the example in this chapter contained only the bare essentials. In the rest of the book, I’ll show you how to flesh out the skeleton you learned to build in this chapter. In Chapter 5, we return to the SQL Query window in phpMyAdmin. 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!


[22] If you’re wondering why the root user is listed twice, it’s because MySQL actually comes configured with two root user accounts: one for accepting connections from the IP address 127.0.0.1, and another for accepting connections from the hostname localhost. Normally 127.0.0.1 and localhost both refer to your own computer, but, depending on how you connect, it may see the connection coming from one or the other. We’ll explore this issue in greater detail in Chapter 10.

[23] For now, I strongly recommend you stick with a Local account. Any host accounts can cause problems that we’ll explore in Chapter 10.

[24] You can read about the details of PDO’s error-handling modes in the PHP Manual.

[25] This is one case where asking a PDO object to do something it cannot do (as fetch cannot return the next row when there are no rows left in the result set) will not throw a PDOException. If it did, we’d be unable to use the fetch method in a while loop condition the way we do here.

[26] In many programming niches, SQL injection attacks are still surprisingly effective, as developers don’t expect them. Consider this remarkable attempt to cause traffic cameras to drop their databases: “SQL Injection Licence (sic) Plate Hopes to Foil Euro Traffic Cameras.”

[27] You can disable magic quotes—and save your web server a lot of work—by setting the magic_quotes_gpc option in your php.ini file to Off. To make sure your code still functions if this setting is changed, however, you should still deal with magic quotes in your code when it’s enabled.

[28] In Chapter 6, I’ll show you how to reduce the burden of repeatedly including this code snippet in your controller code.

[29] Yes, this PDOStatement method is called execute, unlike the similar method of PDO objects, which is called exec. PHP has many strengths, but consistency isn’t one of them.

[30] HTTP stands for HyperText Transfer Protocol, and is the language that describes the request/response communications that are exchanged between the visitor’s web browser and your web server.

[31] You might think that a prepared statement is unnecessary in this instance to protect our database from SQL injection attacks, since the joke ID is provided by a hidden form field invisible to the user. In fact, all form fields—even hidden ones—are ultimately under the user’s control. There are widely distributed browser add-ons, for example, that will make hidden form fields visible and available for editing by the user. Remember: any value submitted by the browser is ultimately suspect when it comes to protecting your site’s security.