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 panelWe 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.
4.1. 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.
4.1.1. 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:
-
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.
-
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.
-
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.
-
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.
-
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.
4.1.2. 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.
4.1.3. 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.
4.1.3.1. 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(
).
4.1.3.2. 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.
4.1.3.3. 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.
4.1.4. 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.
| | | 3.10. More on SQL and MySQL | | 4.2. Formatting Results |
Copyright © 2003 O'Reilly & Associates. All rights reserved.
|