Case Study: Producing a select List (Web Database Applications with PHP & MySQL)

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:

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.

The output of the function for the parameters used in Example 5-12 is shown in Figure 5-9.

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.

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

5.4. Case Study: Producing a select List

Example 5-12. Producing an HTML <form> that contains a database-driven select list

Figure 5-9. The selectDistinct( ) function in action

5.4.1. Implementing the selectDistinct Function