PHP —

Connecting to a MySQL Database

Tutorial 1 introduced the three tiers of a web database application. In this tutorial, we begin to bring the tiers together by developing application logic in the middle tier. We show the PHP scripting techniques to query the database tier and render HTML in a client-tier web browser.

In this section, we present the basics of connecting to and querying the winestore database using a simple query. The output is also simple: we use the HTML <pre> tag to reproduce the results in the same format in which they are returned from the database. The focus of this section is the DBMS interaction, not the presentation. Presentation is the subject of much of the remainder of this tutorial.

Opening and Using a Database Connection

In tutorial 3, we introduced the MySQL command interpreter. In PHP, there is no consolidated interface. Instead, a set of library functions are provided for executing SQL statements, as well as for managing result sets returned from queries, error handling, and setting efficiency options. We overview these functions here and show how they can be combined to access the MySQL DBMS.

Connecting to and querying a MySQL DBMS with PHP is a five-step process. Example 4-1 shows a script that connects to the MySQL DBMS, uses the winestore database, issues a query to select all the records from the wine table, and reports the results as preformatted HTML text. The example illustrates six of the key functions for connecting to and querying a MySQL database with PHP. Each function is prefixed with the string mysql_. We explain the function of this script in detail in this section.

Example 4-1. Connecting to a MySQL database with PHP

<!DOCTYPE HTML PUBLIC
                 "-//W3C//DTD HTML 4.0 Transitional//EN"
                 "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
  <title>Wines</title>
</head>
<body><pre>
<?php
   // (1) Open the database connection and use the winestore
   // database
   $connection = mysql_connect("localhost","fred","shhh");
   mysql_select_db("winestore", $connection);
   // (2) Run the query on the winestore through the
   //  connection
   $result = mysql_query ("SELECT * FROM
                          wine", $connection);
   // (3) While there are still rows in the result set,
   // fetch the current row into the array $row
   while ($row = mysql_fetch_row($result))
   {
     // (4) Print out each element in $row, that is,
     // print the values of the attributes
      for ($i=0; $i<mysql_num_fields($result); $i++)
         echo $row[$i] . " ";
      // Print a carriage return to neaten the output
      echo "\n";
   }
   // (5) Close the database connection
   mysql_close($connection);
?>
</pre>
</body>
</html>

The five steps of querying a database are numbered in the comments in Example 4-1, and they are as follows:

  1. Connect to the DBMS and use a database. Open a connection to the MySQL DBMS using mysql_connect( ). There are three parameters: the hostname of the DBMS server to use, a username, and a password. Once you connect, you can select a database to use through the connection with the mysql_select_db( ) function. In this example, we select the winestore database.

    Let's assume here that MySQL is installed on the same server as the scripting engine and therefore, we can use localhost as the hostname.

    The function mysql_connect( ) returns a connection handle. A handle is a value that can be used to access the information associated with the connection. As discussed in Step 2, running a query also returns a handle that can access results.

    To test this example-and all other examples in this tutorial that connect to the MySQL DBMS-replace the username fred and the password shhh with those you selected when MySQL was installed following the instructions in Appendix A. This should be the same username and password used throughout tutorial 3.

  2. Run the query. Let's run the query on the winestore database using mysql_query( ). The function takes two parameters: the SQL query itself and the DBMS connection to use. The connection parameter is the value returned from the connection in the first step. The function mysql_query( ) returns a result set handle resource; that is, a value that can retrieve the output-the result set-of the query in Step 3.

  3. Retrieve a row of results. The function mysql_fetch_row( ) retrieves one row of the result set, taking only the result set handle from the second step as the parameter. Each row is stored in an array $row, and the attribute values in the array are extracted in Step 4. A while loop is used to retrieve rows until there are no more rows to fetch. The function mysql_fetch_row( ) returns false when no more data is available.

  4. Process the attribute values. For each retrieved row, a for loop is used to print with an echo statement each of the attributes in the current row. Use mysql_num_fields( ) is used to return the number of attributes in the row; that is, the number of elements in the array. For the wine table, there are six attributes in each row: wine_id, wine_name, type, year, winery_id, and description.

    The function mysql_num_fields( ) takes as a parameter the result handle from Step 2 and, in this example, returns 6 each time it is called. The data itself is stored as elements of the array $row returned in Step 3. The element $row[0] is the value of the first attribute (the wine_id), $row[1] is the value of the second attribute (the wine_name), and so on.

    The script prints each row on a line, separating each attribute with a single space character. Each line is terminated with a carriage return using echo "\n" and Steps 3 and 4 are repeated.

  5. Close the DBMS connection using mysql_close( ), with the connection to be closed as the parameter.

The first 10 wine rows produced by the script in Example 4-1 are shown in Example 4-2. The results are shown marked up as HTML.

Example 4-2. Marked-up HTML output from the code shown in Example 4-1

<!DOCTYPE HTML PUBLIC
               "-//W3C//DTD HTML 4.0 Transitional//EN"
               "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
  <title>Wines</title>
</head>
<body><pre>
1 Archibald Sparkling 1997 1
2 Pattendon Fortified 1975 1
3 Lombardi Sweet 1985 2
4 Tonkin Sparkling 1984 2
5 Titshall White 1986 2
6 Serrong Red 1995 2
7 Mettaxus White 1996 2
8 Titshall Sweet 1987 3
9 Serrong Fortified 1981 3
10 Chester White 1999 3
...
</pre>
</body>
</html>

Other functions can be used to manipulate the database-in particular, to process result sets differently-and we discuss these later in this tutorial. However, the basic principles and practice are shown in the six functions we have used. These key functions are described in more detail in the next section.