Creating a Dynamic Drop-down Box with PHP and MySQL
Update 2009-04:This function had some functionality added. The additional field "strNameOrdinal" allows the function to be used for multiple queries to the same table-field combination on the same page. For example, you can be collecting data for an array, such as "item_name1", "item_name2", "item_name3", etc.
Update 2008-09:I put the entire drop-down box HTML within the function so that it is more self-contained. Suggest you put an HTML comment right before the function, like in the example below. Plain text file at bottom was tested, but please alert me to typos, thanks.
Here is a "quick and dirty" way to get a drop-down box in an HTML page to show the latest values from a MySQL database. Notice that there is a significant lack of error-handling. It sure did help clean up my HTML form, though.
Assumptions: You already have a mysql connection object open and a MySQL database selected. [I now have a MySQL Connection Function available]. By default, the mysql_query function will use the latest values. Here is a function that you can separate out as an include file and call as needed. I don't know if the starting and ending php tags are needed, but it works and makes it much easier to edit in Bluefish.
<?php
function dropdown($intIdField, $strNameField, $strTableName, $strOrderField, $strNameOrdinal, $strMethod="asc") {
//
// PHP DYNAMIC DROP-DOWN BOX - HTML SELECT
//
// 2006-05, 2008-09, 2009-04 http://kimbriggs.com/computers/
//
// Function creates a drop-down box
// by dynamically querying ID-Name pair from a lookup table.
//
// Parameters:
// intIdField = Integer "ID" field of table, usually the primary key.
// strMethod = Sort as asc=ascending (default) or desc for descending.
// strNameField = Name field that user picks as a value.
// strNameOrdinal = For multiple drop-downs to same table on same page (Ex: strNameField.$i)
// strOrderField = Which field you want results sorted by.
// strTableName = Name of MySQL table containing intIDField and strNameField.
//
// Returns:
// HTML Drop-Down Box Mark-up Code
//
echo "<select name=\"$strNameOrdinal\">\n";
echo "<option value=\"NULL\">Select Value</option>\n";
$strQuery = "select $intIdField, $strNameField
from $strTableName
order by $strOrderField $strMethod";
$rsrcResult = mysql_query($strQuery);
while($arrayRow = mysql_fetch_assoc($rsrcResult)) {
$strA = $arrayRow["$intIdField"];
$strB = $arrayRow["$strNameField"];
echo "<option value=\"$strA\">$strB</option>\n";
}
echo "</select>";
}
?>
And here are examples of how it looks when used in a script:
- Include a file that contains the code in the head section. Mine is in file called "lib.inc" within the main include directory specified by "include_path" in my php.ini file.
<head>
<title>My Title</title>
<?php require_once("lib.inc") ?>
</head> - Within PHP, just call the function and its arguments. This example has hard-coded values to show sample values. You can also put variables here and define them programatically in your script.
<p>Location:<br /><!-- Drop-down Box queries A_TABLE_NAME table in A_DB_NAME -->
<?php dropdown(location_id, location_name, location, location_name, location_name1); ?></p>
Here is just the drop-down function in plain text.
