Article
About the Author
Kevin Yank
Kevin began developing for the Web in 1995 and is a highly respected technical author. He wrote Build your own Database Driven Website using PHP and MySQL, a practical step-by-step guide published by SitePoint, and he's co-author of the SitePoint Tech Times, a bi-weekly newsletter for technically-minded web developers. Kev believes that any good webmaster should have seen at least one episode of MacGyver.
Build Your Own Database Driven Web Site Using PHP & MySQL, Part 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 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.
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), 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, 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 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, 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 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.