Printed from http://kimbriggs.com

Creating a Dynamic Drop-down Box with PHP and MySQL

Update 2012-10: The function can be reused for multiple drop-downs on the same page and specify the mask that shows up in the dropdown box for each one.

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 myDropdown($intIdField, $strNameField, $strTableName, $strNameOrdinal, $strMaskName, $strOrderField, $strMethod="asc") {

   //
   // PHP DYNAMIC DROP-DOWN BOX - HTML SELECT
   //
   // 2012-10 - 2006-05 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.
   // 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:

  1. 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>

  2. 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");
    ?>

  3. And the finished HMTL would look like this (with all the states in it).

Spider Oak Online Backup

 
CC License Ubuntu OpenOffice GIMP Graphics Geany Editor Firefox php.net Play Ogg what's this?