We have already identified that the scripts in most of this chapter's examples require that the user remember and reproduce the names of the wine regions. A far better approach-and one that works well for small numbers of items-is to present values using the HTML <select> input type. For the wine regions, the <select> input has the following structure:
<select name="regionName"> <option selected> All <option> Barossa Valley <option> Coonawarra <option> Goulburn Valley <option> Lower Hunter Valley <option> Margaret River <option> Riverland <option> Rutherglen <option> Swan Valley <option> Upper Hunter Valley </select>
With only a small number of wine regions, it is tempting to develop a static HTML page with an embedded list of region names. However, this is poor and inflexible. If the region database table changes-that is, new regions are added or deleted or you want to 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 for querying. A better approach is to use the techniques from Chapter 4 to dynamically query the database and produce a <select> element using the region_name values stored in the region table.
Consider the approach of dynamically producing HTML. First, you retrieve the set of different values of the region_name attribute in the region table. Then, you format the values as HTML <option> elements and present a HTML <form> to the user. When the user chooses a region and submits the <form>, you should run a query 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.
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 that uses this new component is shown in Example 5-12.
Example 5-12. Producing an HTML <form> that contains a database-driven select list
// Connect to the DBMS
if (!($connection = @ mysql_connect($hostName,
$username,
$password)))
showerror( );
if (!mysql_select_db($databaseName, $connection))
showerror( );
echo "\nRegion: ";
// Produce the select list
// Parameters:
// 1: Database connection
// 2. Table that contains values
// 3. Attribute that contains values
// 4. <SELECT> element name
// 5. An additional non-database value
// 6. Optional <OPTION SELECTED>
selectDistinct($connection,
"region",
"region_name",
"regionName",
"All",
"All");
echo "\n<br><input type=\"submit\"" .
"value=\"Show wines\">" .
"\n</form>\n<br>";
echo "<a href=\"Preface.htmll\">Home</a>";
The component itself is discussed later but is encapsulated in the function selectDistinct( ), which takes the following parameters:
-
A database connection handle, in this case, a connection opened with
mysql_connectand stored in$connection. -
A database name,
$database, which is a variable that is set towinestorein the include file db.inc, as discussed in Chapter 4. -
The database table from which to produce the list. In this case, the table region contains the region name data.
-
The database table attribute with the values to be used as the text for each
<option>element shown to the user in the list. In this example, it'sregion_namefrom the region table. -
The name of the HTML
<select>element. We useregionName, but this can be anything and isn't dependent on the underlying database. -
An additional option to add to the list if required; the value
Alldoesn't occur in the region database table but is an extra value added to the list. -
An optional default value to output as the
<option selected>in the list; this option is shown as selected when the user accesses the page.Allis used as a default here.
The output of the function for the parameters used in Example 5-12 is shown in Figure 5-9.
Figure 5-9. The selectDistinct( ) function in action
The remainder of the script fragment in Example 5-12 produces the other required tags in the HTML document.