Recipe 7.5. Generating Form Menu Choices from a Database

Problem

You want to create dynamic form menus from values stored in a database.

Solution

Create a PHP function that compiles all the values in a database table into a select menu that can be placed in a form. Then call the function as needed in various places on your web site:

 <? echo makeSelectList(dbTable, dbFieldValue, dbFieldDisplay, menuField); ?> 

Discussion

The ability to grow painlessly is one of the most important qualities of a successful web site. If you have one or more forms on your site that require visitors to make choices from a large and ever-changing menu of options, then you may be able to save a lot of time by storing the menu choices in a database and using a PHP function to generate forms menus from records in that database.

This Recipe makes a couple of assumptions. Since it uses PHP scripting to manipulate the text, you'll need some familiarity with the use of variables and functions in that language. Also, you'll need to have access to an SQL database and some familiarity with creating queries for it.

For the function to work properly, you'll need to have an active connection to your web database. A basic connection to a MySQL database with PHP looks like this:

 $dbName = "mydatabase"; $dbConnection = mysql_connect("dbhost","dbuser", "dbpassword")   or die("Couldn't Connect."); $db = mysql_select_db($dbName, $dbConnection)   or die("Couldn't select database."); 

I recommend saving these three lines in an include file outside your web site's root directory, and then including it on any page where you'll need to use the makeSelectList function.

Now, on to the function itself. First, list the parameters that can be sent to the function; in this example, there are seven:

 function makeSelectList( $dbTable,                          $dbFieldValue,                          $dbFieldDisplay = "",                          $menuField,                          $idSelected = "--",                          $size = 1,                          $defaultText = "--") { 

The first four define the table name, two fields, and the name of the HTML form element. The other three have default values in the function and are optional when the function is called from a web page. They will let you preset a selected value and default display text for the select menu that gets created, and convert the select menu to a select list by increasing size to something greater than 1.

Next, set up the variable that will become the HTML code product of the function ($menu) and the database query:

 $menu = "";      $query = "SELECT $dbFieldValue, $dbFieldDisplay " .              "FROM $dbTable ORDER BY $dbFieldDisplay";     $resultResource = mysql_query($query);  

The bulk of the function is embedded in an if statement that tests for the existence of a result to the database query ($resultResource):

 if ($resultResource)     {       $resultRows = mysql_num_rows($resultResource);       $menu .= "<select name=\"$menuField\" size=\"$size\">\n"; 

Inside the conditional, the function gets a variable for the number of records found with the query ($resultRows) and begins to create the select menu stored in $menu.

 if ($idSelected == "--")     {     $menu .= "<option value=\"--\" label=\"--\" " .              "selected>$defaultText</option>\n";     } 

Then, if the function hasn't been given a new value for $idSelected, a first option is added to $menu with the default values. Next, the function adds options for each found record:

 for ($rowIndex=0; $rowIndex<$resultRows; $rowIndex++) {   $id = mysql_result($resultResource, $rowIndex, 0);   $name = mysql_result($resultResource, $rowIndex, 1);   if ($idSelected == $id)     $menu .= "<option value=\"$id\" label=\"$name\" " .              "selected>$name</option>\n";   else     $menu .= "<option value=\"$id\" label=\"$name\">" .              "$name</option>\n"; } 

The for loop goes through each record in the found set and assigns values to two variables ($id and $name) for the two fields requested with the query. In the middle of the loop, the function checks the $id variable against the value of $idSelected. If they match, then that option in the menu will be selected.

Finally, the function closes the select menu, clears the query results from PHP's memory, and returns the HTML code stored in $menu:

 $menu .= "</select>\n";       mysql_free_result($resultResource);     }   return $menu; } 

Table 7-1 shows the rows, or records, in a hypothetical database table of favorite colors. I'll call this table favcolors in the examples that follow.

Table 7-1. This table lists the sample data to be displayed as a select menu

colorID	colorName
1	Red
2	Orange
3	Yellow
4	Green
5	Blue
6	Purple
7	Black
8	White
9	Pink
10	Brown

A typical function call might look like this:

 <? echo makeSelectList(favcolors, colorID, colorName, favcolor, "--", 1, "Select your favorite color") ?> 

and the resulting code, like this:

 <select name="favcolor" size="1">  <option value="--" label="--" selected>Select your favorite color</option>  <option value="1" label="Red">Red</option>  <option value="2" label="Orange">Orange</option>  <option value="3" label="Yellow">Yellow</option>  <option value="4" label="Green">Green</option>  <option value="5" label="Blue">Blue</option>  <option value="6" label="Purple">Purple</option>  <option value="7" label="Black">Black</option>  <option value="8" label="White">White</option>  <option value="9" label="Pink">Pink</option>  <option value="10" label="Brown">Brown</option> </select> 

To create a list of valuesrather than a menuwith one option already selected, use this function call, which leaves off the last argument for $defaultText:

 <? echo makeSelectList(favcolors, colorID, colorName, favcolor, 7, 10) ?> 

The results are shown in Figure 7-4.

Figure 7-4. A select list generated from values in a database table

The complete PHP function looks like this:

 function makeSelectList( $dbTable,                          $dbFieldValue,                          $dbFieldDisplay = "",                          $menuField,                          $idSelected = "--",                          $size = 1,                          $defaultText = "--") { $menu = "";     $query = "SELECT $dbFieldValue, $dbFieldDisplay " .              "FROM $dbTable ORDER BY $dbFieldDisplay";     $resultResource = mysql_query($query); if ($resultResource)     {       $resultRows = mysql_num_rows($resultResource);       $menu .= "<select name=\"$menuField\" size=\"$size\">\n";       if ($idSelected == "--")           {           $menu .= "<option value=\"--\" label=\"--\" " .                    "selected>$defaultText</option>\n";           }       for ($rowIndex=0; $rowIndex<$resultRows; $rowIndex++)       {         $id = mysql_result($resultResource, $rowIndex, 0);         $name = mysql_result($resultResource, $rowIndex, 1);         if ($idSelected == $id)           $menu .= "<option value=\"$id\" label=\"$name\" " .                    "selected>$name</option>\n";         else           $menu .= "<option value=\"$id\" label=\"$name\">" .                    "$name</option>\n";       } $menu .= "</select>\n";         mysql_free_result($resultResource);     }   return $menu; }