By: Kevin Yank

July 9th, 2009

Reader Rating: 9.5

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 [1] and learned the basics of MySQL [2], a relational database engine, and PHP [3], a server-side scripting language. Now you’re ready to learn how to use these new tools together to create a true database driven web site!

If you'd rather read this tutorial offline, you can download the chapters in PDF format [4].

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 web site is to allow the content of the site to reside in a database, and for that content to be pulled from the database dynamically to create web pages for people to view with a regular web browser. So, at one end of the system you have a visitor to your site who uses a web browser to request a page, and 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 understands only how to respond to SQL queries (commands).

As shown in the figure 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 in Chapter 2: Introducing MySQL [5]), 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 will happen when a person visits a page on your database driven web site:

• The visitor’s web browser requests the web page using a standard URL.
• 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.
• 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.
• The MySQL database responds by sending the requested content to the PHP script.
• 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.
• The PHP interpreter finishes up by handing a copy of the HTML it has created to the web server.
• 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.

Connecting to MySQL with PHP

Before you can retrieve content out of your MySQL database for inclusion in a web page, you must know how to establish a connection to MySQL from inside a PHP script. Back in Chapter 2: Introducing MySQL [6], you used a program called mysql that allowed you to make such a connection from the command prompt. Just as that program could connect directly to a running MySQL server, so too can the PHP interpreter; support for connecting to MySQL is built right into the language in the form of a library of built-in functions.

The built-in function mysqli_connect establishes a connection to a MySQL server:

mysqli_connect(hostname, username, password)

You may remember from Chapter 3: Introducing PHP [7] that PHP functions usually return a value when they’re called. The mysqli_connect function, for example, returns a link identifier that identifies the connection that has been established. Since we intend to make use of the connection, we should hold onto this value. Here’s an example of how we might connect to our MySQL server:

$link = mysqli_connect('localhost', 'root', 'password');

As described above, the values of the three function parameters may differ for your MySQL server; at the very least, you’ll need to substitute in the root password you established for your MySQL server. What’s important to see here is that the value returned by mysqli_connect is stored in a variable named $link.

As the MySQL server is a completely separate piece of software from the web server, 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. In such cases, the mysqli_connect function returns FALSE, instead of a connection identifier, as no connection is established. This allows us to react to such failures using an if statement:

$link = mysqli_connect('localhost', 'root', 'password');
if (!$link)
{
 $output = 'Unable to connect to the database server.';
 include 'output.html.php';
 exit();
}

The condition in this if statement uses the not operator (!) to make the condition true when $link has a value of false (that is, when the connection attempt has failed). If the connection succeeds, $link will have a value that’s considered true, which will make !$link false. In short, the body of the if statement is executed only if the connection fails.

Within the body of the if statement, 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:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
 <head>
   <title>PHP Output</title>
   <meta http-equiv="content-type"
       content="text/html; charset=utf-8"/>
 </head>
 <body>
   <p>
     <?php echo $output; ?>
   </p>
 </body>
</html>

Finally, after outputting the message, the body of the if statement 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.

Assuming the connection succeeds, however, you need to configure it before use. As I mentioned briefly in Chapter 3: Introducing PHP [8], you should use UTF-8 encoded text in your web sites to maximize the range of characters that your users will have at their disposal when filling in forms on your site. By default, when PHP connects to MySQL, it once again uses the simpler ISO-8859-1 encoding instead of UTF-8. You must therefore follow up your mysqli_connect code with a call to mysqli_set_charset, another built-in PHP function:

mysqli_set_charset($link, 'utf8')

Notice we use the $link variable that contains the MySQL link identifier to tell the function which database connection to use. This function returns true when it’s successful and false if an error occurs. Once again, it’s prudent to use an if statement to handle errors:

if (!mysqli_set_charset($link, 'utf8'))
{
 $output = 'Unable to set database connection encoding.';
 include 'output.html.php';
 exit();
}

Note that this time, instead of assigning the result of the function to a variable and then checking if the variable is true or false, I have simply used the function call itself as the condition. This may look a little strange, but it’s a very commonly used shortcut. To check whether the condition is true or false, PHP executes the function and then checks its return value—exactly what we need to happen.

As in Chapter 2: Introducing MySQL [9] when you connected to the MySQL server using the mysql program, once you’ve established a connection the usual next step is to select the database with which you want to work. Let’s say you want to work with the joke database you created in Chapter 2: Introducing MySQL. This database was called ijdb. Selecting that database in PHP is just a matter of another function call:

mysqli_select_db($link, 'ijdb');

mysqli_select_db simply sets the selected database ('ijdb') for the specified database connection ($link). Yet again, it’s best to guard against errors with an if statement:

if (!mysqli_select_db($link, 'ijdb'))
{
 $output = 'Unable to locate the joke database.';
 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:

<?php
$link = mysqli_connect('localhost', 'root', 'password');
if (!$link)
{
 $output = 'Unable to connect to the database server.';
 include 'output.html.php';
 exit();
}

if (!mysqli_set_charset($link, 'utf8'))
{
 $output = 'Unable to set database connection encoding.';
 include 'output.html.php';
 exit();
}

if (!mysqli_select_db($link, 'ijdb'))
{
 $output = 'Unable to locate the joke database.';
 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 like http://localhost/connect/). If your MySQL server is up and running and everything works the way it should, you should see the message indicating success in the figure below.

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 the figure below. To make sure your error handling code is working properly, you might want to misspell your password intentionally to test it out.

What PHP Error?

Depending on your web server’s PHP configuration, you may or may not see the first paragraph shown in the figure above. This warning message is automatically generated by PHP if it’s configured to display errors. These detailed errors can be invaluable tools for diagnosing problems with your code during development. Since you’d probably prefer to keep this kind of technical information hidden once your site is live on the Web, it’s common to switch off these errors on production servers.

If you installed Apache yourself, chances are this message will be displayed. If you’re using a bundled Apache solution (like WampServer or MAMP), PHP error display may be switched off by default. To display these errors (they’re especially helpful in development when you’re trying to determine the cause of a problem), you need to open your server’s php.ini file and set the display_errors option to On. You can access WampServer’s php.ini file from the system tray menu. MAMP’s php.ini file is in the /Applications/MAMP/conf/php5 folder on your system.

If PHP connects to your MySQL server and then fails to find the ijdb database, you’ll see a similar message to Figure 4.4, “A connection failure”. Once again, you should probably test your error handling code by intentionally misspelling your database name.

With a connection established and a database selected, you’re ready to begin using the data stored in the database.

PHP Automatically Disconnects

You might be wondering what happens to the connection with the MySQL server after the script has finished executing. While PHP does have a function for disconnecting from the server (mysqli_close), PHP will automatically close any open database connections when they’re no longer needed, so you can usually just let PHP clean up after you.

Sending SQL Queries with PHP

In Chapter 2: Introducing MySQL [10], we connected to the MySQL database server using a program called mysql that allowed us to type SQL queries (commands) and view the results of those queries immediately. In PHP, a similar mechanism exists: the mysqli_query function.

mysqli_query(link, query)

Here query is a string that contains the SQL query you want to execute. As with mysqli_select_db, you must also provide the MySQL link identifier returned by mysqli_connect.

What this function returns will depend on the type of query being sent. For most SQL queries, mysqli_query returns either true or false to indicate success or failure respectively. Consider the following example, which attempts to create the joke table we created in Chapter 2, Introducing MySQL:

$sql = 'CREATE TABLE joke (  
     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  
     joketext TEXT,  
     jokedate DATE NOT NULL  
   ) DEFAULT CHARACTER SET utf8';  
if (!mysqli_query($link, $sql))  
{  
 $output = 'Error creating joke table: ' . mysqli_error($link);  
 include 'output.html.php';  
 exit();  
}  
 
$output = 'Joke table successfully created.';  
include 'output.html.php';

Note once again we use the same if statement technique to handle possible errors produced by the query. This example also uses the mysqli_error function to retrieve a detailed error message from the MySQL server. The figure below shows the error that’s displayed when the joke table already exists, for example.

For DELETE, INSERT, and UPDATE queries (which serve to modify stored data), MySQL also keeps track of the number of table rows (entries) that were affected by the query. Consider the SQL command below, which we used in Chapter 2: Introducing MySQL [11] to set the dates of all jokes that contained the word “chicken”:

$sql = 'UPDATE joke SET jokedate="2010-04-01"  
   WHERE joketext LIKE "%chicken%"';  
if (!mysqli_query($link, $sql))  
{  
 $output = 'Error performing update: ' . mysqli_error($link);  
 include 'output.html.php';  
 exit();  
}

When we execute this query, we can use the mysql_affected_rows function to view the number of rows that were affected by this update:

$output = 'Updated ' . mysqli_affected_rows($link) . ' rows.';  
include 'output.html.php';

The following figure shows the output of this example, assuming you only have one “chicken” joke in your database.

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

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 mysqli_query function returns either true (success) or false (failure). For SELECT queries, more information is needed. You’ll recall that SELECT queries are used to view stored data in the database. In addition to indicating whether the query succeeded or failed, PHP must also receive the results of the query. Thus, when it processes a SELECT query, mysqli_query returns a result set, which contains a list of all the rows (entries) returned from the query. false is still returned if the query fails for any reason:

$result = mysqli_query($link, 'SELECT joketext FROM joke');  
if (!$result)  
{  
 $error = 'Error fetching jokes: ' . mysqli_error($link);  
 include 'error.html.php';  
 exit();  
}

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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"  
   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">  
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">  
 <head>  
   <title>PHP Error</title>  
   <meta http-equiv="content-type"  
       content="text/html; charset=utf-8"/>  
 </head>  
 <body>  
   <p>  
     <?php echo $error; ?>  
   </p>  
 </body>  
</html>

Provided that no error was encountered in processing the query, the above code will store a result set 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, that result set can be quite big.

I mentioned back in Chapter 3: Introducing PHP [12] that the while loop is a useful control structure for dealing with large amounts of data. Here’s an outline of the code that will process the rows in a result set one at a time:

while ($row = mysqli_fetch_array($result))  
{  
 // 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 = mysqli_fetch_array($result);

The mysqli_fetch_array function accepts a result set as a parameter (stored in the $result variable in this case), and 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, mysqli_fetch_array instead returns false.

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 mysqli_fetch_array are represented as associative arrays. The indices are named after the table columns in the result set. If $row is a row in our result set, then $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 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 = mysqli_fetch_array($result))  
{  
 $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:

<?php  
$link = mysqli_connect('localhost', 'root', 'password');  
if (!$link)  
{  
 $error = 'Unable to connect to the database server.';  
 include 'error.html.php';  
 exit();  
}  
 
if (!mysqli_set_charset($link, 'utf8'))  
{  
 $output = 'Unable to set database connection encoding.';  
 include 'output.html.php';  
 exit();  
}  
 
if (!mysqli_select_db($link, 'ijdb'))  
{  
 $error = 'Unable to locate the joke database.';  
 include 'error.html.php';  
 exit();  
}  
 
$result = mysqli_query($link, 'SELECT joketext FROM joke');  
if (!$result)  
{  
 $error = 'Error fetching jokes: ' . mysqli_error($link);  
 include 'error.html.php';  
 exit();  
}  
 
while ($row = mysqli_fetch_array($result))  
{  
 $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, for the first time we need to display the contents of an array, rather than just a simple variable. The most common way to process an array in PHP is to use a loop. We have already seen while loops and for loops; another type of loop, which is particularly helpful for processing arrays, is the foreach loop:

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)  
{  
?>  
 <!-- Code to output each $joke -->  
<?php  
}  
?>

With this blend of PHP code to describe the loop and HTML code to display it, this 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): ?>  
 <!-- Code to output each $joke -->  
<?php endforeach; ?>

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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"  
   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">  
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">  
 <head>  
   <title>List of Jokes</title>  
   <meta http-equiv="content-type"  
       content="text/html; charset=utf-8"/>  
 </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 use htmlspecialchars to ensure that these are translated into HTML character entities (that is, <, >, and &) so that they’re displayed correctly.

This figure shows what this page looks like once you’ve added a couple of jokes to the database.

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 type in new jokes, you’ll obviously need a form. Here’s a template for a form that will fit the bill:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"  
   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">  
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">  
 <head>  
   <title>Add Joke</title>  
   <meta http-equiv="content-type"  
       content="text/html; charset=utf-8"/>  
   <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>

As we’ve seen before, when submitted this form will request the same PHP script that generated the form—the controller script (index.php). 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.

The figure below shows what this form looks like in a browser.

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:

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

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 with isset($_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:

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

To insert the submitted joke into the database, we must run 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"';

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 just the right sort of 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 web sites.

These attacks were so feared, in fact, that the team behind PHP added some built-in protection against SQL injections to the language that 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. Firstly, 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.

Secondly, 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 2: Introducing MySQL [13] 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 it is, to undo the modifications it has made to the submitted values. (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.) Thankfully, the PHP Manual [14] provides a snippet of code that will do exactly this:

if (get_magic_quotes_gpc())  
{  
 function stripslashes_deep($value)  
 {  
   $value = is_array($value) ?  
       array_map('stripslashes_deep', $value) :  
       stripslashes($value);  
 
   return $value;  
 }  
 
 $_POST = array_map('stripslashes_deep', $_POST);  
 $_GET = array_map('stripslashes_deep', $_GET);  
 $_COOKIE = array_map('stripslashes_deep', $_COOKIE);  
 $_REQUEST = array_map('stripslashes_deep', $_REQUEST);  
}  


Avoid wasting time trying to understand the inner workings of this code; to keep the code short, it uses several advanced PHP features that we’ve yet to see—and one or two others that are beyond the scope of this book. Rather, 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, Cookies, Sessions, and Access Control, browser cookies). And be assured; from this point forward, I’ll remind you whenever this code is required by an example. (In Chapter 6: Structured PHP Programming I’ll show you how to manage the burden of repeatedly including this code snippet in your controller code.)

With the damage done by magic quotes reversed, you must now prepare those values that you do intend to use in your SQL query. Just as it provides htmlspecialchars for outputting user-submitted values into HTML code, PHP provides a function that prepares a user-submitted value so that you can use it safely in your SQL query: mysqli_real_escape_string. Not the most elegant name, but it does the trick. Here’s how you use it:

$joketext = mysqli_real_escape_string($link, $_POST['joketext']);  
$sql = 'INSERT INTO joke SET  
   joketext="' . $joketext . '",  
   jokedate="today's date"';

This code first uses mysqli_real_escape_string to store a “query safe” version of the contents of $_POST['joketext'] in the new variable $joketext. It then uses this variable to insert the submitted value into the INSERT query as the value of the joketext column.

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 form that MySQL requires, but it turns out MySQL itself has a function to do this: CURDATE:

$joketext = mysqli_real_escape_string($link, $_POST['joketext']);  
$sql = 'INSERT INTO joke SET  
   joketext="' . $joketext . '",  
   jokedate=CURDATE()';

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 we’ll introduce them only as required. Appendix B, MySQL Functions provides a reference that describes all commonly used MySQL functions.

Now that we have our query, we can complete the if statement we started above to handle submissions of the “Add Joke” form. We can execute our INSERT query by using the mysqli_query function:

if (isset($_POST['joketext']))  
{  
 $joketext = mysqli_real_escape_string($link, $_POST['joketext']);  
 $sql = 'INSERT INTO joke SET  
     joketext="' . $joketext . '",  
     jokedate=CURDATE()';  
 if (!mysqli_query($link, $sql))  
 {  
   $error = 'Error adding submitted joke: ' . mysqli_error($link);  
   include 'error.html.php';  
   exit();  
 }  
 
 header('Location: .');  
 exit();  
}

But wait! This if statement has one more new 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.

Your first instinct in order to achieve the desired result might be to allow the controller, after adding the new joke to the database, simply to fetch the list of jokes from the database and display the list using the jokes.html.php template as usual. The problem with doing this is that the resulting page, from the browser’s perspective, would be the effect 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 behaviour.

Instead, we want the browser to treat the updated list of jokes as a normal web page, 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 (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.)—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 special headers into the response sent to the server. 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 (.).

Thus, the two lines that redirect the browser back to our controller after adding the new joke to the database:

 header('Location: .');  
 exit();  
}

$_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 is automatically created by PHP. $_SERVER 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 for http://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 have avoided using $_SERVER['PHP_SELF'] in this book. Since it’s so commonly used in basic PHP examples around the Web, however, I thought you might like to know what it does.

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

<?php  
if (get_magic_quotes_gpc())  
{  
 function stripslashes_deep($value)  
 {  
   $value = is_array($value) ?  
       array_map('stripslashes_deep', $value) :  
       stripslashes($value);  
 
   return $value;  
 }  
 
 $_POST = array_map('stripslashes_deep', $_POST);  
 $_GET = array_map('stripslashes_deep', $_GET);  
 $_COOKIE = array_map('stripslashes_deep', $_COOKIE);  
 $_REQUEST = array_map('stripslashes_deep', $_REQUEST);  
}  
 
if (isset($_GET['addjoke']))  
{  
 include 'form.html.php';  
 exit();  
}  
 
$link = mysqli_connect('localhost', 'root', 'password');  
if (!$link)  
{  
 $error = 'Unable to connect to the database server.';  
 include 'error.html.php';  
 exit();  
}  
 
if (!mysqli_set_charset($link, 'utf8'))  
{  
 $output = 'Unable to set database connection encoding.';  
 include 'output.html.php';  
 exit();  
}  
 
if (!mysqli_select_db($link, 'ijdb'))  
{  
 $error = 'Unable to locate the joke database.';  
 include 'error.html.php';  
 exit();  
}  
 
if (isset($_POST['joketext']))  
{  
 $joketext = mysqli_real_escape_string($link, $_POST['joketext']);  
 $sql = 'INSERT INTO joke SET  
     joketext="' . $joketext . '",  
     jokedate=CURDATE()';  
 if (!mysqli_query($link, $sql))  
 {  
   $error = 'Error adding submitted joke: ' . mysqli_error($link);  
   include 'error.html.php';  
   exit();  
 }  
 
 header('Location: .');  
 exit();  
}  
 
$result = mysqli_query($link, 'SELECT joketext FROM joke');  
if (!$result)  
{  
 $error = 'Error fetching jokes: ' . mysqli_error($link);  
 include 'error.html.php';  
 exit();  
}  
 
while ($row = mysqli_fetch_array($result))  
{  
 $jokes[] = $row['joketext'];  
}  
 
include 'jokes.html.php';  
?>

As you review this code to make sure it all makes sense to you, note that the calls to mysqli_connect and mysqli_select_db must come before any of the code that runs database queries. A database connection is unnecessary to display the “Add Joke” form, however, so that code can come at the very top of the controller script.

Load this up and add a new joke or two to the database via your browser. The resulting page should look like the figure below.

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. We’ll place next to each joke on the page a button labeled Delete that, when clicked, 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 that we have for 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).
• You’ll need to use the SQL DELETE command, which I introduced in Chapter 2, Introducing MySQL.
• 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 would 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 we can identify each joke uniquely:

$result = mysqli_query($link, 'SELECT id, joketext FROM joke');    
if (!$result)    
{    
 $error = 'Error fetching jokes: ' . mysqli_error($link);    
 include 'error.html.php';    
 exit();    
}

We must also modify the while loop that stores the database results in the $jokes array. Instead of simply storing the text of each joke as an item in the array, we must 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 = mysqli_fetch_array($result))    
{    
 $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, then, should be to update the jokes.html.php template to retrieve each joke’s text from this new array structure, and also to provide a Delete button for each joke:

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

• 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.
• 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 wish to send along the ID of the joke to be deleted. To do this, we need a form field containing the joke’s ID, but this is a field we’d prefer to keep hidden from the user. We therefore using 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.
• This submit button (<input type="submit"/>) submits the form when clicked. Its value attribute gives it a label of Delete.

This Markup Could Be Better

If you know your HTML, you’re probably thinking 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, to make that tag structure display clearly requires a little Cascading Style Sheets (CSS) code that’s really beyond the scope of this book.

Rather than attempt to 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 securing the services of a person who does) so that you can take complete control of your HTML markup without worrying about the CSS code required to make it look nice.

The figure below shows what the joke list looks like with the Delete buttons added.

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

if (isset($_GET['deletejoke']))    
{    
 $id = mysqli_real_escape_string($link, $_POST['id']);    
 $sql = "DELETE FROM joke WHERE id='$id'";    
 if (!mysqli_query($link, $sql))    
 {    
   $error = 'Error deleting joke: ' . mysqli_error($link);    
   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 this chapter. We start by using mysqli_real_escape_string to sanitize the submitted value of $_POST['id'] before using it in a database query—this time, a DELETE query. You might think it’s unnecessary to sanitize this value, since it’s produced by a hidden form field that the user is unable to see. In fact, however, 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. Once that query is executed, we use the PHP header function to ask the browser to send a new request to view the updated list of jokes.

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&amp;id=<?php echo $joke['id'];    
         ?>">Delete</a>    
   </p>    
 </blockquote>    
<?php endforeach; ?>

In short, hyperlinks should never be used to perform actions (like deleting a joke); hyperlinks should 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 should only ever be performed as a result of a form with method="post" being submitted.

The reason is that forms with method="post" are treated differently by browsers and related software. If you submit a form with method="post" and then click the Refresh button in your browser, for example, the browser will 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 getting 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 [15]!

<?php    
if (get_magic_quotes_gpc())    
{    
 function stripslashes_deep($value)    
 {    
   $value = is_array($value) ?    
       array_map('stripslashes_deep', $value) :    
       stripslashes($value);    
   
   return $value;    
 }    
   
 $_POST = array_map('stripslashes_deep', $_POST);    
 $_GET = array_map('stripslashes_deep', $_GET);    
 $_COOKIE = array_map('stripslashes_deep', $_COOKIE);    
 $_REQUEST = array_map('stripslashes_deep', $_REQUEST);    
}    
   
if (isset($_GET['addjoke']))    
{    
 include 'form.html.php';    
 exit();    
}    
   
$link = mysqli_connect('localhost', 'root', 'password');    
if (!$link)    
{    
 $error = 'Unable to connect to the database server.';    
 include 'error.html.php';    
 exit();    
}    
   
if (!mysqli_set_charset($link, 'utf8'))    
{    
 $output = 'Unable to set database connection encoding.';    
 include 'output.html.php';    
 exit();    
}    
   
if (!mysqli_select_db($link, 'ijdb'))    
{    
 $error = 'Unable to locate the joke database.';    
 include 'error.html.php';    
 exit();    
}    
   
if (isset($_POST['joketext']))    
{    
 $joketext = mysqli_real_escape_string($link, $_POST['joketext']);    
 $sql = 'INSERT INTO joke SET    
     joketext="' . $joketext . '",    
     jokedate=CURDATE()';    
 if (!mysqli_query($link, $sql))    
 {    
   $error = 'Error adding submitted joke: ' . mysqli_error($link);    
   include 'error.html.php';    
   exit();    
 }    
     
 header('Location: .');    
 exit();    
}    
   
if (isset($_GET['deletejoke']))    
{    
 $id = mysqli_real_escape_string($link, $_POST['id']);    
 $sql = "DELETE FROM joke WHERE id='$id'";    
 if (!mysqli_query($link, $sql))    
 {    
   $error = 'Error deleting joke: ' . mysqli_error($link);    
   include 'error.html.php';    
   exit();    
 }    
   
 header('Location: .');    
 exit();    
}    
   
$result = mysqli_query($link, 'SELECT id, joketext FROM joke');    
if (!$result)    
{    
 $error = 'Error fetching jokes: ' . mysqli_error($link);    
 include 'error.html.php';    
 exit();    
}    
   
while ($row = mysqli_fetch_array($result))    
{    
 $jokes[] = array('id' => $row['id'], 'text' => $row['joketext']);    
}    
   
include 'jokes.html.php';    
?>

Mission Accomplished

In this chapter, you learned some new PHP functions that allow you to interface with a MySQL database server. Using these functions, you built your first database driven web site, which published the ijdb database online, and allowed visitors to add jokes to it and delete jokes from it.

In a way, you could say this chapter achieved the stated mission of this book, to teach you how to build a database driven web site. Of course, the example in this chapter contains only the bare essentials. In the rest of this book, I’ll show you how to flesh out the skeleton you learned to build in this chapter.

In Chapter 5, Relational Database Design, we go back to the MySQL command line. 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!

But to read that of course, you'll have to buy the book [16]. What are you waiting for?

[1] http://www.sitepoint.com/article/php-amp-mysql-1-installation/
[2] http://www.sitepoint.com/article/getting-started-mysql/
[3] http://www.sitepoint.com/article/mysql-3-getting-started-php/
[4] http://www.sitepoint.com/books/phpmysql4/samplechapters.php
[5] http://www.sitepoint.com/article/getting-started-mysql/
[6] http://www.sitepoint.com/article/getting-started-mysql/
[7] http://www.sitepoint.com/article/mysql-3-getting-started-php/
[8] http://www.sitepoint.com/article/mysql-3-getting-started-php/
[9] http://www.sitepoint.com/article/getting-started-mysql/
[10] http://www.sitepoint.com/article/getting-started-mysql/
[11] http://www.sitepoint.com/article/getting-started-mysql/
[12] http://www.sitepoint.com/article/mysql-3-getting-started-php/
[13] http://www.sitepoint.com/article/getting-started-mysql/
[14] http://www.php.net/manual/en/security.magicquotes.disabling.php
[15] http://www.sitepoint.com/forums/
[16] http://www.sitepoint.com/books/phpmysql4/