Building a Database-Driven Web Site Using PHP and MySQL

Connecting to MySQL with PHP

Before we can get content out of our MySQL database for inclusion in our Web page, we must first know how to establish a connection to MySQL. Back in Part Two, we used a program called mysql that allowed us to make such a connection. PHP has no need of any special program, however; support for connecting to MySQL is built right into the language. The following PHP function call establishes the connection:

mysql_connect(<address>, <username>, <password>);

Where <address> is the IP address or hostname of the computer on which the MySQL server software is running ("localhost" if 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 Part Two.

You may or may not remember that functions in PHP usually return (output) a value when they are called. Don't worry if this doesn't ring any bells for you -- it's a detail that we glossed over when originally discussing functions. In addition to doing something useful when they are called, most functions output a value, and 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.

Since the MySQL server is a completely separate piece of software, we must consider the possibility that the server is unavailable, or inaccessible due to a network outage, or because the username/password combination you provided is not accepted by the server. In such cases, the mysql_connect function doesn't return a connection identifier (since 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 a @ symbol in front of the mysql_connect function. Many functions, including mysql_connect, automatically display ugly error messages when they fail. Placing a @ symbol in front of the function name tells the function to fail silently, allowing us to display our own, friendlier error message.

Next, we put an exclamation point in front of the $dbcnx variable in the condition of the if statement. The exclamation point 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 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 of a function that takes no parameters that we have encountered. 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, since in most cases the page will be unable to display any useful information without that connection.

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

mysql_select_db("jokes", $dbcnx);

Notice we use the $dbcnx variable containing the database connection identifier to tell the function what database connection to use. This parameter is actually optional. When it is omitted, the function will automatically use the link identifier for the last connection opened. This function returns true when successful and false if an error occurs. Once again, it is prudent to use an if statement to handle errors:

if (! @mysql_select_db("jokes") ) {
  echo( "<P>Unable to locate the joke " .
        "database at this time.</P>" );
  exit();
}

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

Performing SQL Queries with PHP

SitePoint.com is a fast growing Web Developer Community. Kevin Yank is the Editor of the SitePoint TechTimes, a fresh, technically oriented newsletter for the serious Webmaster.

Related Pages:

Building a Database-Driven Web Site Using PHP and MySQL