A common question on the PHP mailing list is "How do I populate a select list with items in a database?". Thank the maker for the while construct, because it makes this all very easy!

In Chapter 7, "Advanced PHP Techniques: Web-Based Database Adminstration" of PHP Essentials, I use very verbose coding techniques to match, then print, an <OPTION> field within a <SELECT> form element (drop-down list).

I'm just going to dive right in here, and show you how to use a while loop to get a list from a database table. If you are having trouble following the concept, blame it on me for now explaining it thoroughly, and contact me to help fill in the gaps.

Suppose you have a database table called OPERATING_SYSTEMS, which contains one field (OS) and looks something like this:

The goal is to bring the contents of the OPERATING_SYSTEMS table into some sort of form you're making, so you would do the following with MySQL (you can substitute any database connectivity code that you would normally use).

First, start an HTML shell and add your basic database connectivity code, including the SQL statement:

Next, since the values will be represented within the context of a form, pretend that you have a form. I've made a fake one, below. In a few minutes, we'll create a string called $option_block, which will contain all of the <OPTION> variables created by sucking data out of the MySQL table (OPERATING_SYSTEMS). That's why the echo statement appears in the code below.

Now go back into your PHP code, and add the while loop that will get the list of operating systems and put them in <OPTION> elements. First, separate the results by row, using the mysql_fetch_array function:

The while loop will create an array called $row for each record in the result set. To get the individual elements of the record (in this case, simply OS), create specific variables:

Put the value inside an <OPTION> element. Remember, we'll be adding to the $option_block string as the loop continues, so use the concatenation operator (.=) instead of the assignment operator:

The entire while loop looks like this:

From top to bottom, this code looks something like this:

Using the code above, the loop will print a drop-down list box and submit button like the following:

Suppose, however, that this form was part of a "Modify Record" script, where you were pulling a record's values from a database table, and pre-populating a form with the values of that record. One simple modification to the above code will help you to pre-select an item in the drop-down list box.

If the record contains a value called $curr_os, and you want to pre-select an item in the drop-down list box based on that values, simply re-write the while loop like this:

Using this new code, and supposing that the value of $curr_os was "Solaris", the loop will print a drop-down list box and submit button like the following: