Case Study: Producing a select List

To conclude this chapter, we present a short case study of dynamically producing <form> components from a database. The techniques used are an application of the five-step querying process from Chapter 4.

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

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,
     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>
  echo "\n<br><input type=\"submit\"" .
       "value=\"Show wines\">" .
  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_connect and stored in $connection.

  • A database name, $database, which is a variable that is set to winestore in the include file, 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's region_name from the region table.

  • The name of the HTML <select> element. We use regionName, 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 All doesn'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. All is 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.