5.4 Case Study: Producing a select List

only for RuBoard - do not distribute or recompile

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 </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=\"index.html\">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 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'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.

5.4.1 Implementing the selectDistinct Function

This section details the implementation of the generic selectDistinct( ) function. The function produces a <select> list with an optional <option selected> element using attribute values retrieved from a database table. One additional non-database item can be added to the list. The body of the function is shown in Example 5-13.

Example 5-13. The body of the selectDistinct( ) function for producing select lists

   function selectDistinct ($connection,                             $tableName,                             $columnName,                             $pulldownName,                             $additionalOption,                             $defaultValue)   {      $defaultWithinResultSet = FALSE;      // Query to find distinct values of $columnName      // in $tableName      $distinctQuery = "SELECT DISTINCT $columnName                        FROM $tableName";      // Run the distinctQuery on the databaseName      if (!($resultId = @ mysql_query ($distinctQuery,                                       $connection)))         showerror(  );      // Retrieve all distinct values      $i = 0;      while ($row = @ mysql_fetch_array($resultId))         $resultBuffer[$i++] = $row[$columnName];      // Start the select widget      echo "\n<select name=\"$pulldownName\">";             // Is there an additional option?      if (isset($additionalOption))         // Yes, but is it the default option?         if ($defaultValue == $additionalOption)            // Show the additional option as selected            echo "\n\t<option selected>$additionalOption";         else            // Just show the additional option            echo "\n\t<option>$additionalOption";      // check for a default value      if (isset($defaultValue))      {         // Yes, there's a default value specified         // Check if the defaultValue is in the         // database values         foreach ($resultBuffer as $result)            if ($result == $defaultValue)               // Yes, show as selected               echo "\n\t<option selected>$result";            else               // No, just show as an option               echo "\n\t<option>$result";      }  // end if defaultValue      else       {          // No defaultValue                 // Show database values as options         foreach ($resultBuffer as $result)            echo "\n\t<option>$result";      }      echo "\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, extracts the values of the attribute $columnName also passed as a parameter into an array $resultBuffer, and produces a <select> element with the name attribute $pulldownName. The code is a five-step querying module.

The remainder of the code deals with the possible cases for a default value passed though as $defaultValue:

If there is an $additionalOption, it is output as an <option>. If it is also the default option, it is output as the <option selected>.
If there is no $defaultValue passed through as a parameter, the code produces an option for each value in $resultBuffer with no <option selected>.
If there is a $defaultValue, the code iterates through the $resultBuffer to see if this value is in the result set. If the value does occur in the $resultBuffer, it is output as the <option selected>.

The regionName select list for the online winestore has the default option of All which isn't a region in the region table and this is added manually to the list of options the user can choose from.

Generic, database-independent or at least table-independent code is a useful addition to a web database application. Similar functions to selectDistinct( ) can be developed using the same five-step process to produce radio buttons, checkboxes, multiple-select lists, or even generic complete <form> pages based on a database table.

only for RuBoard - do not distribute or recompile