Web Database Applications with PHP \& MySQLWeb Database Applications with PHP \& MySQLSearch this book

5.4. 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
</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:

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

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:

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.



Library Navigation Links

Copyright © 2003 O'Reilly & Associates. All rights reserved.