PHP —

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.