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] PHP scripts developed in this chapter generate pages that are valid as XHTML, not just as HTML. See "Writing Web Output" in Chapter 7, "The Perl DBI API," 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 HTML mode to PHP code mode and starts interpreting the file as PHP code to be executed. The interpreter switches from code mode back to HTML 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 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 HTML 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 the following:

 <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 HTML 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 H 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. For example, suppose 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 the following command:

 % php -q 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.)

You can make the 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 it 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 ability 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 ability to generate dynamic output that can vary 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 $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret")     or exit (); mysql_select_db ("sampdb")     or exit (); $result_id = mysql_query ("SELECT COUNT(*) FROM member")     or exit (); if ($row = mysql_fetch_row ($result_id))     print ("<p>The League currently has " . $row[0] . " members.</p>"); mysql_free_result ($result_id); ?> </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:

It opens a connection to the MySQL server and makes the sampdb database the default database.
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).
The script constructs from the query result a message containing the membership count and then disposes of the result set.

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 is 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.

You can find this script 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 the following URLs:

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 connect to the server using mysql_connect():

 $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret")      or exit ();

This function takes three arguments that indicate the name of the MySQL server host and the name and password for your MySQL account. mysql_connect() returns a connection identifier if it successfully established a connection or FALSE if an error occurs. If the connection attempt fails, the script calls exit() to terminate immediately.

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 "Using Functions and Include Files" section later in this chapter.

What about the '@' character that appears in front of the mysql_connect() call? That is the "Shut up, please" operator. Some PHP functions write an error message when they fail, in addition to returning a status code. In the case of mysql_connect(), a failed connection attempt would cause a message like the following to appear in the Web page that is sent to the client's browser:

 Warning: MySQL Connection Failed: Access denied for user:  'sampadm@cobra.snake.net' (Using password: YES)

That's ugly, and the person visiting our site may not know what to make of it or what to do about it. Putting '@' in front of the mysql_connect() call suppresses this error message so that we can choose how to deal with errors ourselves on the basis of the return value. For the script at hand, the best thing to do if an error occurs is to produce no output at all pertaining to the membership count. In that case, the page will contain only the welcome message.

You can precede any PHP expression with the @ operator, but, in my experience, the most likely cause of failure is the initial connection call; hence, the example scripts in this chapter suppress messages only from mysql_connect(). (If some explicit error indicator is necessary, the scripts print their own message.)

mysql_connect() Versus mysql_pconnect()

A function similar to mysql_connect() is mysql_pconnect(). Both take hostname, username, and password arguments and return a connection identifier or FALSE to indicate success or failure of the connection attempt. The difference between the two calls is that mysql_connect() establishes a non-persistent connection, whereas mysql_pconnect() establishes a persistent connection. A persistent connection differs from a non-persistent one in that it is not closed when the script terminates. Suppose an Apache process executes a PHP script that calls mysql_pconnect() to open a persistent connection. If the same process later executes another PHP script that calls mysql_pconnect() with the same arguments, the connection is reused. For many database engines, using persistent connections is much more efficient than establishing each connection from scratch. However, there is little advantage for MySQL because the connection-establishment process is extremely efficient. In fact, it may even be disadvantageous to use persistent connections. On a busy Web site with many PHP scripts, you may end up with Apache processes that hold open so many connections to the MySQL server that all available connection slots get used up. You may be able to deal with this issue by increasing the value of the max_connections server variable (see the "Tuning the Server" section in Chapter 11, "General MySQL Administration"), but another option is to use non-persistent connections.

The connection identifier returned by mysql_connect() can be passed to several other MySQL-related calls in the PHP API. However, for such calls, the identifier is always optional. For example, you can call mysql_select_db() using either of the following forms:

 mysql_select_db ($db_name, $conn_id);  mysql_select_db ($db_name);

If you omit the connection identifier argument from a call to any MySQL-related PHP function that takes one, the function uses the most recently opened connection. Thus, if your script opens only a single connection, that connection will be the default and you never need to specify a connection argument explicitly in any of your MySQL calls. This is quite different than MySQL programming with the C or DBI APIs, for which there is no such default.

The connection code in our simple home page script was written as follows using the $conn_id variable to make it clearer what kind of value mysql_connect() returns:

 $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret")      or exit ();

However, the script doesn't actually use $conn_id anywhere else, so that statement actually could have been written more simply as follows:

 @mysql_connect ("cobra.snake.net", "sampadm", "secret")      or exit ();

Assuming the connection is established successfully, the next step is to select a database:

 mysql_select_db ("sampdb")      or exit ();

If mysql_select_db() fails, we exit silently. An error is unlikely to occur at this point if we've been able to connect to the server and the database exists, but it's still prudent to check for problems and take appropriate action.

After selecting the database, the script sends a member-counting query to the server, extracts the result, displays it, and frees the result set:

 $result_id = mysql_query ("SELECT COUNT(*) FROM member")      or exit (); if ($row = mysql_fetch_row ($result_id))     print ("<p>The League currently has " . $row[0] . " members.</p>"); mysql_free_result ($result_id);

The mysql_query() function sends the query to the server to be executed. (Note that the query string contains no terminating semicolon character or \g sequence, in contrast to the way you issue queries from within the mysql program.) mysql_query() returns FALSE if the query was illegal or couldn't be executed for some reason; otherwise, it returns a result identifier. This identifier is a value 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, we pass the result identifier to mysql_fetch_row() to fetch the row, assign the row to the variable $row, and access its first element, $row[0], which also happens to be its only element.

After processing the result set, we free it by passing the result identifier to mysql_free_result(). This call is included for completeness. It actually isn't necessary here because PHP automatically releases any active result sets when a script terminates. mysql_free_result() is useful primarily in scripts that execute very large queries or a large number of queries, where it helps prevents 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_id, $result_id, 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.

PHP's Linguistic Influences

If you have experience with C programming, you've probably noticed that many of the syntactic constructs in our PHP script are very similar to what you use for C programming. PHP syntax is in fact largely drawn from C, so the similarity is not coincidental. If you have some background in C, you'll be able to transfer much of it to PHP. In fact, if you're not sure how to write an expression or control structure in PHP, just try it the way you'd write it in C and it'll often be correct.

Although PHP has its roots mainly in C, elements of Java and Perl are present, too. You can certainly see this in the comment syntax, where any of the following forms are allowed:

# Perl-style comment from '#' to end of line
// C++- or Java-style comment from // to end of line
/* C-style comment between slash-star to star-slash */

Other similarities with Perl include the '.' string concatenation operator (including '.=' as additive concatenation) and the way that variable references and escape sequences are interpreted within double quotes but not within single quotes.

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_connect() to establish the connection and put the function in an include file a file that is not part of our main script but that can be referenced from it. Some advantages of this approach are as follows:

It's easier to write connection establishment code. We can write out the connection parameters once in the sampdb_connect() helper function, not in every individual script that needs to connect. Also, because the scripts we develop here will always use the sampdb database, the helper function can select it after connecting. This way it handles the work of two MySQL operations. 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 the 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 cobra to boa, we don't need to change a bunch of individual scripts, we just change the hostname argument of the mysql_connect() call in the include file where the sampdb_connect() 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 that 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 that have login accounts on the Web server host may be able to read the include file directly unless you take some precautions. The "Connecting to the MySQL Server from Web Scripts" section in Chapter 7, "The Perl DBI API," 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:

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 outside my document tree (/usr/local/apache/htdocs), not within it.
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).^[3] 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:
^[3] The 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.
```
 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.
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_connect() function, as shown in the following listing:
```
 <?php  # sampdb.php - sampdb sample database common functions # Connect to the MySQL server using our top-secret name and password function sampdb_connect () {     $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret");     if ($conn_id && mysql_select_db ("sampdb"))         return ($conn_id);     return (FALSE); } ?> 
```
If the sampdb_connect() function successfully connects and selects the database, it returns a connection identifier. If an error occurs, it returns FALSE. Because sampdb_connect() prints no message when an error occurs, the caller can exit silently or print a message as circumstances warrant.

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 HTML 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.)
To reference the include file from a script, use a line like the following:
```
 include "sampdb.php";  
```
When PHP sees that line, it searches for the file and reads its contents. Anything in the file becomes accessible to the following parts of the script.

A version of sampdb.php is included in the phpapi directory of the sampdb distribution. Copy it into the include directory 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 by calling the sampdb_connect() 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 include "sampdb.php"; sampdb_connect ()     or exit (); $result_id = mysql_query ("SELECT COUNT(*) FROM member")     or exit (); if ($row = mysql_fetch_row ($result_id))     print ("<p>The League currently has " . $row[0] . " members.</p>"); mysql_free_result ($result_id); ?> </body> </html>

You can find the script just shown 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.

include Versus require

PHP has a require statement that is similar to include. For include, the file is read and evaluated each time the include statement is executed 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.

PHP 4 adds two related statements include_once and require_once. These are similar to include and require except that if the named file has already been read earlier, it will not be read 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.

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 can 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 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 as follows:

 <?php  # USHL home page - version 3 include "sampdb.php"; $title = "U.S. Historical League"; html_begin ($title, $title); ?> <p>Welcome to the U.S. Historical League Web Site.</p> <?php sampdb_connect ()     or exit (); $result_id = mysql_query ("SELECT COUNT(*) FROM member")     or exit (); if ($row = mysql_fetch_row ($result_id))     print ("<p>The League currently has " . $row[0] . " members.</p>"); mysql_free_result ($result_id); 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 example, 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 the following:

 <?php  # dump_members.php - dump Historical League membership list as HTML table include "sampdb.php"; $title = "U.S. Historical League Member List"; html_begin ($title, $title); sampdb_connect ()     or die ("Cannot connect to server"); # issue query $query = "SELECT last_name, first_name, suffix, email,"     . "street, city, state, zip, phone FROM member ORDER BY last_name"; $result_id = mysql_query ($query)     or die ("Cannot execute query"); print ("<table>\n");                    # begin table # read results of query, then clean up while ($row = mysql_fetch_row ($result_id)) {     print ("<tr>\n");                   # begin table row     for ($i = 0; $i < mysql_num_fields ($result_id); $i++)     {         # escape any special characters and print table cell         printf ("<td>%s</td>\n", htmlspecialchars ($row[$i]));     }     print ("</tr>\n");                  # end table row } mysql_free_result ($result_id); print ("</table>\n");                   # end table html_end (); ?>

This script uses the die() function to print a message and to exit if an error occurs.^[4] 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 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.

^[4] The die() function is similar to exit(), but it prints a message before exiting.

To try out the dump_members.php script, install it in the ushl directory of your Web server document tree and access it as follows:

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 include "sampdb.php"; $title = "U.S. Historical League"; html_begin ($title, $title); ?> <p>Welcome to the U.S. Historical League Web Site.</p> <?php sampdb_connect ()     or exit (); $result_id = mysql_query ("SELECT COUNT(*) FROM member")     or exit (); if ($row = mysql_fetch_row ($result_id))     print ("<p>The League currently has " . $row[0] . " members.</p>"); mysql_free_result ($result_id); ?> <p> You can view the directory of members <a href="dump_members.php">here</a>. </p> <?php html_end (); ?>

The dump_members.php script serves the purpose of demonstrating 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 as follows:

 while ($row = mysql_fetch_row ($result_id))  {     print ("<tr>\n");                   # begin table row     for ($i = 0; $i < mysql_num_fields ($result_id); $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 than the rest, printing a hyperlink if the value is not empty:

 while ($row = mysql_fetch_row ($result_id))  {     print ("<tr>\n");                   # begin table row     for ($i = 0; $i < mysql_num_fields ($result_id); $i++)     {         print ("<td>");         if ($i == 3 && $row[$i] != "")  # email is in 4th column of result         {             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 Query Results

This section examines in more detail PHP's facilities for executing MySQL queries and handling result sets. In PHP, queries are issued by calling the mysql_query() function, which takes a query string and a connection identifier as arguments. The connection identifier is optional, so you can invoke mysql_query() using either of the following forms:

 $result_id = mysql_query ($query, $conn_id);  # use explicit connection  $result_id = mysql_query ($query);            # use default connection

mysql_query() returns a result identifier, which must be interpreted according to the type of statement you issue. For non-SELECT statements such as DELETE, INSERT, REPLACE, and UPDATE that don't return rows, mysql_query() returns TRUE or FALSE to indicate the success or failure of the query. For a successful query, you can call mysql_affected_rows() to find out how many rows were changed (deleted, inserted, replaced, or updated, as the case may be).

For SELECT statements, mysql_query() returns either a result identifier or FALSE to indicate the success or failure of the query. For a successful query, you use the result identifier to obtain further information about the result set. For example, you can determine how many rows or columns the result set has by calling mysql_num_rows() or mysql_num_fields(). To access the records in the result, you can call any of several row-fetching functions.

If mysql_query() returns FALSE, it means the statement failed in other words, some error occurred and the query couldn't even be executed. A statement can fail for any number of reasons:

It may be malformed and contain a syntax error.
The query 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 query.
The MySQL server host may have become unreachable due to network problems.

If mysql_query() returns FALSE and you want to know the particular reason for the error, call mysql_error() or mysql_errno() to obtain the error message string or numeric error code (see the "Handling Errors" section later in this chapter).

Don't Assume That mysql_query() Will Succeed

On the PHP mailing list, it's common for new PHP users to ask why a script prints the following error message:

 Warning: 0 is not a MySQL result index in file on line n

This message indicates that a result identifier value of zero (that is, FALSE) was passed to some function (such as a row-fetching routine) that expects a valid result identifier. This means that an earlier call to mysql_query() returned FALSE (in other words, mysql_query() failed), and the script passed the return value to another function without bothering to check it first. That is a mistake. When you use mysql_query(), always test its return value if the code that follows it depends on the success of the query.

When mysql_query() does not return FALSE, the result identifier must be properly interpreted to be useful. For example, two common mistakes are to think that the return value is a row count or that it contains the data returned by your query. Neither is true, as the following sections demonstrate.

Handling Queries That Return No Result Set

For statements that modify rows, the return value from mysql_query() is not a row count. It is simply an indicator of success or failure, nothing more. To get a row count, call mysql_affected_rows(). Suppose you want to delete the record for member 149 in the member table and report the result. An incorrect way of doing so is as follows:

 $result_id = mysql_query ("DELETE FROM member WHERE member_id = 149");  if (!$result_id)     print ("member 149 was not deleted\n"); else     print ("member 149 was deleted\n");

This code is incorrect because it treats $result_id as a row count and assumes that a value of 0 (FALSE) means the query executed successfully but deleted no rows. But what it really means is that the query failed to execute.

Another incorrect way to interpret the result from mysql_query() is as follows:

 $result_id = mysql_query ("DELETE FROM member WHERE member_id = 149");  if (!$result_id)     print ("query failed\n"); else     print ("member 149 was deleted\n");

This code properly distinguishes query failure from query success, but is still incorrect because it assumes that if the query succeeded it actually deleted a record. Why is that wrong? Because a DELETE statement need not actually delete anything to execute successfully. If there does happen to be a member with an ID of 149, MySQL deletes the record and mysql_query() returns TRUE. But if no such member exists, mysql_query() still returns TRUE, because the query is legal. To determine whether or not a successful query actually deleted any rows, call mysql_affected_rows(). The following example shows the proper way to interpret the result identifier:

 $result_id = mysql_query ("DELETE FROM member WHERE member_id = 149");  if (!$result_id)     print ("query failed\n"); else if (mysql_affected_rows () < 1)     print ("no record for member 149 was found\n"); else     print ("member 149 was deleted\n");

Handling Queries That Return a Result Set

To process a query that returns rows, you must first execute the query 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 query returns only a single value. Consider the following attempt to determine how many records are in the member table:

 $result_id = mysql_query ("SELECT COUNT(*) FROM member");  print ("The member table has $result_id records\n");

This code is incorrect. It assumes that because the result set consists of only a single data value, that value must be what mysql_query() returns. That is untrue. $result_id allows you to access the result set, but is never itself the result. Even if the result consists of a single value, you must still fetch it after executing the query. The following code illustrates one way to do this. It makes sure that mysql_query() succeeds and then fetches the record into $row with mysql_fetch_row(). Only if both operations succeed does the code print the value of COUNT(*):

 $result_id = mysql_query ("SELECT COUNT(*) FROM member");  if (!$result_id || !($row = mysql_fetch_row ($result_id)))     print ("query failed\n"); else     print ("The member table has $row[0] records\n");

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_id = mysql_query ("SELECT * FROM member");  if (!$result_id)     print ("query failed\n"); else {     printf ("number of rows returned: %d\n", mysql_num_rows ($result_id));     # fetch each row in result set     while ($row = mysql_fetch_row ($result_id))     {         # print values in row, separated by commas         for ($i = 0; $i < mysql_num_fields ($result_id); $i++)         {             if ($i > 0)                 print (",");             print ($row[$i]);         }         print ("\n");     }     mysql_free_result ($result_id); }

If the query fails, the result is FALSE, and the script simply prints a message to that effect. If the query succeeds, mysql_query() returns a valid result identifier that is useful in a number of ways (though not as a row count!). The result identifier can be used for any of the following purposes:

Pass it to mysql_num_rows() to determine the number of rows in the result set.
Pass it to mysql_num_fields() to determine the number of columns in the result set.
Pass it to a row-fetching routine to fetch successive rows of the result set. The example uses mysql_fetch_row(), but there are other choices, which we'll see shortly.
Pass it to mysql_free_result() to allow PHP to free the result set and dispose of any resources associated with it.

PHP provides several row-fetching functions for retrieving a result set after mysql_query() successfully executes a SELECT query (see Table 8.1). Each of these functions takes a result identifier as the argument and returns FALSE when there are no more rows.

Table 8.1. PHP Row-Fetching Functions
Function Name	Return Value
`mysql_fetch_row()`	An array; elements are accessed by numeric indices
`mysql_fetch_assoc()`	An array; elements are accessed by associative indices
`mysql_fetch_array()`	An array; elements are accessed by numeric or associative indices
`mysql_fetch_object()`	An object; elements are accessed as properties

The most basic call is mysql_fetch_row(), which returns the next row of the result set as an array. Elements of the array are accessed by numeric indices in the range from 0 to mysql_num_fields() 1. The following example shows how to use mysql_fetch_row() in a simple loop that fetches and prints the values in each row in tab-delimited format:

 $query = "SELECT * FROM president";  $result_id = mysql_query ($query)     or die ("Query failed"); while ($row = mysql_fetch_row ($result_id)) {     for ($i = 0; $i < mysql_num_fields ($result_id); $i++)     {         if ($i > 0)             print ("\t");         print ($row[$i]);     }     print ("\n"); } mysql_free_result ($result_id);

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 identifier to mysql_num_fields(). You might be tempted to count the number of values by passing $row to PHP's count() function, which counts the number of values in an array. That is problematic if the result set contains NULL values, which PHP represents using unset values. count() doesn't count unset values in PHP 3, which makes it an unreliable measure of the number of columns. Use mysql_num_fields() to do so; that's what it's for.

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 the following:

 $query = "SELECT last_name, first_name FROM president";  $result_id = mysql_query ($query)     or die ("Query failed"); while (list ($ln, $fn) = mysql_fetch_row ($result_id))     printf ("%s %s\n", $fn, $ln); mysql_free_result ($result_id);

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.

mysql_fetch_assoc(), the second row-fetching function listed in Table 8.1, returns a row with elements that are accessed by associative index. The element names are the names of the columns selected by the query:

 $query = "SELECT last_name, first_name FROM president";  $result_id = mysql_query ($query)     or die ("Query failed"); while ($row = mysql_fetch_assoc ($result_id))     printf ("%s %s\n", $row["first_name"], $row["last_name"]); mysql_free_result ($result_id);

mysql_fetch_assoc() is newer than the other row-fetching functions; it's available only as of PHP 4.0.3.

The third row-fetching function, mysql_fetch_array(), returns a row with elements that can be accessed both by numeric index and associative index. In other words, you can access elements by number or by name:

 $query = "SELECT last_name, first_name FROM president";  $result_id = mysql_query ($query)     or die ("Query failed"); while ($row = mysql_fetch_array ($result_id)) {     printf ("%s %s\n", $row[1], $row[0]);     printf ("%s %s\n", $row["first_name"], $row["last_name"]); } mysql_free_result ($result_id);

The information returned by mysql_fetch_array() is a combination of the information returned by mysql_fetch_row() and mysql_fetch_assoc(). Despite that, performance differences between the functions are negligible, and you can call mysql_fetch_array() with no particular penalty.

The final row-fetching function, mysql_fetch_object(), 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:

 $query = "SELECT last_name, first_name FROM president";  $result_id = mysql_query ($query)     or die ("Query failed"); while ($row = mysql_fetch_object ($result_id))     printf ("%s %s\n", $row->first_name, $row->last_name); mysql_free_result ($result_id);

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 for use with mysql_fetch_object(). 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 the results of this query with mysql_fetch_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 isset() 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:

 $query = "SELECT last_name, first_name, email FROM member";  $result_id = mysql_query ($query)     or die ("Query failed"); while (list ($last_name, $first_name, $email) = mysql_fetch_row ($result_id)) {     printf ("Name: %s %s, Email: ", $first_name, $last_name);     if (!isset ($email))         print ("no email address available");     else         print ($email);     print ("\n"); } mysql_free_result ($result_id);

A related function is empty(), but empty() returns the same result for NULL and empty strings, so it's not useful as a NULL value test.

In PHP 4, you can test for NULL values using the PHP NULL constant by using the === identically-equal-to operator:

 $query = "SELECT last_name, first_name, email FROM member";  $result_id = mysql_query ($query)     or die ("Query failed"); while (list ($last_name, $first_name, $email) = mysql_fetch_row ($result_id)) {     printf ("Name: %s %s, Email: ", $first_name, $last_name);     if ($email === NULL)         print ("no email address available");     else         print ($email);     print ("\n"); } mysql_free_result ($result_id);

Handling Errors

PHP puts three means at your disposal for dealing with errors in MySQL-based scripts. The two are generally applicable to many kinds of errors, and one is specific to MySQL operations. First, you can use the @ operator to suppress any error message a function might emit. We've been doing this in calls to mysql_connect() to prevent error messages from that function from appearing in the page sent to the client:

 $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret");

Second, the error_reporting() function can be used to turn error reporting on or off at any of the following levels shown in Table 8.2.

Table 8.2. PHP Error-Handling Levels
Error Level	Types of Errors Reported
`E_ERROR`	Normal function errors
`E_WARNING`	Normal warnings
`E_PARSE`	Parser errors
`E_NOTICE`	Notices
`E_CORE_ERROR`	Errors from core engine
`E_CORE_WARNING`	Warnings from core engine
`E_COMPILE_ERROR`	Errors from compiler
`E_COMPILE_WARNING`	Warnings from compiler
`E_USER_ERROR`	User-generated error
`E_USER_WARNING`	User-generated warning
`E_USER_NOTICE`	User-generated notice
`E_ALL`	All errors

To control error reporting with error_reporting(), call it with an argument equal to the bitwise OR of the levels you want enabled. Turning off the E_ERROR and level E_WARNING levels should be sufficient to suppress messages from MySQL functions:

 error_reporting (E_PARSE | E_NOTICE);

You probably don't want to turn off level E_PARSE warnings about parse errors; if you do, you may have a difficult time debugging any changes you make to your scripts. Level E_NOTICE warnings often can be ignored but sometimes indicate a problem with your script that you should pay attention to, so you may want to leave that level enabled as well. Normally, you need not be concerned about the other error levels, except perhaps E_ALL to enable all messages.

In PHP 3, only four error levels are available, and they must be referred to by literal values, not symbolic constants (see Table 8.3).

Table 8.3. PHP 3 Error-Handling Levels
Error Level	Types of Errors Reported
1	Normal function errors
2	Normal warnings
4	Parser errors
8	Notices

The third error-handling technique is to use mysql_error() and mysql_errno(). These functions report error information that is returned by the MySQL server. They are similar to the calls with the same names in the C API. mysql_error() returns an error message in string form (an empty string if no error occurred). mysql_errno() returns an error number (0 if no error occurred). Both functions take a connection identifier argument specifying a connection to the MySQL server, and both return error information for the most recently invoked MySQL function on that connection that returns a status. The connection identifier is optional; if it's missing, the most recently opened connection is used. For example, you could report an error from mysql_query() as follows:

 $result_id = mysql_query ($query);  if (!$result_id) {     print ("errno: " . mysql_errno() . "\n");     print ("error: " . mysql_error() . "\n"); }

With the C API, you can get error information from mysql_error() and mysql_errno(), even when an attempt to connect to the server fails. That's also true for PHP, but only as of PHP 4.0.6. Prior to 4.0.6, the PHP versions of mysql_error() and mysql_errno() do not return useful error information for a connection until the connection has been established successfully. (In other words, if a connection attempt fails, you cannot use mysql_error() or mysql_errno() to report the reason why.) Under these circumstances, if you want to report a specific reason for connection failure rather than some generic message, you must take special measures. See Appendix H for instructions on how to do this.

The scripts in this chapter print fairly generic error messages, such as "Query failed" as they detect an error. However, while you're developing a script, you'll often find it useful to add a call to mysql_error() to help you discover the particular reason for an error.

Quoting Issues

It's necessary to be aware of quoting issues when you're constructing query 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 you're constructing a query to insert a new record into a table. In the query string, you might put quotes around each value to be inserted into a string column:

 $last = "O'Malley";  $first = "Brian"; $expiration = "2002-9-1"; $query = "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) that results in a syntax error if you send the query 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 ("2002-9-1"); $query = "INSERT INTO member (last_name,first_name,expiration)"         . " VALUES('$last','$first','$expiration')";

In DBI, quote() adds surrounding quotes to the string, but in PHP, addslashes() does not, so it's still necessary to specify quotes explicitly in the query string around the values to be inserted. This technique works well for all except unset values, for which you'd want to insert the word NULL into the query string without any surrounding quotes. This problem does have a solution, which we'll consider shortly.

Alternatives to addslashes() include mysql_escape_string() as of PHP 4.0.3, and mysql_real_escape_string() as of PHP 4.3.0:

 $escaped_str = mysql_escape_string ($str);  $escaped_str = mysql_real_escape_string ($str, $conn_id);

Each of these is based a function of the same name in the MySQL C API. mysql_real_escape_string() takes into account the character set of the connection when performing encoding, which is why it takes a connection identifier argument. If you omit that argument, the function uses the character set of the current connection.

Given that it's possible to choose from up to three functions when handling string escaping, it can become very messy to pick one each time a string is to be encoded for insertion into a query string. So let's write a quote_value() utility routine to be placed into the sampdb.php library file. quote_value() will do the work of escaping a string with the most recent of the available encoding functions, using function_exists() to check which functions are available:

 function quote_value ($str)  {     if (!isset ($str))         return ("NULL");     if (function_exists ("mysql_real_escape_string"))         return ("'" . mysql_real_escape_string ($str) . "'");     if (function_exists ("mysql_escape_string"))         return ("'" . mysql_escape_string ($str) . "'");     return ("'" . addslashes ($str) . "'"); }

In addition to choosing from among the available encoding functions, quote_value() also solves the problem of handling unset (NULL) values that was mentioned a short while back. If the argument is an unset value, quote_value() returns the word NULL with no surrounding quotes; otherwise, it escapes the argument and returns the resulting value, including surrounding single quotes. Thus, to use quote_value() for query construction, you insert the value that it returns directly into the query string, without adding any extra quotes yourself:

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

The preceding discussion covers quoting issues that occur when constructing query strings to be sent to the MySQL server. Quoting issues also occur when generating output to be presented in Web pages. If you're writing a string that should appear as HTML or as part of a URL, it's best to encode it if the string may contain characters that are special within HTML or URLs. The PHP functions htmlspecialchars() and urlencode() can be used for this. They're similar to the CGI.pm escapeHTML() and escape() methods discussed in Chapter 7.