featured computer 850

PHP – Dynamic Drop-down Box for MySQL DB


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.

function myDropdown($intIdField, $strNameField, $strTableName, $strNameOrdinal, $strMaskName, $strOrderField, $strMethod="asc") {

   // 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.
   // strOrderField = Which field you want results sorted by.
   // strTableName = 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.

<title>My Title</title>
<?php require_once("lib.inc") ?>

Within PHP, just call the function and its arguments. Here is an example for a State Table.

$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");

Similar Posts