Populating a Select Box With PHP and MySQL – PHP Tutorial
I’m going to assume that you already have your database connection set up, so we don’t need to go into that type of detail. Let’s start with the query. For this example, I’m going to populate the select box with a list of category names and category IDs.
<select name="categoryID">
<?php
$sql = "SELECT category_id, category_name FROM categories ".
"ORDER BY category_name";
$rs = mysql_query($sql);
while($row = mysql_fetch_array($rs))
{
echo "<option value=\"".$row['category_id']."\">".$row['category_name']."\n ";
}
?>
</select>
An HTML select box needs to be used with an HTML form, so the first line sets up the select box as a form input and declares the form variable name, which in this case is categoryID. Next, we read the category table and place the category names in alphabetical order in a recordset. The WHILE loop reads each of the rows in the recordset and generates the OPTION list.
Note the backslashes in the line that generates the options. The backslash denote an “escape character”. An escape is needed in an ECHO statement if you want a special character following the escape to be displayed, rather than processed. In this case, there are some quotes that we want to display, rather than using them to delineate segments of the string. The \n near the end of the line is a special escape sequence known as a newline. A newline is basically a carriage return that is generated in the viewable HTML code. It it not necessary to use it, but if you do not, each OPTION will appear in the HTML code next to each other horizontally, rather than stacked, which is more readable. A newline does not generate a line break in the web page that is generated. It only works as a way to format the HTML code that is generated by PHP to make it more readable.
The result will be a category select box that looks like this.
The real benefit with using a script like this to read data from a table is when you have selections that change over time, or when you need to display the same selection on multiple forms with in a web site. Category selections and category names have a tendency to change periodically. It is easier to maintain this type of data in a table, rather than hard-coding it in HTML in a web page. Also, if you need to display a SELECT box list on multiple pages in a web site, it is much easier to maintain the data in a table. Otherwise, when the data changes, you would have to make changes to multiple pages in your web site.
Nathan Says:
Nice one! I’m an artist and not a coder (!!), but I got it straight away and edited it to my table and it worked for me first go! Many, many thanks.
Syd Says:
Many thanks – Have spent an age looking for a well explained simple example.
KK Says:
This code is awesome.. exactly what i was looking for! Got it up and running in no time at all.
Thank you very much!
Patrick Says:
My problem is a little different. I have a select box with the states all filled in. If the page is refreshed the select box reverts to the first item in the list. How do I retain the selected index and display it after a refresh?
Doogie Says:
Hi Patrick
There isn’t any way to save the information if someone makes a selection and then simply refreshes the form, because no value has been stored anywhere. Sometimes a browser will cache the data, but each browser works a little differntly. If you have a state code already stored in a table or you are passing the value from one form to the next, it is easy to show which state was selected.
First, we need to have the state code stored and passed to a variable. I am going to call the variable $stateCode.
Second, let’s assume that the column names for your state code and state name are state_code and state_name.
Third, modify the code above and change the echo statement to the following.
echo "<option value=\"".$row['state_code']."\"";
if ($stateCode == $row['state_code']) echo " selected=\"selected\"";
echo ">".$row['state_name']."\n";
This will not work unless you have a value assigned to $stateCode. In order for a stored value to show as “selected”, the option statement must include the “selected” attribute.
Cafunfa Says:
GREAT! Exactly what i’m looking for!
Thanks a lot!
lunar Dust Says:
nice one… awesome, to-the-point tutorial yet with plenty of handy explanation.
Wish you could do another tutorial which sort of processes the value selected in the select box using php.
thanks
Doogie Says:
We do plan to do a series of PHP tutorials regarding forms processing. It is all a matter of finding the time to do it. We each have regular jobs that get in the way.
Keep checking back and we will get them done sooner of later.