Drop-down lists have long been a form designer’s friend in terms of packing a lot of choices into a single area. The maintenance of these however can become tedius over time if you must continually update the list.

In the static Web 1.0 days, we would have coded each option in the list. Fortunately through the use of a server-side language and a database we can make this a lot more efficient.

In this example I will showcase how to do this in PHP. For those that use ASP.NET, I’m pretty sure you are accustomed to the drag-n-drop capabilities of Visual Studio, where you simply drag-n-drop and set your connection settings accordingly.

In PHP however, it is a little more work. In the following I will have assumed you already have a table named states created with 3 columns (I used state_id, state, state_abbr - *If you haven’t created the table, Kim Briggs has a wonderful sql example at http://kimbriggs.com/computers/computer-notes/mysql-notes/mysql-create-state-table.file .*

I will also assume that you have already connected to the database in your file.

We will begin first by creating a variable which will hold our SQL query. From there we will create a variable that will hold the result.

<?
$query="SELECT * FROM state";
$result = mysql_query ($query);

Then we shall begin the drop down list:

echo "<select name='state' value=''>State</option>";

And we will use a while statement to create an array to cycle through the results printing HTML options for each item. As you will see, we will be binding the columns to the values that we want to print on the page.

while($myfetch=mysql_fetch_array($result)){
echo "<option value=$myfetch[state_id]>$myfetch[state_abbr]</option>";
}

From there we will close the list and the PHP file.:

echo "</select>";
?>

And for the code in its complete form:

 <?
  //Create SQL Query
  $query="SELECT * FROM state";
  // Variable for results
  $result = mysql_query ($query);
  // Begin select tag
  echo "<select name=state value=''>State</option>";
  //Return each result
  while($myfetch=mysql_fetch_array($result))
  {
     echo "<option value=$myfetch[state_id]>$myfetch[state_abbr]</option>";
  }
  //End select
  echo "</select>";
?>

Easy enough huh? Do note that with the query, we can control how the results are printed (IE alphabetical, by id, etc.). This can be used for a variety of fields, from states to categories.