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
$defaultValuepassed through as a parameter, the code produces an option for each value in$resultBufferwith no<option selected>. -
If there is a
$defaultValue, the code iterates through the$resultBufferto 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.
