Querying a MySQL Database with PHP

The reason for using PHP as an interface to MySQL is to format the results of SQL queries in a form visible in a web page. As long as you can log in to your MySQL installation using your username and password, you can also do so from PHP. However, instead of using MySQL’s command line to enter instructions and view output, you will create query strings that are passed to MySQL. When MySQL returns its response, it will come as a data structure that PHP can recognize instead of the formatted output you see when you work on the command line. Further PHP commands can retrieve the data and format it for the web page.

The Process

The process of using MySQL with PHP is:

  1. Connect to MySQL.

  2. Select the database to use.

  3. Build a query string.

  4. Perform the query.

  5. Retrieve the results and output them to a web page.

  6. Repeat Steps 3 through 5 until all desired data has been retrieved.

  7. Disconnect from MySQL.

We’ll work through these sections in turn, but first it’s important to set up your login details in a secure manner so people snooping around on your system have trouble getting access to your database.

Creating a Login File

Most websites developed with PHP contain multiple program files that will require access to MySQL and will therefore need your login and password details. So, it’s sensible to create a single file to store these and then include that file wherever it’s needed. Example 10-1 shows such a file, which I’ve called login.php. Type it in, replacing the username and password values with the actual values you use for your MySQL database, and save it to the web development directory you set up in Chapter 2. We’ll be making use of the file shortly. The hostname localhost should work as long as you’re using a MySQL database on your local system, and the database publications should work if you’re typing in the examples we’ve used so far.

Example 10-1. The login.php file
<?php // login.php
$db_hostname = 'localhost';
$db_database = 'publications';
$db_username = 'username';
$db_password = 'password';
?>

The enclosing <?php and ?> tags are especially important for the login.php file in Example 10-1, because they mean that the lines between can be interpreted only as PHP code. If you were to leave them out and someone were to call up the file directly from your website, it would display as text and reveal your secrets. However, with the tags in place, all they will see is a blank page. The file will correctly include in your other PHP files.

The $db_hostname variable will tell PHP which computer to use when connecting to a database. This is required because you can access MySQL databases on any computer connected to your PHP installation, and that potentially includes any host anywhere on the Web. However, the examples in this chapter will be working on the local server, so in place of specifying a domain such as mysql.myserver.com, you can just use the word localhost (or the IP address 127.0.0.1).

The database we’ll be using, $db_database, is the one called publications, which you probably created in Chapter 8. Alternatively, you can use the one you were provided with by your server administrator (in which case you’ll have to modify login.php accordingly).

The variables $db_username and $db_password should be set to the username and password that you have been using with MySQL.

Note

Another benefit of keeping these login details in a single place is that you can change your password as frequently as you like and there will be only one file to update when you do, no matter how many PHP files access MySQL.

Connecting to MySQL

Now that you have the login.php file saved, you can include it in any PHP files that will need to access the database by using the require_once statement. This has been chosen in preference to an include statement, as it will generate a fatal error if the file is not found. And believe me, not finding the file containing the login details to your database is a fatal error.

Also, using require_once instead of require means that the file will be read in only when it has not previously been included, which prevents wasteful duplicate disk accesses. Example 10-2 shows the code to use.

Example 10-2. Connecting to a MySQL server
<?php
require_once 'login.php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);

if (!$db_server) die("Unable to connect to MySQL: " . mysql_error());
?>

This example runs PHP’s mysql_connect function, which requires three parameters, the hostname, username, and password of a MySQL server. Upon success it returns an identifier to the server; otherwise, FALSE is returned. Notice that the second line uses an if statement with the die function, which does what it sounds like and quits from PHP with an error message if $db_server is not TRUE.

The die message explains that it was not possible to connect to the MySQL database, and—to help identify why this happened—includes a call to the mysql_error function. This function outputs the error text from the last called MySQL function.

The database server pointer $db_server will be used in some of the following examples to identify the MySQL server to be queried. Using identifiers this way, it is possible to connect to and access multiple MySQL servers from a single PHP program.

Note

The die function is great for when you are developing PHP code, but of course you will want more user-friendly error messages on a production server. In this case, you won’t abort your PHP program, but rather will format a message that will be displayed when the program exits normally, such as:

function mysql_fatal_error($msg)
{
    $msg2 = mysql_error();
    echo <<< _END
We are sorry, but it was not possible to complete
the requested task. The error message we got was:

    <p>$msg: $msg2</p>

Please click the back button on your browser
and try again. If you are still having problems,
please <a href="mailto:admin@server.com">email
our administrator</a>. Thank you.
_END;
}

Having successfully connected to MySQL, you are now ready to select the database that you will be using. Example 10-3 shows how to do this.

Example 10-3. Selecting a database
<?php
mysql_select_db($db_database)
    or die("Unable to select database: " . mysql_error());
?>

The command to select the database is mysql_select_db. Pass it the name of the database you want and the server to which you connected. As with the previous example, a die statement has been included to provide an error message and explanation, should the selection fail—the only difference is that there is no need to retain the return value from the mysql_select_db function, as it simply returns either TRUE or FALSE. Therefore, the PHP or statement was used, which means, “If the previous command failed, do the following.” Note that for the or to work, there must be no semicolon at the end of the first line of code.

Sending a query to MySQL from PHP is as simple as issuing it using the mysql_query function. Example 10-4 shows you how to use this function.

Example 10-4. Querying a database
<?php
$query = "SELECT * FROM classics";
$result = mysql_query($query);

if (!$result) die ("Database access failed: " . mysql_error());
?>

First, the variable $query is set to the query to be made. In this case it is asking to see all rows in the table classics. Note that, unlike when using MySQL’s command line, no semicolon is required at the tail of the query. This is because the mysql_query function is used to issue a complete query, and cannot be used to query by sending multiple parts, one at a time. Therefore, MySQL knows the query is complete and doesn’t look for a semicolon.

This function returns a result that we place in the variable $result. Having used MySQL at the command line, you might think that the contents of $result will be the same as the result returned from a command-line query, with horizontal and vertical lines, and so on. However, this is not the case with the result returned to PHP. Instead, upon success, $result will contain a resource that can be used to extract the results of the query. You’ll see how to extract the data in the next section. Upon failure, $result contains FALSE. So, the example finishes by checking $result. If it’s FALSE, it means that there was an error and the die command is executed.

Once you have a resource returned from the mysql_query function, you can use it to retrieve the data you want. The simplest way to do this is to fetch the cells you want, one at a time, using the mysql_result function. Example 10-5 combines and extends the previous examples into a program that you can type in and run yourself to retrieve the returned results. I suggest that you save it in the same folder as login.php and give it the name query.php.

Example 10-5. Fetching results one cell at a time
<?php // query.php
require_once 'login.php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);

if (!$db_server) die("Unable to connect to MySQL: " . mysql_error());

mysql_select_db($db_database)
    or die("Unable to select database: " . mysql_error());

$query = "SELECT * FROM classics";
$result = mysql_query($query);

if (!$result) die ("Database access failed: " . mysql_error());

$rows = mysql_num_rows($result);

for ($j = 0 ; $j < $rows ; ++$j)
{
    echo 'Author: '   . mysql_result($result,$j,'author')   . '<br />';
    echo 'Title: '    . mysql_result($result,$j,'title')    . '<br />';
    echo 'Category: ' . mysql_result($result,$j,'category') . '<br />';
    echo 'Year: '     . mysql_result($result,$j,'year')     . '<br />';
    echo 'ISBN: '     . mysql_result($result,$j,'isbn')     . '<br /><br />';
}
?>

The final 10 lines of code are the new ones, so let’s look at them. They start by setting the variable $rows to the value returned by a call to mysql_num_rows. This function reports the number of rows returned by a query.

Armed with the row count, we enter a for loop that extracts each cell of data from each row using the mysql_result function. The parameters supplied to this function are the resource $result, which was returned by mysql_query, the row number $j, and the name of the column from which to extract the data.

The results from each call to mysql_result are then incorporated within echo statements to display one field per line, with an additional line feed between rows. Figure 10-1 shows the result of running this program.

The output from the query.php program in
Figure 10-1. The output from the query.php program in Example 10-5

As you may recall, we populated the classics table with five rows in Chapter 8, and indeed, five rows of data are returned by query.php. But, as it stands, this code is actually extremely inefficient and slow, because a total of 25 calls are made to the function mysql_result in order to retrieve all the data, a single cell at a time. Luckily, there is a much better way of retrieving the data: getting a single row at a time using the mysql_fetch_row function.

Note

In Chapter 9, I talked about First, Second, and Third Normal Form. You may have noticed that the classics table doesn’t satisfy these, because both author and book details are included within the same table. That’s because we created this table before encountering normalization. However, for the purposes of illustrating access to MySQL from PHP, reusing this table avoids the hassle of typing in a new set of test data, so we’ll stick with it for the time being.

Fetching a row

It was important to show how you can fetch a single cell of data from MySQL, but fetching a row is a much more efficient method. Replace the for loop of query.php (in Example 10-5) with the new loop in Example 10-6, and you will find that you get exactly the same result that was displayed in Figure 10-1.

Example 10-6. Replacement for loop for fetching results one row at a time
<?php
for ($j = 0 ; $j < $rows ; ++$j)
{
    $row = mysql_fetch_row($result);
    echo 'Author: ' .     $row[0] . '<br />';
    echo 'Title: ' .      $row[1] . '<br />';
    echo 'Category: ' .   $row[2] . '<br />';
    echo 'Year: ' .       $row[3] . '<br />';
    echo 'ISBN: ' .       $row[4] . '<br /><br />';
}
?>

In this modified code, only one-fifth as many calls are made to a MySQL-calling function (a full 80 percent less), because each row is fetched in its entirety using the mysql_fetch_row function. This returns a single row of data in an array, which is then assigned to the variable $row.

All that’s necessary then is to reference each element of the array $row in turn (starting at an offset of zero). $row[0] contains the author data, $row[1] the title, and so on, because each column is placed in the array in the order in which it appears in the MySQL table. Also, by using mysql_fetch_row instead of mysql_result, you use substantially less PHP code and achieve much faster execution time, due to simply referencing each item of data by offset rather than by name.

When you have finished using a database, you should close the connection. This is done by issuing the command in Example 10-7.

Example 10-7. Closing a MySQL server connection
<?php
mysql_close($db_server);
?>

We have to pass the identifier returned by mysql_connect back in Example 10-2, which we stored in the variable $db_server.

Note

All database connections are automatically closed when PHP exits, so it doesn’t matter that the connection wasn’t closed in Example 10-5. But in longer programs, where you may continually open and close database connections, you are strongly advised to close each one as soon as you are done accessing it.