Creating a Dynamic Drop-down Box with PHP and MySQL
Just the function in plain text.
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. 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
// Kim Briggs 2006-05. http://kimbriggs.com/computers/
// Function completes a drop-down box
// by dynamically querying ID-Name pair from a lookup table
//
// intID = Integer "ID" field of table, usually the primary key
// strName = Name field that user picks as a value, ordered by this field
// tableName = Name of MySQL table containing intID and strName
// strOrderField = Which field you want results sorted by
// strMethod = Sort as asc=ascending (default) or desc for descending
function drop_down($intID, $strName, $tableName, $strOrderField, $strMethod="asc") {
$strQuery = "select $intID, $strName from $tableName order by $strOrderField $strMethod";
$rsrcResult = mysql_query($strQuery);
while($arrayRow = mysql_fetch_assoc($rsrcResult)) {
$intIdField = $arrayRow["$intID"];
$strNameField = $arrayRow["$strName"];
echo "<option value=\"$intIdField\">$strNameField</option>\n";
}
echo "</select>\n\n";
}
?>
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 a directory called functions within the working directory of the main script.
<head> <title>My Title</title> <?php include("functions/dropdown.inc") ?> </head> - Within PHP, just call the function and its arguments:
<p>Location:<br> <select name="intLocation_id"> <option value="NONE">Select Location</option> <?php drop_down(location_id, location_name, location, location_name); ?></p>
