Summary
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 Database 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 Geany.
<?php
function myDropdown($intIdField, $strNameField, $strTableName, $strNameOrdinal, $strMaskName, $strOrderField, $strMethod="asc") {
//
// PHP DYNAMIC DROP-DOWN BOX - HTML SELECT
//
// 2012-10 http://kimbriggs.com
//
// 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.
// strMaskName = What shows up first in the drop-down box.
// strMethod = Sort as asc=ascending (default) or desc for descending.
// strNameField = Name field that user picks as a value.
// strNameOrdinal = $_POST name handles multiple drop-downs on one page.
// 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\">".$strMaskName."</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. Here is an example for a State Table.
<?php $intIdField = 'state_id'; $strNameField = 'state_name'; $strTableName = 'tbl_states'; $strNameOrdinal = 'state_id_mg'; $strMaskName = 'Select State'; $strOrderField = 'state_name'; ... myDropdown($intIdField, $strNameField, $strTableName, $strNameOrdinal, $strMaskName, $strOrderField, $strMethod="asc"); ?> - And the finished HMTL would look like this (with all the states in it).
