Web Database Applications with PHP, and MySQL

Chapter 4. Querying Web Databases

This chapter is the first of six that introduce practical web database application development. In Chapter 1, we introduced our case-study application, Hugh and Dave’s Online Wines. We use the winestore here to illustrate the basic principles and practice of building commonly used web database components.

In this chapter, we introduce the basics of connecting to the MySQL DBMS with PHP. We detail the key MySQL functions used to connect, query databases, and retrieve result sets, and we present the five-step process for dynamically serving data from a database. Queries that are driven by user input into an HTML <form> or through clicking on hypertext links are the subject of Chapter 5.

We introduce the following techniques in this chapter:

• Using the five-step web database querying approach to develop database-driven queries

• Coding a simple solution to produce HTML <pre> preformatted text

• Using the MySQL library functions for querying databases

• Handling MySQL DBMS errors

• Producing formatted output with the HTML <table> environment

• Using include files to modularize database code

• Adding multiple queries to a script and consolidating the results into one HTML presentation environment

• Performing simple calculations on database data

• Developing basic database-driven scripts incrementally and producing modular code encapsulated in functions

Our case study in this chapter is the front-page panel from the winestore that shows customers the Hot New Wines available at the winestore. The front page of the winestore is shown in Figure 4-1, and the panel is the section of the page that contains the list of the three newest wines that have been added to the database and reviewed by a wine expert.

Figure 4-1. The front page of the winestore, showing the front page panel

We begin by introducing the basic principles of web database querying. Our first examples use a simple approach to presenting result sets using the HTML <pre> preformatted text tag. We then build on this approach and introduce result presentation with the <table> environment. The panel itself is a complex case study, and we follow its development as natural join queries are introduced, conditional presentation of results included, and the HTML <table> environment used for more attractive presentation. We focus on iterative development, starting simply and progressively adding new functionality. The complete code for the front page of the winestore application is presented in Chapter 11.

For completeness, we conclude this chapter with a brief overview of how other DBMSs can be accessed and manipulated with PHP.

Connecting to a MySQL Database

Chapter 1 introduced the three tiers of a web database application. In this chapter, 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 chapter.

Opening and Using a Database Connection

In Chapter 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 book 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 Chapter 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 chapter. 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.

Essential Functions for Accessing MySQL with PHP

resource mysql_connect([string host ], [string username ], [string password ])

Establishes a connection to the MySQL DBMS. The function returns a connection resource handle on success that can be used to access databases through subsequent commands. Returns false on failure (error handling is discussed later in this section).

The command has three optional parameters, all of which—host, username, and password—are used in practice. The first permits not only the hostname, but also an optional port number; the default port for MySQL is 3306 (ports are discussed in more detail in Appendix B). However, when the DBMS runs on the same machine as the PHP scripting engine and the web server—and you have set up a database user that can access the DBMS from the local machine—the first parameter need only be localhost.

In Example 4-1, the function call:

mysql_connect("localhost", "fred", "shhh")

connects to the MySQL DBMS on the local machine with the username fred and a password of shhh. As discussed in the last section, you should replace these with the username and password values you chose in Appendix A and used in Chapter 3. If the connection is successful, the returned result is a connection resource handle that should be stored in a variable for use as a parameter to other MySQL functions.

This function needs to be called only once in a script, assuming you don’t close the connection (see mysql_close( ) , later in this section). Indeed, subsequent calls to the function in the same script with the same parameters—the same host, username, and password triple—don’t return a new connection. They return the same connection handle returned from the first successful call to the function.

int mysql_select_db (string database, [resource connection ])

Uses the specified database on a connection. In Example 4-1, the database winestore is used on the connection returned from mysql_connect( ). If the second parameter is omitted, the last connection opened is assumed, or an attempt is made to open a connection with mysql_connect( ) and no parameters. We caution against omitting the connection parameter.

resource mysql_query(string SQL_command, [resource connection ])

Runs the SQL statement SQL_command. In practice, the second argument isn’t optional and should be a connection handle returned from a call to mysql_connect( ). The function mysql_query( ) returns a resource—a result handle that can fetch the result set—on success, and false on failure.

In Example 4-1, the function call:

$result=mysql_query("SELECT * FROM wine", $connection)

runs the SQL query SELECT * FROM wine through the previously established DBMS connection resource $connection. The return value is assigned to $result, a result resource handle that is used as a parameter to mysql_fetch_row( ) to retrieve the data.

Tip

The query string passed to mysql_query( ) or mysql_unbuffered_query() doesn’t need to be terminated with a semicolon; the latter function is discussed later in this section.

If the second parameter to mysql_query( ) is omitted, PHP tries to use any open connection to the MySQL DBMS. If no connections are open, a call to mysql_connect( ) with no parameters is issued. In practice, the second parameter should be supplied.

array mysql_fetch_row(resource result_set )

Fetches the result set data one row at a time by using as a parameter the result handle result_set that was returned from an earlier mysql_query( ) function call. The results are returned as an array, and the elements of the array can then be processed with a loop statement. The function returns false when no more rows are available.

In Example 4-1, a while loop repeatedly calls the function and fetches rows into the array variable $row until there are no more rows available.

int mysql_num_fields(resource result_set )

Returns the number of attributes associated with a result set handle result_set. The result set handle is returned from a prior call to mysql_query( ).

This function is used in Example 4-1 to determine how many elements to process with the for loop that prints the value of each attribute. In practice, the function might be called only once per query and the returned result assigned to a variable that can be used in the for loop. This is possible since all rows in a result set have the same number of attributes. Avoiding repeated calls to DBMS functions where possible is likely to improve performance.

The array function count( ) can also be used to count the number of elements in an array.

int mysql_close([resource connection ])

Closes a MySQL connection that was opened with mysql_connect( ). The connection parameter is optional. If it is omitted, the most recently opened connection is closed.

As we discuss later, this function doesn’t really need to be called to close a connection opened with mysql_connect( ), because all connections are closed when a script terminates. Also, this function has no effect on persistent connections opened with mysql_pconnect( ) ; these connections stay open until they are unused for a specified period. We discuss persistent connections in the next section.

The functions we have described are a contrasting approach for DBMS access to the consolidated interface of the MySQL command line interpreter. mysql_connect( ) and mysql_close( ) perform equivalent functions to running and quitting the interpreter. The mysql_select_db( ) function provides the use database command, and mysql_query( ) permits an SQL statement to be executed. The mysql_fetch_row( ) and mysql_num_fields( ) functions manually retrieve a result set that’s automatically output by the interpreter.

More MySQL Functions in PHP

Web database applications can be developed that use only the six functions we have described. However, in many cases, additional functionality is required. For example, database tables sometimes need to be created, information about database table structure needs to be used in reporting or querying, and it is desirable to retrieve specific rows in a result set without processing the complete dataset.

Additional functions for interacting with a MySQL DBMS using PHP are the subject of this section. We have omitted functions that are used to report on insertions, deletions, and updates. These are discussed in Chapter 6.

Frequently used functions

int mysql_data_seek(resource result_set , int row )

This function retrieves only some results from a query. It allows retrieval from a result set to begin at a row other than the first row. For example, executing the function for a result_set with a row parameter of 10, and then issuing a mysql_fetch_row( ), mysql_fetch_array( ) , or mysql_fetch_object( ) , retrieves the tenth row of the result set.

This function can reduce communications between the database and middle tiers in an application.

The parameter result_set is the result resource handle returned from mysql_query( ). The function returns true on success and false on failure.

array mysql_fetch_array(resource result_set , [int result_type ])

This function is an extended version of mysql_fetch_row( ) that returns results into an associative array, permitting access to values in the array by their table attribute names.

Consider an example query on the wine table using the mysql_query( ) function:

$result=mysql_query("SELECT * FROM wine", $connection)

A row can then be retrieved into the array $row using:

$row=mysql_fetch_array($result)

After retrieving the row, elements of the array $row can be accessed by their attribute names in the wine table. For example, echo $row["wine_name"] prints the value of the wine_name attribute from the retrieved row. Attributes can also be accessed by their element numbers. For example, echo $row[1] also works.

There are three tricks to using mysql_fetch_array( ) :

• Even though an attribute might be referenced as customer.name in the SELECT statement, it must be referenced as $row["name"] in the associative array; this is a good reason to design databases so that attribute names are unique across tables. If attribute names are not unique, aliases can be used in the SELECT statement; we discuss this later in this chapter.

• Aggregates fetched with mysql_fetch_array( )—for example, SUM(cost)—are associatively referenced as $row["SUM(cost)"].

• NULL values are ignored when creating the returned array. This has no effect on associative access to the array but can change the numbering of the array elements for numeric access.

The second parameter to mysql_fetch_array( ), result_type, controls whether associative access, numeric access, or both are possible on the returned array. Because the default is MYSQL_BOTH, there is no reason to supply or change the parameter.

object mysql_fetch_object(resource result_set , [int result_type ])

This function is another alternative for returning results from a query. It returns an object that contains one row of results associated with the result_set handle, permitting access to values in an object by their table attribute names.

For example, after a query to SELECT * from wine, a row can be retrieved into the object $object using:

$object =mysql_fetch_object($result)

The attributes can then be accessed in $object by their attribute names. For example:

echo $object->wine_name

prints the value of the wine_name attribute from the retrieved row. Attributes can also be accessed by their element numbers. For example, echo $object->1 also works.

The second parameter to mysql_fetch_object( ) controls whether associative access, numeric access, or both are possible on the returned array. The default is MYSQL_BOTH, but MYSQL_ASSOC and MYSQL_NUM can also be specified.

int mysql_free_result(resource result_set )

This function frees the resources associated with a result_set handle. This process happens when a script terminates, so the function need be called only if repeated querying is performed in one script and MySQL memory use is a concern.

int mysql_num_rows(resource result_set )

This function returns the number of rows associated with the result_set query result resource handle. This function works only for SELECT queries; queries that modify a database should use mysql_affected_rows( ) , which is discussed in Chapter 6.

If the number of rows in a table is required but not the data itself, it is more efficient to run an SQL query of the form SELECT count(*) FROM table and retrieve the result, rather than running SELECT * FROM table and then using mysql_num_rows( ) to determine the number of rows in the table.

resource mysql_pconnect([string host:port ], [string user ], [string password ])

This function is a performance-oriented alternative to mysql_connect( ) that reuses open connections to the MySQL DBMS. The p in mysql_pconnect( ) stands for persistent , meaning that a connection to the DBMS stays open after a script terminates. Open connections are maintained as a pool that is available to PHP. When a call to mysql_pconnect( ) is made, a pooled connection is used in preference to creating a new connection. Using pooled connections saves the costs of opening and closing connections.

Tip

Whether persistency is faster in practice depends on the server configuration and the application. However, in general, for web database applications with many users running on a server with plenty of main memory, persistency is likely to improve performance.

This function need be called only once in a script. Subsequent calls to mysql_pconnect( ) in any script—with the same parameters—check the connection pool for an available connection. If no connections are available, a new connection is opened.

The function takes the same parameters and returns the same results as its non-persistent sibling mysql_connect( ). It returns a connection resource handle on success that can access databases through subsequent commands; it returns false on failure. The command has the same three optional parameters as mysql_connect( ).

Note

A connection opened with mysql_pconnect( ) can’t be closed with mysql_close( ) . It stays open until unused for a period of time. The timeout is a MySQL DBMS parameter—not a PHP parameter—and is set by default to five seconds; it can be adjusted with a command-line option to the MySQL DBMS script safe_mysqld. For example, to set the timeout to 10 seconds:

safe_mysqld --set-variable connect_timeout=10

resource mysql_unbuffered_query(string query , [resource connection ])

This function is available only in PHP 4.0.6 or later. The function executes a query without retrieving and buffering the result set. This is useful for queries that return large result sets or that are slow to execute. The advantage is that no resources are required to store a large result set, and the function returns before the SQL query is complete. In contrast, the function mysql_query( ) doesn’t return until the query is complete and the results have been buffered for subsequent retrieval.

The disadvantage of mysql_unbuffered_query( ) is that mysql_num_rows( ) can’t be called for the result resource handle, because the number of rows returned from the query isn’t known.

The function is otherwise identical to mysql_query( ).

Other functions

int mysql_change_user(string user , string password , [string database , [resource connection ]])

Changes the logged-in MySQL user to another user, using that user’s password for an optionally specified database and connection. If omitted, the current database and most recently opened connection are assumed. Returns false on failure and, if it does fail, the previous, successful connection stays current.

int mysql_create_db(string db , [resource connection ])

Creates a database named db using the connection resource returned from a mysql_connect( ) function call or the last-opened connection if the parameter is omitted.

int mysql_drop_db(string db , [resource connection ])

Drops a database named db using the connection resource returned from a mysql_connect( ) function call or the last-opened connection if the parameter is omitted.

object mysql_fetch_field(resource result_set , [int attribute_number ])

Returns as an object the metadata for each attribute associated with a result_set resource returned from a query function call. An optional attribute_number can be specified to retrieve the metadata associated with a specific attribute. However, repeated calls process the attributes one by one.

The properties of the object returned by the function are:

name

The attribute name

table

The name of the table that the attribute belongs to

max_length

The maximum length of the attribute

not_null

Set to 1 if the attribute can’t be NULL

primary_key

Set to 1 if the attribute forms part of a primary key

unique_key

Set to 1 if the attribute is a unique key

multiple_key

Set to 1 if the attribute is a nonunique key

numeric

Set to 1 if the attribute is a numeric type

blob

Set to 1 if the attribute is a BLOB type

type

The type of the attribute

unsigned

Set to 1 if the attribute is an unsigned numeric type

zerofill

Set to 1 if the numeric column is zero-filled

Example 4-3 is a script that uses the mysql_fetch_field() function to emulate most of the behavior of the SHOW COLUMNS or DESCRIBE commands discussed in Chapter 3. The code uses the same five-step query process discussed earlier, with the exception that mysql_fetch_field( ) is used in place of mysql_fetch_row( ). Sample output for the table wine is shown in Example 4-4. The same result could have been achieved by executing DESCRIBE WINE on the winestore database using mysql_query( ) and retrieving the results with mysql_fetch_object( ) .

This function also has other uses. For example, it can be used in validation—the subject of Chapter 7—to check whether the data entered by a user is longer than the maximum length of the database attribute. Indeed, a script can be developed that automatically performs basic validation based on the table structure.

Example 4-3. Using mysql_fetch_field( ) to describe the structure of a table

<!DOCTYPE HTML PUBLIC
               "-//W3C//DTD HTML 4.0 Transitional//EN"
               "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
  <title>Wine Table Structure</title>
</head>
<body><pre>
<?php
   // Open a connection to the DBMS
   $connection = mysql_connect("localhost","fred","shhh");

   mysql_select_db("winestore", $connection);

   // Run a query on the wine table in the
   // winestore database to retrieve one row
   $result = mysql_query ("SELECT * FROM wine LIMIT 1", 
                         $connection);
   
   // Output a header, with headers spaced by padding
   print str_pad("Field", 20) . 
         str_pad("Type", 14) . 
         str_pad("Null", 6) .
         str_pad("Key", 5) . 
         str_pad("Extra", 12) . "\n";

   // for each of the attributes in the result set
   for($i=0;$i<mysql_num_fields($result);$i++)
   {
      // Get the meta-data for the attribute
      $info = mysql_fetch_field ($result);

      // Print the attribute name
      print str_pad($info->name, 20);

      // Print the data type
      print str_pad($info->type, 6);

      // Print a "(", the field length, and a ")" e.g.(2)
      print str_pad("(" . $info->max_length . ")", 8);

      // Print out YES if attribute can be NULL
      if ($info->not_null != 1)
          print " YES ";
      else
         print  "     ";

      // Print out selected index information
      if ($info->primary_key == 1)
         print " PRI ";
      elseif ($info->multiple_key == 1)
         print " MUL ";
      elseif ($info->unique_key == 1)
         print " UNI ";

      // If zero-filled, print this
      if ($info->zerofill)
         print " Zero filled";

      // Start a new line
      print "\n";
   }

   // Close the database connection
   mysql_close($connection);
?>
</pre>
</body>
</html>

Example 4-4. HTML output of the DESCRIBE WINE emulation script 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>Wine Table Structure</title>
</head>
<body><pre>
Field               Type          Null  Key  Extra       
wine_id             int   (1)           PRI 
wine_name           string(9)           MUL 
type                string(9)          
year                int   (4)          
winery_id           int   (1)           MUL 
description         blob  (0)      YES 
</pre>
</body>
</html>

resource mysql_list_tables(string database , [resource connection ])

Returns a result set resource handle that can be used as input to mysql_tablename( ) to list the names of tables in a database accessed through a connection. If the connection is omitted, the last-opened connection is assumed.

string mysql_tablename(resource result , int table_number )

Used in combination with mysql_list_tables( ) to produce a list of tables in a database. Returns the name of the table indexed by the numeric value table_number using a result resource returned from the mysql_list_tables( ) function.

The number of tables in a database can be determined by calling mysql_num_rows( ) with the result resource handle returned from mysql_list_tables( ) as a parameter.

Functions to avoid

Several MySQL functions shouldn’t be used in practice:

• The functions of mysql_fetch_field( ) are also available in the non-object-based alternatives mysql_fetch_length( ), mysql_field_flags( ), mysql_field_name( ), mysql_field_len( ), mysql_field_table( ), and mysql_field_type( ); as these functions are almost a complete subset of mysql_fetch_field( ), we don’t describe them here.

• The function mysql_result( ) is a slower alternative to fetching and processing a row with mysql_fetch_row( ) or mysql_fetch_array( ) and shouldn’t be used in practice.

• mysql_fetch_assoc( ) fetches a row of results as an associative array only, providing half the functionality of mysql_fetch_array( ). The other half—fetching into an array accessed by numeric index—is provided by mysql_fetch_row( ). Since mysql_fetch_array( ) provides both sets of functionality—or can provide the same functionality by passing through MYSQL_ASSOC as the second parameter—it should be used instead.

• mysql_field_seek( ) can seek to a specific field for a subsequent call to mysql_fetch_field( ), but this is redundant because the field number can be supplied directly to mysql_fetch_field( ) as the optional second parameter.

• mysql_db_query( ) combines the functionality of mysql_select_db( ) and mysql_query( ). This function has been deprecated in recent releases of PHP.

Error Handling of MySQL Database Functions

Database functions can fail. There are several possible classes of failure, ranging from critical—the DBMS is inaccessible or a fixed parameter is incorrect to recoverable, such as a password being entered incorrectly by the user.

The PHP interface functions to MySQL support two error-handling functions for detecting and reporting errors:

int mysql_errno(resource connection )

Returns the error number of the last error on the connection resource

string mysql_error(resource connection )

Returns a descriptive string of the last error on the connection resource

Example 4-5 shows the script illustrated earlier in Example 4-1 with additional error handling. We have deliberately included an error where the name of the database winestore is misspelled as “winestor”. The error handler is a function, showerror( ) , that—with the database name error—prints a phrase in the format:

Error 1049 : Unknown database 'winestor'

The error message shows both the numeric output of mysql_errorno( ) and the string output of mysql_error( ) . The die( ) function outputs the message and then gracefully ends the script.

Warning

The functions mysql_query( ) and mysql_unbuffered_query( ) return false only on failure; that is, when a query is incorrectly formed and can’t be executed.

A query that executes but returns no results still returns a result resource handle. However, a subsequent call to mysql_num_rows( ) reports no rows in the result set.

The mysql_connect( ) and mysql_pconnect( ) functions don’t set either the error number or error string on failure and so must be handled manually. This custom handling can be implemented with a die( ) function call and an appropriate text message, as in Example 4-5.

Example 4-5. Querying a database with error handling

<!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

   function showerror(  )
   {
      die("Error " . mysql_errno(  ) . " : " . mysql_error(  ));
   }

   // (1) Open the database connection
   if (!($connection = @ mysql_connect("localhost",
                                       "fred","shhh")))
      die("Could not connect");

   // NOTE : 'winestore' is deliberately misspelt to 
   // cause an error
   if (!(mysql_select_db("winestor", $connection)))
      showerror(  );

   // (2) Run the query on the winestore through the
   //  connection
   if (!($result = @ mysql_query ("SELECT * FROM wine",
                                   $connection)))
      showerror(  );

   // (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
   if (!mysql_close($connection))
      showerror(  );
?>
</pre>
</body>
</html>

The MySQL error-handling functions should be used with the @ operator that suppresses default output of error messages by the PHP script engine. Omitting the @ operator produces messages that contain both the custom error message and the default error message produced by PHP. Consider an example where the string localhost is misspelled, and the @ operator is omitted:

if (!($connection = mysql_connect("localhos",
                                  "fred",:"shhh") ))
   die("Could not connect");

This fragment outputs the following error message that includes both the PHP error and the custom error message:

Warning:  MySQL Connection Failed: Unknown MySQL Server 
Host 'localhos' (0) in Example 4-5.php on line 42

Could not connect

Tip

Don’t forget to add an @ operator as the prefix to any function call that is handled manually with a custom error handler. The @ operator prevents PHP from issuing its own internal error message.