Team LiB
Previous Section Next Section

PHP Overview

The basic function of PHP is to interpret a script to produce a Web page that is sent to a client. The script typically contains a mix of HTML and executable code. The HTML is sent literally to the client, whereas the PHP code is executed and replaced by whatever output it produces. Consequently, the client never sees the code; it sees only the resulting HTML page. [1]

[1] The PHP scripts developed in this chapter generate pages that are valid as XHTML, not just as HTML. See "Writing Web Output," in Chapter 7, "Writing MySQL Programs Using Perl DBI," for a brief description of XHTML.

When PHP begins reading a file, it simply copies whatever it finds there to the output, under the assumption that the contents of the file represent literal text, such as HTML content. When the PHP interpreter encounters a special opening tag, it switches from text copy mode to PHP code mode and starts interpreting the file as PHP code to be executed. The interpreter switches from code mode back to text mode when it sees another special tag that signals the end of the code. This allows you to mix static text (the HTML part) with dynamically generated results (output from the PHP code part) to produce a page that varies depending on the circumstances under which it is called. For example, you might use a PHP script to process the result of a form into which a user has entered parameters for a database search. Depending on what the user types, the search parameters may be different each time the form is submitted, so when the script searches for and displays the information the user requested, each resulting page will be different.

Let's see how PHP works beginning with an extremely simple script:

<html>
<body>
<p>hello, world</p>
</body>
</html>

This script is in fact so simple that it contains no PHP code! "What good is that?," you ask. That's a reasonable question. The answer is that it's sometimes useful to set up a script containing just the HTML framework for the page you want to produce and then to add the PHP code later. This is perfectly legal, and the PHP interpreter has no problem with it.

To include PHP code in a script, distinguish it from the surrounding text with the special opening and closing tags: <?php and ?>. When the PHP interpreter encounters the opening <?php tag, it switches from text mode to PHP code mode and treats whatever it finds as executable code until it sees the closing ?> tag. The code between the tags is interpreted and replaced by its output. The previous example could be rewritten to include a small section of PHP code like this:

<html>
<body>
<p><?php print ("hello, world"); ?></p>
</body>
</html>

In this case, the code part is minimal, consisting of a single line. When the code executes, it produces the output hello, world, which becomes part of the output sent to the client's browser. Thus, the Web page produced by this script is equivalent to the one produced by the preceding example, where the script consisted entirely of HTML.

You can use PHP code to generate any part of a Web page. We've already seen one extreme, in which the entire script consists of literal HTML and contains no PHP code. The other extreme is for the HTML to be produced completely from within code mode:

<?php
print ("<html>\n");
print ("<body>\n");
print ("<p>hello, world</p>\n");
print ("</body>\n");
print ("</html>\n");
?>

These three examples demonstrate that PHP gives you a lot of flexibility in how you produce output. PHP leaves it up to you to decide whatever combination of HTML and PHP code is appropriate. PHP is also flexible in that you don't need to put all your code in one place. You can switch between text mode and PHP code mode throughout the script however you please, as often as you want.

PHP allows tag styles other than the <?php and ?> style that is used for examples in this chapter. See Appendix I for a description of the tag styles that are available and instructions on enabling them.

Standalone PHP Scripts

The example scripts in this chapter are written with the expectation that they will be invoked by a Web server to generate a Web page. However, if you have a standalone version of PHP, you can use it to execute PHP scripts from the command line. Suppose that you have a script named hello.php that looks like this:

<?php print ("hello, world\n"); ?>

To execute the script from the command line yourself, use this command:

% php hello.php
hello, world

This is sometimes useful when you're working on a script, because you can see right away whether it has syntax errors or other problems without having to request the script from a browser each time you make a change. (For this reason, you may want to build a standalone version of PHP even if normally you use it as a module from within Apache.)

On Unix, you can make a PHP script directly executable (just like a shell or Perl script) by adding to it a #! line at the beginning that names the pathname to PHP. Suppose that PHP is installed in the /usr/local/bin directory. You can modify the script to look like this:

#! /usr/local/bin/php -q
<?php print ("hello, world\n"); ?>

Make the script executable with chmod +x, and you can invoke it as follows:

% chmod +x hello.php
% ./hello.php
hello, world


If all that PHP provided was the capability to produce what is essentially static HTML by means of print statements, it wouldn't be very useful. Where PHP's power comes in is through its capability to generate dynamic output that can very from one invocation of a script to the next. The next script demonstrates this. It's still relatively short, but a bit more substantial than the previous examples. It shows how easily you can access a MySQL database from PHP and use the results of a query in a Web page. The following script was presented very briefly in Chapter 5. It forms a simple basis for a home page for the Historical League Web site. As we go on, we'll make the script a bit more elaborate, but for now all it does is display a short welcome message and a count of the current League membership:

<html>
<head>
<title>U.S. Historical League</title>
</head>
<body bgcolor="white">
<p>Welcome to the U.S. Historical League Web Site.</p>
<?php
# USHL home page

require_once "DB.php";

$conn =& DB::connect ("mysql://sampadm:secret@localhost/sampdb");
if (DB::isError ($conn))
    exit ();
$result =& $conn->query ("SELECT COUNT(*) FROM member");
if (DB::isError ($result))
    exit ();
if ($row =& $result->fetchRow ())
    print ("<p>The League currently has " . $row[0] . " members.</p>");
$result->free ();
$conn->disconnect ();
?>
</body>
</html>

The welcome message is just static text, so it's easiest to write it as literal HTML. The membership count, on the other hand, is dynamic and changes from time to time, so it must be determined on the fly by querying the member table in the sampdb database.

The text of the code within the opening and closing script tags performs a simple task:

  1. It opens a connection to the MySQL server and makes the sampdb database the default database.

  2. It sends a query to the server to determine how many members the Historical League has at the moment (assessed as the number of rows in the member table).

  3. It uses the query result to construct a message containing the membership count, and then disposes of the result set.

  4. It closes the connection to the MySQL server. This step actually is optional. If you don't close the connection, PHP closes it when the script terminates.

If an error occurs at any point during this process, the script simply exits without producing any further output. It doesn't display any error message because that's likely simply to be confusing to people visiting the Web site. [2]

[2] If you generate an entire Web page by means of PHP code, exiting on an error without producing any output at all, you're likely to annoy visitors to your site, because some browsers will display a "this page contained no data" dialog box that must be dismissed. It's better in this case to display a page containing at least a message indicating that the request could not be satisfied.

This script can be found as a file named index.php in the phpapi/ushl directory of the sampdb distribution. Change the connection parameters as necessary, install a copy of it as index.php in the ushl directory of your Web server's document tree, and request it from your browser using either of these URLs (changing the hostname to that of your own Web server):

http://www.snake.net/ushl/
http://www.snake.net/ushl/index.php

Let's break down the script into pieces to see how it works. The first step is to pull in the DB module so that the script has access to the interface that the module provides:

require_once "DB.php";

The require_once statement tells PHP to read in the code from the main DB module source file, DB.php. For this to work properly, the directory where DB.php is installed must be in PHP's include path value. For information on including files and setting the include path, see "Using Functions and Include Files" later in this chapter.

Next, connect to the server using DB::connect():

$conn =& DB::connect ("mysql://sampadm:secret@localhost/sampdb");
if (DB::isError ($conn))
    exit ();

The argument to DB::connect() is a string called a "data source name." It looks something like a URL and contains all the parameters needed for DB to determine which driver to use and how the driver should connect to the database server. The general DSN syntax takes this form:

driver://user_name:password@host_name/db_name

For our simple script, the DSN specifies the use of the mysql module. If you are using PHP 5 and want to use the mysqli driver instead, just change mysql to mysqli. The DSN also indicates that the MySQL server host is localhost and that the account username and password are sampdb and secret. The last part of the DSN specifies which database to select as the default database.

DB::connect() returns either a DB object representing a successful connection or a DB_Error object if the connection attempt failed. The next statement determines the status of the connection attempt and calls exit() to terminate immediately if a problem occurred.

Note that the statement that calls DB::connect() assigns the return value to the $conn variable using the =& assignment operator rather than the = operator. =& assigns a reference to the object created by DB::connect(). Using = would cause a copy of the object to be made, which would result in an unnecessary extra object instance.

Perhaps it makes you nervous that the name and password are embedded in the script for all to see. Well, it should. It's true that the name and password don't appear in the resulting Web page that is sent to the client, because the script's contents are replaced by its output. Nevertheless, if the Web server becomes misconfigured somehow and fails to recognize that your script needs to be processed by PHP, it will send your script as plain text, and your connection parameters will be exposed. We'll deal with this problem in the section "Using Functions and Include Files."

The connection object returned by DB::connect() becomes the means for further interaction with the MySQL server, such as issuing SQL statements to be executed. That is in fact the next step for the script. It invokes the connection object's query() object to send a member-counting query to the server, extracts the result, displays it, and frees the result set:

$result =& $conn->query ("SELECT COUNT(*) FROM member");
if (DB::isError ($result))
    exit ();
if ($row =& $result->fetchRow ())
    print ("<p>The League currently has " . $row[0] . " members.</p>");
$result->free ();

The query() method sends the query to the server to be executed. It can return two kinds of objects, either a DB_result object representing the query result, or a DB_Error object if the query was illegal or couldn't be executed for some reason. Here too the statement uses the =& assignment operator to obtain a reference to the object rather than a new copy of it. (Note that the query string contains no terminating semicolon character or \g or \G sequence, in contrast to the way you issue statements from within the mysql program.)

If the return value represents an error, the script terminates. Otherwise, it represents an object that we can use to obtain information about the result set. For the query shown, the result set consists of a single row with a single column value representing the membership count. To get this value, the script invokes the $result object's fetchRow() method to fetch the row as an array. Then it assigns the row to the variable $row, and accesses its first element, $row[0], which also happens to be its only element.

After processing the result set, we free it by invoking the free() method of the result object. This call is included for completeness. It actually isn't necessary here because PHP automatically releases any active result sets when a script terminates. free() is beneficial primarily in scripts that execute very large statements or a large number of statements, where its use helps prevent an excessive amount of memory from being used.

Variables in PHP

In PHP you can make variables spring into existence simply by using them. Our home page script uses three variables, $conn, $result, and $row, none of which are declared anywhere. (There are contexts in which you do declare variables, such as when you reference a global variable inside a function, but we'll get to that later.)

Variables are signified by an identifier preceded by a dollar sign ('$'). This is true no matter what kind of value the variable represents, although for arrays and objects you tack on some extra stuff to access individual elements of the value. If a variable $x represents a single scalar value, such as a number or a string, you access it as just $x. If $x represents an array with numeric indices, you access its elements as $x[0], $x[1], and so on. If $x represents an array with associative indices such as "yellow" or "large", you access its elements as $x["yellow"] or $x["large"]. (PHP arrays can even have both numeric and associative elements. For example, $x[1] and $x["large"] both can be elements of the same array.) If $x represents an object, it has properties that you access as $x->property_name. For example, $x->yellow and $x->large may be properties of $x. Numbers are not legal as property names, so $x->1 is not a valid construct in PHP.


Using Functions and Include Files

PHP scripts differ from DBI scripts in that PHP scripts are located within your Web server document tree, whereas DBI scripts typically are located in a cgi-bin directory that's located outside of the document tree. This brings up a security issue: A server misconfiguration error can cause pages located within the document tree to leak out as plain text to clients. This means that usernames and passwords for establishing connections to the MySQL server are at a higher risk of being exposed to the outside world if they are used in a PHP script than in a DBI script.

Our initial Historical League home page script is subject to this problem because it contains the literal values of the MySQL username and password. Let's move these connection parameters out of the script using two of PHP's capabilities: functions and include files. We'll write a function sampdb_dsn() that returns a DSN string appropriate for establishing a connection, and put that function in an include filea file that is not part of our main script but that can be referenced from it. This approach has certain advantages:

  • It's easier to write connection establishment code. We can write out the connection parameters once in the sampdb_dsn() helper function, not in every individual script that needs to connect. Hiding details like this tends to make scripts more understandable, because you can concentrate on the unique aspects of each script without being distracted by common connection setup code.

  • The include file can be used by multiple scripts. This promotes code reusability and makes code more maintainable. It also allows global changes to be made easily to every script that accesses the file. For example, if we move the sampdb database from localhost to boa.snake.net, we don't need to change a bunch of individual scripts. Instead, we just change the hostname parameter stored in the include file where the sampdb_dsn() function is defined.

  • The include file can be moved outside of the Apache document tree. This means that clients cannot request the include file directly from their browsers, so its contents cannot be exposed to them even if the Web server becomes misconfigured. Using an include file is a good strategy for hiding any kind of sensitive information that you don't want to be sent offsite by your Web server. However, although this is a security improvement, don't be lulled into thinking that it makes the name and password secure in all senses. Other users who have login accounts on the Web server host (and thus have access to its filesystem) might be able to read the include file directly unless you take some precautions. The section "Connecting to the MySQL Server from Web Scripts," in Chapter 7, "Writing MySQL Programs Using Perl DBI," has some notes that pertain to installing DBI configuration files so as to protect them from other users. Similar precautions apply to the use of PHP include files.

To use include files, you need to have a place to put them, and you need to tell PHP to look for them. If your system already has such a location, you can use that. If not, use the following procedure to establish an include file location:

1.
Create a directory outside of the Web server document tree in which to store PHP include files. I use /usr/local/apache/lib/php, which is not within my document tree, /usr/local/apache/htdocs.

2.
Include files can be accessed from scripts by full pathname or, if you set up PHP's search path, by just their basenames (the last component of the pathname). The latter approach is more convenient because PHP will find the file for us. The search path used by PHP when searching for include files is controlled by the value of the include_path configuration setting in the PHP initialization file, php.ini. Find this file on your system (mine is installed in /usr/local/lib), and locate the include_path line. If it has no value, set it to the full pathname of your new include directory:

include_path = "/usr/local/apache/lib/php"

If include_path already has a value, add the new directory to that value:

include_path = "current_value:/usr/local/apache/lib/php"

For Unix, directories listed in include_path should be separated by colon characters, as shown. For Windows, use semicolons instead.

After modifying php.ini, restart Apache so that your changes take effect.

Use of PHP include files is somewhat analogous to the use of C header files. For example, the way that PHP will look for them in several directories is similar to the way the C preprocessor looks in multiple directories for C header files.

3.
Create the include file that you want to use and put it into the include directory. The file should have some distinctive name; we'll use sampdb.php. This file eventually will contain several functions, but to start with, it need contain only the sampdb_dsn() function, as shown in the following listing:

<?php
# sampdb.php - common functions for sampdb PHP scripts

# Retrieve the data source name for connecting to the MySQL server and
# selecting the sampdb database using our top-secret name and password

# To use the original MySQL module, $driver should be "mysql".
# To use the "MySQL improved" module, $driver should be "mysqli".

function sampdb_dsn ()
{
    $driver = "mysql";
    $host_name = "localhost";
    $user_name = "sampadm";
    $password = "secret";
    $db_name = "sampdb";
    return ("$driver://$user_name:$password@$host_name/$db_name");
}
?>

The sampdb_dsn() function constructs a data source name that can be passed to DB::connect() to connect to the database server. For example, we could use sampdb_dsn() like this:

$conn =& DB::connect (sampdb_dsn ());
if (DB::isError ($conn))
    exit ();

Observe that the PHP code in the sampdb.php file is bracketed within <?php and ?> script tags. That's because PHP begins reading include files in text copy mode. If you omit the tags, PHP will send out the file as plain text rather than interpreting it as PHP code. (That's just fine if you intend the file to produce literal HTML, but if you want its contents to be executed, you must enclose the PHP code within script tags.)

4.
To reference the include file from a script, use one of the following statements:

include "sampdb.php";
require "sampdb.php";
include_once "sampdb.php";
require_once "sampdb.php";

The four statements differ as follows:

  • For include, PHP reads the file and evaluates it each time it encounters the include statement during the course of the script's execution. For require, the contents of the file replace the require statement, whether or not it falls into the script's execution path. This means that if you have code containing one of these directives and the code may be executed several times, it's more efficient to use require. On the other hand, if you want to read a different file each time you execute your code or you have a loop that iterates through a set of files, you want include because you can set a variable to the name of the file you want to include, and use the variable as the argument to include.

  • The include_once and require_once statements are similar to include and require, except that if PHP already has read the named file earlier, it will not read it again. This can be useful when include files include other files, to avoid the possibility of including a file multiple times and perhaps triggering function redefinition errors.

The scripts in this chapter use require_once.

When PHP sees the file-inclusion statement, it searches for the file and reads its contents. Anything in the file becomes accessible to the following parts of the script.

The sampdb distribution includes the sampdb.php file in its phpapi directory. Copy the file into the include directory that you want to use, and then set the file's mode and ownership so that it's readable by your Web server. You should also modify the connection parameters to reflect those that you use for connecting to MySQL.

After setting up sampdb.php, we can modify the Historical League home page to reference it and connect to the MySQL server using the DSN returned by the sampdb_dsn() function:

<html>
<head>
<title>U.S. Historical League</title>
</head>
<body bgcolor="white">
<p>Welcome to the U.S. Historical League Web Site.</p>
<?php
# USHL home page - version 2

require_once "DB.php";
require_once "sampdb.php";

$conn =& DB::connect (sampdb_dsn ());
if (DB::isError ($conn))
    exit ();
$result =& $conn->query ("SELECT COUNT(*) FROM member");
if (DB::isError ($result))
    exit ();
if ($row =& $result->fetchRow ())
    print ("<p>The League currently has " . $row[0] . " members.</p>");
$result->free ();
$conn->disconnect ();
?>
</body>
</html>

The script just shown can be found as index2.php in the phpapi/ushl directory of the sampdb distribution. Copy it to the ushl directory in your Web server's document tree, naming it index.php to replace the file of that name that is there now. This replaces the less secure version with a more secure one because the new file contains no literal MySQL name or password.

You may be thinking that we haven't really saved all that much coding in the home page by using an include file. But just wait. The sampdb.php file can be used for other functions as well, and thus serve as a convenient repository for any routine that we expect to be useful in multiple scripts. In fact, we can create two more such functions to put in that file right now. Every Web script we write in the remainder of the chapter will generate a fairly stereotypical set of HTML tags at the beginning of a page and another set at the end. Rather than writing out those tags in each script, we can write functions html_begin() and html_end() to generate them for us. The html_begin() function can take a couple of arguments that specify a page title and header. The code for the two functions is as follows:

function html_begin ($title, $header)
{
    print ("<html>\n");
    print ("<head>\n");
    if ($title != "")
        print ("<title>$title</title>\n");
    print ("</head>\n");
    print ("<body bgcolor=\"white\">\n");
    if ($header != "")
        print ("<h2>$header</h2>\n");
}
function html_end ()
{
    print ("</body></html>\n");
}

After putting html_begin() and html_end() in sampdb.php, the Historical League home page can be modified to use them. The resulting script looks like this:

<?php
# USHL home page - version 3

require_once "DB.php";
require_once "sampdb.php";

$title = "U.S. Historical League";
html_begin ($title, $title);
?>

<p>Welcome to the U.S. Historical League Web Site.</p>

<?php

$conn =& DB::connect (sampdb_dsn ());
if (DB::isError ($conn))
    exit ();
$result =& $conn->query ("SELECT COUNT(*) FROM member");
if (DB::isError ($result))
    exit ();
if ($row =& $result->fetchRow ())
    print ("<p>The League currently has " . $row[0] . " members.</p>");
$result->free ();
$conn->disconnect ();

html_end ();
?>

Notice that the PHP code has been split into two pieces, with the literal HTML text of the welcome message appearing between the pieces.

The use of functions for generating the initial and final part of the page provides an important capability. If you want to change the look of your page headers or footers, just modify the functions appropriately, and every script that uses them will be affected automatically. For instance, you might want to put a message "Copyright USHL" at the bottom of each Historical League page. Adding the message to a page-trailer function such as html_end() is an easy way to do that.

A Simple Data-Retrieval Page

The script that we've embedded in the Historical League home page runs a query that returns just a single row (the membership count). Our next script shows how to process a multiple-row result set (the full contents of the member table). This is the PHP equivalent of the DBI script dump_members.pl developed in Chapter 7, so we'll call it dump_members.php. The PHP version differs from the DBI version in that it's intended to be used in a Web environment rather than from the command line. For this reason, it needs to produce HTML output rather than simply writing tab-delimited text. To make rows and columns line up nicely, dump_members.php writes the member records as an HTML table. The script looks like this:

<?php
# dump_members.php - dump U.S. Historical League membership as HTML table

require_once "DB.php";
require_once "sampdb.php";

$title = "U.S. Historical League Member List";
html_begin ($title, $title);

$conn =& DB::connect (sampdb_dsn ());
if (DB::isError ($conn))
    die ("Cannot connect to server");

# issue statement
$stmt = "SELECT last_name, first_name, suffix, email,"
    . "street, city, state, zip, phone FROM member ORDER BY last_name";
$result =& $conn->query ($stmt);
if (DB::isError ($result))
    die ("Cannot execute statement");

print ("<table>\n");                    # begin table
# read results of statement, and then clean up
while ($row =& $result->fetchRow ($result_id))
{
    print ("<tr>\n");                   # begin table row
    for ($i = 0; $i < $result->numCols (); $i++)
    {
        # escape any special characters and print table cell
        printf ("<td>%s</td>\n", htmlspecialchars ($row[$i]));
    }
    print ("</tr>\n");                  # end table row
}
$result->free ();
$conn->disconnect ();
print ("</table>\n");                   # end table

html_end ();
?>

This script uses the die() function to print a message and to exit if an error occurs. (The die() function is similar to exit(), but it prints a message before exiting.) This is a different approach to error handling than we used in the Historical League home page. There, printing the membership count was just a little addition to the script's main purpose of presenting a greeting to the visitor. For dump_members.php, showing the query result is the entire reason for the script's existence, so if a problem occurs that prevents the result from being displayed, it's reasonable to print an error message indicating what the problem was.

To encode values for display in the Web page, dump_members.php uses the htmlspecialchars() function to take care of escaping characters that are special in HTML, such as '<', '>', or '&'. To encode values for inclusion with URLs, use urlencode() instead. These two functions are similar to the CGI.pm escapeHTML() and escape() methods that are discussed in Chapter 7.

To try the dump_members.php script, install it in the ushl directory of your Web server document tree and access it from your Web browser using this URL:

http://www.snake.net/ushl/dump_members.php

To let people know about dump_members.php, place a link to it in the Historical League home page script. The modified script then looks like this:

<?php
# USHL home page - version 4

require_once "DB.php";
require_once "sampdb.php";

$title = "U.S. Historical League";
html_begin ($title, $title);
?>

<p>Welcome to the U.S. Historical League Web Site.</p>

<?php
$conn =& DB::connect (sampdb_dsn ());
if (DB::isError ($conn))
    exit ();
$result =& $conn->query ("SELECT COUNT(*) FROM member");
if (DB::isError ($result))
    exit ();
if ($row =& $result->fetchRow ())
    print ("<p>The League currently has " . $row[0] . " members.</p>");
$result->free ();
$conn->disconnect ();
?>

<p>
You can view the directory of members <a href="dump_members.php">here</a>.
</p>

<?php
html_end ();
?>

The dump_members.php script demonstrates how a PHP script can retrieve information from MySQL and convert it into Web page content. If you like, you can modify the script to produce more elaborate results. One such modification is to display the values from the email column as live hyperlinks rather than as static text, to make it easier for site visitors to send mail to League members. The sampdb distribution contains a dump_members2.php script that does this. It differs from dump_members.php only slightly, in the loop that fetches and displays member entries. The original loop looks like this:

while ($row =& $result->fetchRow ($result_id))
{
    print ("<tr>\n");                   # begin table row
    for ($i = 0; $i < $result->numCols (); $i++)
    {
        # escape any special characters and print table cell
        printf ("<td>%s</td>\n", htmlspecialchars ($row[$i]));
    }
    print ("</tr>\n");                  # end table row
}

The email addresses are in the fourth column of the query result, so dump_members2.php treats that column differently from the rest, printing a hyperlink if the value is not empty:

while ($row =& $result->fetchRow ($result_id))
{
    print ("<tr>\n");                   # begin table row
    for ($i = 0; $i < $result->numCols (); $i++)
    {
        print ("<td>");
        # email is in column 4 (index 3) of result
        if ($i == 3 && $row[$i] != "")
        {
            printf ("<a href=\"mailto:%s\">%s</a>",
                        $row[$i],
                        htmlspecialchars ($row[$i]));
        }
        else
        {
            # escape any special characters and print table cell
            print (htmlspecialchars ($row[$i]));
        }
        print ("</td>\n");
    }
    print ("</tr>\n");                  # end table row
}

Processing Statement Results

This section examines in more detail some of PEAR DB's facilities for executing MySQL statements and handling result sets. The simplest way to issue statements with the DB module is to invoke the query() method of your connection object. This method takes a statement string as its argument. Its return value depends on whether the statement executes successfully and what kind of statement it is:

  • For a successful non-SELECT statement such as DELETE, INSERT, REPLACE, and UPDATE that doesn't return rows, query() returns DB_OK. In this case, you can call the affectedRows() method of the connection object to find out how many rows were changed (deleted, inserted, replaced, or updated, as the case may be).

  • For a successful SELECT statement, query() returns a DB_result result object. You can use this object to obtain further information about the result set. For example, you can find out how many rows or columns the result set has by calling its numRows() or numCols() method, respectively. To access the records in the result, invoke the result object's fetchRow() method.

  • If an error occurs, query() returns a DB_Error error object.

If the query() method returns an error object, it means the statement failedin other words, some error occurred and the statement couldn't even be executed. A statement may fail for any number of reasons:

  • It may be malformed and contain a syntax error.

  • The statement may be syntactically correct but semantically meaningless, such as when you try to select a column from a table containing no such column.

  • You may not have sufficient privileges to perform the statement.

  • The MySQL server host may have become unreachable due to network problems.

If query() fails and returns an error object, you can use that object to determine the particular reason for the error. For example, it has getMessage() and getCode() methods that return the error message string or error code. (See "Handling Errors.")

It's essential to check the return value from a statement-execution call to make sure that it succeeded, just as for the initial connection call. This is an important point. Many messages on PHP mailing lists asking why a script doesn't work could have been avoided by using proper error checking and not assuming that every call will succeed.

Handling Statements That Return No Result Set

For statements that modify rows, query() returns DB_OK for success or a DB_Error error object if an error occurred. This means you can test for errors a couple of ways. First, you can test the result directly to see if it is DB_OK:

if ($conn->query ("DELETE FROM member WHERE member_id = 149") != DB_OK)
    print ("Statement failed\n");

Second, you can test the result to see whether it is an error object:

$result =& $conn->query ("DELETE FROM member WHERE member_id = 149");
if (DB::isError ($result))
    print ("Statement failed\n");

The second approach is more involved, but gives you access to the error object should you want to invoke one of its methods to obtain additional information about the error.

Following successful execution of a statement that modifies rows, you can get a row count by invoking the affectedRows() method of the connection object. Suppose that you want to delete the record for member 149 in the member table and report the result. The following example shows how to determine whether the statement succeeded, and if so whether it actually deleted any rows:

if ($conn->query ("DELETE FROM member WHERE member_id = 149") != DB_OK)
    print ("Statement failed\n");
else if ($conn->affectedRows () < 1)
    print ("No record for member 149 was found\n");
else
    print ("Member 149 was deleted\n");

Handling Statements That Return a Result Set

To process a statement that returns rows, you must first execute the statement, and then (if it succeeds) fetch the contents of the result set. It's easy to forget that this process has two stages, especially if the statement returns only a single value. The following code illustrates how to fetch a single-value result. It makes sure that query() succeeds, and then fetches the record into $row with fetchRow(). Only if both operations succeed does the code print the value of COUNT(*):

$result =& $conn->query ("SELECT COUNT(*) FROM member");
if (DB::isError ($result))
    print ("Could not execute statement\n");
else
{
    if (!($row =& $result->fetchRow ()))
        print ("Could not fetch result\n");
    else
        print ("The member table has $row[0] records\n");
    $result->free ();
}

A similar approach can be used when you expect to get back several records, although in this case you'll usually use a loop to fetch the rows. The following example illustrates one way to do this:

$result =& $conn->query ("SELECT * FROM member");
if (DB::isError ($result))
    print ("Could not execute statement\n");
else
{
    printf ("Number of rows returned: %d\n", $result->numRows ());
    # fetch each row in result set
    while ($row =& $result->fetchRow ())
    {
        # print values in row, separated by commas
        for ($i = 0; $i < $result->numCols (); $i++)
        {
            if ($i > 0)
                print (",");
            print ($row[$i]);
        }
        print ("\n");
    }
    $result->free ();
}

If the statement fails, the script simply prints a message to that effect. If the statement succeeds, query() returns a valid result object that is useful in a number of ways. The object can be used for any of the following purposes:

  • Pass it to numRows() to determine the number of rows in the result set.

  • Pass it to numCols() to determine the number of columns in the result set.

  • Pass it to the row-fetching method, fetchRows(), to fetch successive rows of the result. The example uses this method to fetch an ordered (numerically indexed) array, but there are other ways to call it, which we'll see shortly. When there are no more rows, fetchRows() returns NULL.

  • Pass it to free() to allow DB to free the result set and dispose of any resources associated with it.

By default, the fetchRow() method returns an ordered array. It can be passed an argument to indicate what kind of value to return, as indicated in Table 8.1.

Table 8.1. fetchRow() Arguments

Argument

Return Value

DB_FETCHMODE_ORDERED

An array; elements are accessed by numeric indices

DB_FETCHMODE_ASSOC

An array; elements are accessed by associative indices

DB_FETCHMODE_OBJECT

An object; elements are accessed as properties


When called with an argument of DB_FETCHMODE_ORDERED, fetchRow() returns the next row of the result set as an ordered array. Elements of the array are accessed by numeric indices in the range from 0 to numCols()1. The following example shows how to use fetchRow() in a simple loop that fetches and prints the values in each row in tab-delimited format:

$stmt = "SELECT * FROM president";
$result =& $conn->query ($stmt);
if (DB::isError ($result))
    die ("Statement failed");
while ($row =& $result->fetchRow (DB_FETCHMODE_ORDERED))
{
    for ($i = 0; $i < $result->numCols ($result_id); $i++)
    {
        if ($i > 0)
            print ("\t");
        print ($row[$i]);
    }
    print ("\n");
}
$result->free ();

For each row in the result set that is available, the value assigned to $row is an array. You access its elements as $row[$i], where $i is the numeric column index. To determine the number of elements in each row, pass the result object to numCols().

Another way to fetch an array is to assign the result to a list of variables. For example, to fetch the last_name and first_name columns directly into variables named $ln and $fn and print the names in first name, last name order, do this:

$stmt = "SELECT last_name, first_name FROM president";
$result =& $conn->query ($stmt);
if (DB::isError ($result))
    die ("Statement failed");
while (list ($ln, $fn) = $result->fetchRow (DB_FETCHMODE_ORDERED))
    printf ("%s %s\n", $fn, $ln);
$result->free ();

The variables can have any legal names you like, but their order in the list() must correspond to the order of the columns selected by the query.

When called with an argument of DB_FETCHMODE_ASSOC, fetchRow() returns the next row of the result set as an associative array. The element names are the names of the columns selected by the query:

$stmt = "SELECT last_name, first_name FROM president";
$result =& $conn->query ($stmt);
if (DB::isError ($result))
    die ("Statement failed");
while ($row =& $result->fetchRow (DB_FETCHMODE_ASSOC))
    printf ("%s %s\n", $row["first_name"], $row["last_name"]);
$result->free ();

When called with an argument of DB_FETCHMODE_OBJECT, fetchRow() returns the next row of the result set as an object. This means you access elements of the row using $row->col_name syntax. For example, if you retrieve the last_name and first_name values from the president table, the columns can be accessed as follows:

$stmt = "SELECT last_name, first_name FROM president";
$result =& $conn->query ($stmt);
if (DB::isError ($result))
    die ("Statement failed");
while ($row =& $result->fetchRow (DB_FETCHMODE_OBJECT))
    printf ("%s %s\n", $row->first_name, $row->last_name);
$result->free ();

What if your query contains calculated columns? For example, you might issue a query that returns values that are calculated as the result of an expression:

SELECT CONCAT(first_name, ' ', last_name) FROM president

A query that is written like that is unsuitable when fetching rows as objects. The name of the selected column is the expression itself, which isn't a legal property name. However, you can supply a legal name by giving the column an alias. The following query aliases the column as full_name:

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM president

If you fetch each row from of this query as an object, the alias allows the column to be accessed as $row->full_name.

Testing for NULL Values in Query Results

PHP represents NULL values in result sets as unset values. One way to check for NULL in a column value returned from a SELECT query is to use the is_null() function. The following example selects and prints names and email addresses from the member table, printing "no email address available" if the address is NULL:

$stmt = "SELECT last_name, first_name, email FROM member";
$result =& $conn->query ($stmt);
if (DB::isError ($result))
    die ("Statement failed");
while (list ($last_name, $first_name, $email) = $result->fetchRow ())
{
    printf ("Name: %s %s, Email: ", $first_name, $last_name);
    if (is_null ($email))
        print ("No email address available");
    else
        print ($email);
    print ("\n");
}
$result->free ();

You can also test for NULL values by using the PHP NULL constant and the === identically-equal-to operator:

$stmt = "SELECT last_name, first_name, email FROM member";
$result =& $conn->query ($stmt);
if (DB::isError ($result))
    die ("Statement failed");
while (list ($last_name, $first_name, $email) = $result->fetchRow ())
{
    printf ("Name: %s %s, Email: ", $first_name, $last_name);
    if ($email === NULL)
        print ("No email address available");

    else
        print ($email);
    print ("\n");
}
$result->free ();

Handling Errors

The DB module provides for error handling by means of the DB_Error object and related methods. Some calls return one kind of object for success and a DB_Error object for failure. We have seen this for the DB::connect() call that connects to the database server, and the query() method that executes SQL statements. To test whether the return values from these calls represents an error, use the DB::isError() call. For example:

$conn =& DB::connect ($dsn);
if (DB::isError ($conn))
    die ("Cannot connect to server");

This example calls die() if an error occurs, but a DB_Error object supports methods that provide additional information. These are the three that I find most useful:

  • getMessage() returns a string containing an error message.

  • getCode() returns an error code.

  • getUserInfo() returns a string containing additional information about the error.

The following example shows how to display information when a statement fails to execute:

$result =& $conn->query ("SELECT * FROM no_such_table");
if (DB::isError ($result))
{
    print ("  getMessage value: " . $result->getMessage() . "\n");
    print ("     getCode value: " . $result->getCode() . "\n");
    print (" getUserInfo value: " . $result->getUserInfo() . "\n");
}

Other error object methods are available; consult the sections in the PEAR manual that describe the DB_Error and PEAR_Error objects.

The scripts in this chapter print fairly generic error messages such as "statement failed" when they detect an error. However, while you're developing a script, you'll often find it useful to invoke the error object methods to help you discover the particular reason for a problem that occurs.

Some DB methods return the value DB_OK for success. For example, if you invoke query() to execute a statement that does not return a result set, such as INSERT or DELETE, the result is DB_OK for success or a DB_Error object if an error occurred.

If it happens that you don't want to do error checking, you can arrange to have PEAR terminate a script automatically. To do this, call setErrorHandling():

PEAR::setErrorHandling (PEAR_ERROR_DIE);

It's also possible to invoke this method for a particular object, so enable automatic error checking on an object-specific basis:

$conn->setErrorHandling (PEAR_ERROR_DIE);

Some PHP functions produce an error message if an error occurs, in addition to returning a status value. In Web contexts, this message appears in the page sent to the client browser, which may not be what you want. To suppress the (possibly cryptic) error message that a function normally would produce, precede the function name by the @ operator: For example, to suppress the error message from a function named some_func() so that you can report failure in a more suitable manner, you might do something like this:

$status = @some_func ();

Quoting Issues

It's necessary to be aware of quoting issues when you're constructing SQL statement strings in PHP, just as it is in other languages such as C and Perl. The way to deal with quoting problems is similar, too, although the function names are different in the various languages. Suppose that you're constructing a statement to insert a new record into a table. In the statement string, you might put quotes around each value to be inserted into a string column:

$last = "O'Malley";
$first = "Brian";
$expiration = "2005-9-1";
$stmt = "INSERT INTO member (last_name,first_name,expiration)"
        . " VALUES('$last','$first','$expiration')";

The problem here is that one of the quoted values itself contains a quote (O'Malley), which results in a syntax error if you send the statement to the MySQL server. To deal with this in C, we could call mysql_real_escape_string() or mysql_escape_string(), and in a Perl DBI script, we could use quote(). PHP has an addslashes() function that accomplishes much the same objective. For example, a call to addslashes("O'Malley") returns the value O\'Malley. The previous example should be written as follows to prevent quoting problems:

$last = addslashes ("O'Malley");
$first = addslashes ("Brian");
$expiration = addslashes ("2005-9-1");
$stmt = "INSERT INTO member (last_name,first_name,expiration)"
        . " VALUES('$last','$first','$expiration')";

addslashes() has a couple of shortcomings that reduce its usefulness. First, it does not add surrounding quotes to the string, so it's necessary to specify quotes explicitly in the statement string around the values to be inserted. Second, if a value is unset or NULL, you'd want to insert the word "NULL" into the statement string without any surrounding quotes. This means you have to either know somehow what a value is, or test it and handle it differentially depending on whether it represents a NULL value. This gets messy quickly.

The DB module provides a connection object quoteSmart() method that handles these problems for you. For NULL values, it returns the word "NULL" without surrounding quotes. For non-NULL values, it returns a properly escaped value and adds quotes if necessary. (It does not quote numeric values.) To use quoteSmart() for statement construction, insert the value that it returns directly into the statement string, without adding any extra quotes yourself:

$last = $conn->quoteSmart ("O'Malley");
$first = $conn->quoteSmart ("Brian");
$expiration = $conn->quoteSmart ("2005-9-1");
$stmt = "INSERT INTO member (last_name,first_name,expiration)"
        . " VALUES($last,$first,$expiration)";

Placeholders and Prepared Statements

The preceding section describes how to quote data values for inclusion in SQL statements. Another approach is use placeholders and let the DB module do all the quoting for you. To indicate where data values should go within an SQL statement, use '?' characters as placeholder markers. Then supply the data values as parameters to the statement when you execute it. The DB module quotes the values properly and binds them to the placeholders. The values should be passed as an array. Here is an example that uses the query() method:

$stmt = "INSERT INTO member (last_name,first_name,expiration) VALUES(?,?,?)";
$params = array ("O'Malley", "Brian", "2005-09-01");
if ($conn->query ($stmt, $params) != DB_OK)
    die ("Statement failed");

You can also prepare the statement in advance with the prepare() method, and then execute it with execute().

$stmt = "INSERT INTO member (last_name,first_name,expiration) VALUES(?,?,?)";
$prep_stmt = $conn->prepare ($stmt);
if (DB::isError ($prep_stmt))
    die ("Could not prepare statement");

$params = array ("O'Malley", "Brian", "2005-09-01");
if ($conn->execute ($prep_stmt, $params) != DB_OK)
    die ("Could not execute statement");
$conn->freePrepared ($prep_stmt);

When you have finished with the prepared statement, release it with freePrepared().

The latter technique of using prepare() plus execute() is most useful for statements that you plan to execute multiple times. For example, to insert multiple records, you can prepare an INSERT statement once, and then execute() it within a loop that supplies data values for individual records each time through the loop.

    Team LiB
    Previous Section Next Section