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

Buy gold online - quickly, safely and at low prices

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