In PHP, library functions are provided for executing SQL statements, as well as for managing result sets returned from queries, error handling, and controlling how data is passed from the database server to the PHP engine. We overview these functions here and show how they can be combined to access the MySQL server. At the time of writing, PHP4.3 and MySQL 4.0 were the stable releases. The MySQL library functions that are discussed here work with those versions. The PHP5 MySQL library functions also work with MySQL 4.0. However, the MySQL functions discussed here do not work with the alpha release of MySQL 4.1. Instead, a new improved library is being developed for MySQL 4.1, and it is intended to be part of PHP5 in addition to the regular library. An introduction to this library is included as Appendix H. 6.1.1 Opening and Using a Database ConnectionIn this section, we introduce the basic PHP scripting techniques to query a MySQL server and produce HTML for display in a web browser. Connecting to and querying a MySQL server with PHP is a five-step process. Example 6-1 shows a script that connects to the MySQL server, uses the winestore database, issues a query to select all the records from the wine table, and reports the results as pre-formatted HTML text. The example illustrates four of the key functions for connecting to and querying a MySQL database with PHP. Each function is prefixed with the string mysql_. Example 6-1. Connecting to a MySQL database with PHP<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html401/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <title>Wines</title> </head> <body> <pre> <?php // (1) Open the database connection $connection = mysql_connect("localhost","fred","shhh"); // (2) Select the winestore database mysql_select_db("winestore", $connection); // (3) Run the query on the winestore through the connection $result = mysql_query ("SELECT * FROM wine", $connection); // (4) While there are still rows in the result set, fetch the current // row into the array $row while ($row = mysql_fetch_array($result, MYSQL_NUM)) { // (5) Print out each element in $row, that is, print the values of // the attributes foreach ($row as $attribute) print "{$attribute} "; // Print a carriage return to neaten the output print "\n"; } ?> </pre> </body> </html> The five steps of querying a database are numbered in the comments in Example 6-1, and they are as follows:
The first ten wine rows produced by the script in Example 6-1 are shown in Example 6-2. The results are shown marked up as HTML. Example 6-2. Marked-up HTML output from the code shown in Example 6-1<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html401/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <title>Wines</title> </head> <body><pre> 1 Archibald Sparkling 1997 1 2 Pattendon Fortified 1975 1 3 Lombardi Sweet 1985 2 4 Tonkin Sparkling 1984 2 5 Titshall White 1986 2 6 Serrong Red 1995 2 7 Mettaxus White 1996 2 8 Titshall Sweet 1987 3 9 Serrong Fortified 1981 3 10 Chester White 1999 3 ... </pre> </body> </html> PHP does programmatically what you have done by hand in Chapter 5 with the MySQL command line interpreter. The function mysql_connect( ) performs the equivalent function to running the interpreter. The mysql_select_db( ) function provides the use database command, and mysql_query( ) permits an SQL statement to be executed. The mysql_fetch_array( ) function manually retrieves a result set that's automatically output by the interpreter. The basic principles and practice of using MySQL with PHP are shown in the four functions we've used. These key functions and all others are described in detail in "MySQL Function Reference." 6.1.2 Using mysql_fetch_array( )In our first example, we accessed attributes in order using the foreach loop statement. In many cases, you'll also want to access the attributes in another way, and this is usually best achieved by using the attribute names themselves. It's much easier to remember that you want to show the user the vintage year, the wine's name, the varieties, and the price, than to remember you want to show attributes four, two, six, and one from the SELECT statement. It's also a much better programming methodology because your code will be independent of the structure of the SQL statement and it'll be more readable. What's more, it's faster to access only the values you need. Consider a fragment of PHP that displays information about wineries: $result = mysql_query("SELECT winery_name, phone, fax FROM winery"); while($row = mysql_fetch_array($result)) { print "The {$row["winery_name"]} winery's fax is {$row["fax"]}". print "Their phone is {$row["phone"]}.\n"; } The array $row contains one row of the results, and each of the attributes of the winery table is accessible using its attribute name as the associative key. We've used the curly brace style discussed in Chapter 2 to output variables within a double-quoted string: you can see its usefulness here! There are four tricks to using mysql_fetch_array( ):
6.1.3 Error Handling of MySQL Database FunctionsDatabase functions can fail. There are several possible classes of failure, ranging from critical the server is inaccessible or a fixed parameter is incorrect to recoverable, such as a password being entered incorrectly by the user. In this section, we show you how to detect and handle these errors during code development. Chapter 12 discusses how to develop a professional error handler that you can use when your application is deployed. PHP has two error-handling functions, mysql_error( ) and mysql_errno( ) , for detecting and reporting errors. Example 6-3 shows the script illustrated earlier in Example 6-1 with additional error handling: it does exactly the same thing, but we've added error handling. In addition, we've deliberately included an error so that you can see what happens when one occurs: the keyword SELECT is misspelled as SELEC. The error handler is a function, showerror( ) , that prints a phrase in the format: Error 1064 : You have an error in your SQL syntax near 'SELEC * FROM wine' at line 1 (Error messages often change between MySQL versions, so the error message might be worded differently when you run the example on your system.) The error message shows both the numeric output of mysql_errorno( ) and the string output of mysql_error( ). The die( ) function outputs the message and then gracefully ends the script. Ending the script is often useful it prevents the PHP engine from outputting several warnings as consecutive database functions fail; for example, if a connection can't be established, the PHP engine will issue a warning, and this will be followed by warnings as each subsequent database function is attempted and fails.
Example 6-3. Querying a database with error handling<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html401/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <title>Wines</title> </head> <body><pre> <?php function showerror( ) { die("Error " . mysql_errno( ) . " : " . mysql_error( )); } // (1) Open the database connection if (!($connection = @ mysql_connect("localhost","fred","shhh"))) die("Could not connect"); // (2) Select the winestore database if (!(@ mysql_select_db("winestore", $connection))) showerror( ); // (3) Run the query on the winestore through the connection // NOTE : 'SELECT' is deliberately misspelt to cause an error if (!($result = @ mysql_query ("SELEC * FROM wine", $connection))) showerror( ); // (4) While there are still rows in the result set, // fetch the current row into the array $row while ($row = @ mysql_fetch_array($result, MYSQL_NUM)) { // (5) Print out each element in $row, that is, print the values of // the attributes foreach ($row as $attribute) print "{$attribute} "; // Print a carriage return to neaten the output print "\n"; } ?> </pre> </body> </html> MySQL functions should be used with the @ operator that suppresses default output of error messages by the PHP script engine. Omitting the @ operator produces messages that contain both the custom error message and the default error message produced by PHP. Consider an example where the string localhost is misspelled, and the @ operator is omitted: if (!($connection = mysql_connect("localhos", "fred",:"shhh") )) die("Could not connect"); This fragment outputs the following error message that includes first the PHP error and second the custom error message: Warning: mysql_connect( ) [function.mysql-connect]: Unknown MySQL Server Host 'localhos' (2) in bug.php on line 42 Could not connect. The error handling approach we've described here works well when you're developing and testing an application. However, when your application is finished and in production, it isn't a good approach: the error messages that are output interrupt the look and feel of the application, and stopping the processing with the die( ) function is likely to result in non-compliant HTML. We show you how to build a production error handler in Chapter 12. 6.1.4 Working with Table StructuresExample 6-4 is a script that uses the mysql_fetch_field( ) function to discover information about attributes in a table; field is another way of saying attribute, and you'll also find some database users call it a column . The script emulates most of the behavior of the SHOW COLUMNS or DESCRIBE commands discussed in Chapter 15. The code uses the same five-step query process discussed earlier, with the exception that mysql_fetch_field( ) is used in place of mysql_fetch_array( ). Sample output for the table wine is shown in Example 6-5. Example 6-4. Using mysql_fetch_field( ) to describe the structure of a table<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html401/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <title>Wine Table Structure</title> </head> <body><pre> <?php // Open a connection to the server and USE the winestore $connection = mysql_connect("localhost","fred","shhh"); mysql_select_db("winestore", $connection); // Run a query on the wine table in the winestore database to retrieve // one row $result = mysql_query ("SELECT * FROM wine LIMIT 1", $connection); // Output a header, with headers spaced by padding print str_pad("Field", 20) . str_pad("Type", 14) . str_pad("Null", 6) . str_pad("Key", 5) . str_pad("Extra", 12) . "\n"; // How many attributes are there? $x = mysql_num_fields($result); // for each of the attributes in the result set for($y=0;$y<$x;$y++) { // Get the meta-data for the attribute $info = mysql_fetch_field ($result); // Print the attribute name print str_pad($info->name, 20); // Print the data type print str_pad($info->type, 6); // Print the field length in brackets e.g.(2) print str_pad("({$info->max_length})", 8); // Print out YES if attribute can be NULL if ($info->not_null != 1) print " YES "; else print " "; // Print out selected index information if ($info->primary_key == 1) print " PRI "; elseif ($info->multiple_key == 1) print " MUL "; elseif ($info->unique_key == 1) print " UNI "; // If zero-filled, print this if ($info->zerofill) print " Zero filled"; // Start a new line print "\n"; } ?> </pre> </body> </html> Example 6-5. HTML output of the DESCRIBE WINE emulation script in Example 6-4<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html401/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <title>Wine Table Structure</title> </head> <body><pre> Field Type Null Key Extra wine_id int (1) PRI wine_name string(9) MUL type string(9) year int (4) winery_id int (1) MUL description blob (0) YES </pre> </body> </html> 6.1.5 Formatting ResultsSo far we've shown you the basic techniques for connecting to and querying a MySQL server using PHP. In this section, we extend this to produce results with embedded HTML that have better structure and presentation. We extend this further in Chapter 7, where we show you how to separate HTML from PHP code using templates. Let's consider an example that presents results in an HTML table environment. Example 6-6 shows a script to query the winestore database and present the details of wines. Previously, in Example 6-1 and Example 6-3, the details of wines were displayed by wrapping the output in HTML <pre> tags. The script in Example 6-6 uses the function displayWines( ) to present the results as an HTML table. The main body of the script has a similar structure to previous examples, with the exceptions that the query is stored in a variable, and the username, password, and the showerror( ) function are stored in separate files and included in the script with the require directive. We introduced the require directive in Chapter 2 and discuss it in more detail later in this section. The displayWines( ) function first outputs a <table> tag, followed by a table row <tr> tag with six <th> header tags and descriptions matching the six attributes of the wine table. We could have output these using mysql_fetch_field( ) to return the attribute names rather than hard-coding the heading names. However, in most cases, the headers are hard-coded because attribute names aren't meaningful to users. Also, as we discuss later, giving users details about your database design can contribute to a security problem. Example 6-6. Producing simple table output with MySQL<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html401/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <title>Wines</title> </head> <body> <?php require 'db.inc'; // Show the wines in an HTML <table> function displayWines($result) { print "<h1>Our Wines</h1>\n"; // Start a table, with column headers print "\n<table>\n<tr>\n" . "\n\t<th>Wine ID</th>" . "\n\t<th>Wine Name</th>" . "\n\t<th>Type</th>" . "\n\t<th>Year</th>" . "\n\t<th>Winery ID</th>" . "\n\t<th>Description</th>" . "\n</tr>"; // Until there are no rows in the result set, fetch a row into // the $row array and ... while ($row = @ mysql_fetch_row($result)) { // ... start a TABLE row ... print "\n<tr>"; // ... and print out each of the attributes in that row as a // separate TD (Table Data). foreach($row as $data) print "\n\t<td> {$data} </td>"; // Finish the row print "\n</tr>"; } // Then, finish the table print "\n</table>\n"; } $query = "SELECT * FROM wine"; // Connect to the MySQL server if (!($connection = @ mysql_connect($hostname, $username, $password))) die("Cannot connect"); if (!(mysql_select_db($databaseName, $connection))) showerror( ); // Run the query on the connection if (!($result = @ mysql_query ($query, $connection))) showerror( ); // Display the results displayWines($result); ?> </body> </html> After producing the HTML <table> open tag, the displayWines( ) function retrieves the rows in the result set, showing each row as a separate table row using the <tr> tag. Each attribute value for each wine, where the attributes match the headings, is displayed within the row as table data using the <td> tag. Carriage returns and tab characters are used to lay out the HTML for readability; this has no effect on the presentation of the document by a web browser, but it makes the HTML much more readable if the user views the HTML source. It also makes debugging your HTML easier. The results of using a table environment instead of <pre> tags are more structured and more visually pleasing. The output in the Mozilla browser is shown in Figure 6-1, along with a window showing part of the HTML source generated by the script. The downside of the approach we've shown is that the HTML is embedded in the script, making it difficult to work with the presentation and the code separately. In our simple example, this isn't a huge problem. In a larger application such as our online winestore, it makes changing the overall look and feel of the application difficult, and it can also make the code harder to modify. In Chapter 7, we show you how to solve this problem using templates. Figure 6-1. Presenting wines from the winestore in an HTML table environment6.1.6 Using Require Files in PracticeExample 6-7 shows the file included with the require directive in Example 6-6. As discussed in Chapter 2, the require directive allows common functions, variables, and constants in other files to be accessible from within the body of a script without directly adding the functions to the code. Example 6-7. The db.inc require file<?php $hostName = "localhost"; $databaseName = "winestore"; $username = "fred"; $password = "shhh"; function showerror( ) { die("Error " . mysql_errno( ) . " : " . mysql_error( )); } ?> A require file is usually referenced by all code developed for an application and, in this case, allows easy adjustment of the database server name, database name, and server username and password. The flexibility to adjust these parameters in a central location allows testing of the system on a backup or remote copy of the data, by changing the database name or hostname in one file. This approach also allows the use of different username and password combinations with different privileges, for testing purposes. We have chosen to name our include files with the .inc extension. This presents a minor security problem. If the user requests the file, the source of the file is shown in the browser. This may expose the username and password for the server, the source code, the database structure, and other details that should be secure. There are three ways to address this problem:
All three approaches to securing require files work effectively in practice. Using the extension .php for require files is the simplest solution but has the disadvantage that require files can't be easily distinguished from other files; however, this is the best approach if you're in a shared hosting environment and can't change Apache's configuration. In the online winestore, we have configured Apache to disallow retrieval of files with the extension .inc. We did this by adding the following lines to Apache's httpd.conf file, and restarting the web server: <Files ~ "\.inc$"> Order allow,deny Deny from all </Files> 6.1.7 Case Study: Producing a Select ListTo conclude this section, we present a longer case study of dynamically producing values for an HTML select input type in a form. The example shows you how the PHP MySQL functions can be put to use to develop one of the components of an application. You'll find this a useful tool when you want the user to choose an item from a list of values stored in the database. Consider an example where we want our users to be able to choose one of the wine regions from a drop-down list so that we can display the wineries in the area. For the wine regions, the select input might have the following structure: <select name="regionName"> <option value="All">All</option> <option value="Barossa Valley">Barossa Valley</option> <option value="Coonawarra">Coonawarra</option> <option value="Goulburn Valley">Goulburn Valley</option> <option value="Lower Hunter Valley">Lower Hunter Valley</option> <option value="Margaret River">Margaret River</option> <option value="Riverland">Riverland</option> <option value="Rutherglen">Rutherglen</option> <option value="Swan Valley">Swan Valley</option> <option value="Upper Hunter Valley">Upper Hunter Valley</option> </select> With only a small number of wine regions, it's tempting to develop a static HTML page with an embedded list of region names. However, this is poor design. If the region database table changes because you add, delete, or change a region_name value, you have to remember to update the HTML page. Moreover, a spelling mistake or an extra space when creating the HTML page renders a select option useless, because it no longer matches the values in the database when used in a query. A better approach is to dynamically query the database and produce a select element using the region_name values stored in the region table. Let's consider dynamically producing HTML. First, the set of different values of the region_name attribute in the region table need to be retrieved. Then, the values need to be formatted as HTML option elements and presented as an HTML form to the user. When the user chooses a region and submits the form, a query needs to be run that uses the region name the user selected as one of the query parameters to match against data in the database and to produce a result set. Because the values chosen by the user in the form are compared against database values, it makes sense that the list values should originate from the database. We show you how to incorporate user data in a query in the next section. In this section, we develop a component that can be reused to produce select lists in different modules of a web database application. An example fragment that uses this new component is shown in Example 6-8. The selectDistinct( ) function that produces the drop-down list isn't shown and we show you it in the next section. Example 6-8. Producing an HTML form that contains a database-driven select list<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html401/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <title>Wines</title> </head> <body> <form action="example.6-14.php" method="GET"> <?php require "db.inc"; // selectDistinct( ) function shown in Example 6-9 goes here require "example.6-9.php"; // Connect to the server if (!($connection = @ mysql_connect($hostName, $username, $password))) showerror( ); if (!mysql_select_db($databaseName, $connection)) showerror( ); print "\nRegion: "; // Produce the select list // Parameters: // 1: Database connection // 2. Table that contains values // 3. Attribute that contains values // 4. <SELECT> element name // 5. Optional <OPTION SELECTED> selectDistinct($connection, "region", "region_name", "regionName", "All"); ?> <br> <input type="submit" value="Show Wines"> </form> </body> </html> The component itself is discussed later but is encapsulated in the function selectDistinct( ), which takes the following parameters:
The output of the function for the parameters used in Example 6-8 is shown in Figure 6-2. Figure 6-2. The selectDistinct( ) function in actionThe remainder of the script fragment in Example 6-8 produces the other required tags in the HTML document. 6.1.7.1 Implementing the selectDistinct( ) functionThis section details the implementation of the general-purpose selectDistinct( ) function. The function produces a select list, with an optional selected item, using attribute values retrieved from a database table. The body of the function is shown in Example 6-9. Example 6-9. The body of the selectDistinct( ) function for producing select lists<?php function selectDistinct ($connection, $tableName, $attributeName, $pulldownName, $defaultValue) { $defaultWithinResultSet = FALSE; // Query to find distinct values of $attributeName in $tableName $distinctQuery = "SELECT DISTINCT {$attributeName} FROM {$tableName}"; // Run the distinctQuery on the databaseName if (!($resultId = @ mysql_query ($distinctQuery, $connection))) showerror( ); // Start the select widget print "\n<select name=\"{$pulldownName}\">"; // Retrieve each row from the query while ($row = @ mysql_fetch_array($resultId)) { // Get the value for the attribute to be displayed $result = $row[$attributeName]; // Check if a defaultValue is set and, if so, is it the // current database value? if (isset($defaultvalue) && $result == $defaultValue) // Yes, show as selected print "\n\t<option selected value=\"{$result}\">{$result}"; else // No, just show as an option print "\n\t<option value=\"{$result}\">{$result}"; print "</option>"; } print "\n</select>"; } // end of function ?> The implementation of selectDistinct( ) is useful for most cases in which a select list needs to be produced. The first section of the code queries the table $tableName passed as a parameter and produces a select element with the name attribute $pulldownName. The second part of the function retrieves the database results row by row using a while loop. Inside the while loop, the value of the attribute to be displayed is saved in $result and then an option element is printed using that value. If a $defaultValue is passed through as a parameter and the current value in $result is equal to the default, the code produces the option as the selected option. If there's no default value or the current value doesn't match the default value, the current value is output without the selected attribute. General-purpose, database-independent or table-independent code is a useful addition to a web database application. Similar functions to selectDistinct( ) can be developed to produce radio buttons, checkboxes, multiple-select lists, or even complete form pages based on a database table. As we discussed in the previous section, the code can be improved with the use of templates that we show you in Chapter 7, and you'll find a template version of the code in this section on our book's web site http://www.webdatabasebook.com/. |