Publishing MySQL Data on the Web

[sivaji]

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 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'll see how to use these two new tools together to create a true database-driven Website!

A Look Back at First Principles

Before we leap forward, it's worth a brief look back to remind you 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, 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, on 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. On 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).

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, then spits it out dynamically as the nicely-formatted HTML page that the browser expects. With PHP, you can write the presentation aspects
of your site (the fancy graphics and page layouts) as "templates" in regular HTML.

At the points at which content belongs in those templates, you use some PHP code to connect to the MySQL database and—using SQL queries just like those you used to create a table of jokes in Chapter 2—retrieve and display some content in its place.

Just so it's clear and fresh in your mind, this is what will happen when someone visits a page on your database-driven Website:

1. The visitor's Web browser requests the Web page using a standard URL.
2. The Web server software (Apache, IIS, or whatever) recognizes that the requested file is a PHP
    script, so the server interprets the file using its PHP plug-in before responding to the page request.
3. Certain PHP commands (which you have yet to learn) 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 the now-familiar echo
    statement to output the content as part of the Web page.
6. The PHP plug-in 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 plug-in.

Connecting to MySQL with PHP

Before you can get 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, you used a program called mysql that allowed you to make such a connection from the command prompt. PHP has no need of
any special program, however; support for connecting to MySQL is built right into the language. The built-in function mysql_connect establishes the connection:

mysql_connect(address, username, password)

Here, address is the IP address or host name of the computer on which the MySQL server software is running ('localhost' if it's running on the same computer as the Web server software), and username and password are the same MySQL user name and password you used to connect to the MySQL server in
Chapter 2.

You may remember that functions in PHP usually return (output) a value when they're called. Don't worry if this doesn't ring any bells for you—it's a detail that I glossed over when I first discussed functions in Chapter 3. In addition to doing something useful when they are called, most functions output a value; that value may be stored in a variable for later use. The mysql_connect function shown above, for example, returns a number 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.

$dbcnx = mysql_connect('localhost', 'root', 'mypasswd');

As described above, the values of the three function parameters may differ for your MySQL server. What's important to see here is that the value returned by mysql_connect (which we'll call a connection identifier) is stored in a variable named $dbcnx.

As the MySQL server is a completely separate piece of software, we must consider the possibility that the server may be unavailable or inaccessible due to a network outage, or because the user  name/password combination you provided is not accepted by the server. In such cases, the  mysql_connect function doesn't return a connection identifier, as no connection is established; instead, it returns false. This allows us to react to such failures using an if statement:

$dbcnx = @mysql_connect('localhost', 'root', 'mypasswd');
if (!$dbcnx) {
echo '<p>Unable to connect to the ' .
'database server at this time.</p>' );
exit();
}

There are three new tricks in the above code fragment. First, we have placed an @ symbol in front of the mysql_connect function. Many functions, including mysql_connect, automatically display ugly error messages when they fail. Placing the @ symbol (also known as the error suppression operator) in front of the function name tells the function to fail silently, and allows us to display our own, friendlier error message. Next, we put an exclamation mark (!) in front of the $dbcnx variable in the condition of the if statement. The exclamation mark is the PHP negation operator, which basically flips a false value to true, or a true value to false. Thus, if the connection fails and mysql_connect returns false, !$dbcnx will evaluate to true, and cause the statements in the body of our if statement to be executed.

Alternatively, if a connection was made, the connection identifier stored in $dbcnx will evaluate to true (any number other than zero is considered "true" in PHP), so !$dbcnx will evaluate to false, and the statements in the if statement will not be executed.

The last new trick is the exit function, which is the first example that we've encountered of a function that can be called with no parameters. When called this way, all this function does is cause PHP to stop reading the page at this point. This is a good response to a failed database connection because in most cases the page will be unable to display any useful information without that connection.

As in Chapter 2, once a connection is established, the next step is to select the database with which you want to work. Let's say we want to work with the joke database we created in Chapter 2. The database we created was called ijdb. Selecting that database in PHP is just a matter of another function call:

mysql_select_db('ijdb', $dbcnx);

Notice we use the $dbcnx variable that contains the database connection identifier to tell the function which database connection to use. This parameter is actually optional. When it's omitted, the function will automatically use the link identifier for the last connection opened. 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 (!@mysql_select_db('ijdb')) {
exit('<p>Unable to locate the joke ' .
'database at this time.</p>');
}

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.

Another short cut I've used here is to call exit with a string parameter. When called with a parameter, exit works just like an echo statement, except that the script exits after the string is output. So, calling exit this way is equivalent to an echo statement followed by a call to exit with no parameters, which is what we used for mysql_connect above.

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