Populating a Select Box With PHP and MySQL – PHP Tutorial
By Jonathan | November 22, 2007 | Copyright 2007 - All Rights ReservedThis is a simple PHP script that takes data from a MySQL table and uses it to generate a drop-down select box for an HTML form. This can be used for a list of states on a credit card or user data form, or for a category selection or something where you need to pull the selections from a MySQL table. The script uses PHP to generate the HTML that is sent to the user’s browser.
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.
ebud Says:
Any advantage to using:
while($row = mysql_fetch_array($rs))
vs
while ($row = mysql_fetch_object($rs))
Doogie Says:
I have not tested it, but mysql_fetch_array is generally believed to be faster than mysql_fetch_object because the latter is theoretically more resource intensive. However, the official PHP site says that the performance is identical.
http://php.net/manual/en/function.mysql-fetch-object.php
frezya Says:
GREAT!
Thanks a lot! I solved php problem…
Letras Ybytes Says:
I am able to populate a select of States, but then I need that based on the selected states, another select is populated with cities within the selected State. How can I do that?
Tks!
Doogie Says:
There is no simple answer to that one. That is typically done using JavaScript or PHP.
We will put it on the list for future tutorials.
Ed Says:
Thank you for the easy to understand code. I have it working, but the next step I have problems with.
The dropdown shows the category_name only, and when I select a name and submit, on the next page I use:
$cat = isset($_REQUEST["categoryID"]) ? $_REQUEST["categoryID"] : “”;
echo $cat; //shows category_id
Is there a way to get the category_id and the category_name ?
Thank you for your help,
Ed
Conrad Says:
Hi Ed
You can only pass one value, but there are two ways of dealing with the category name.
1) Look it up using the category_id value after the value is passed.
2) You could try passing both the category_id and category_name through the select box option value and then use the PHP explode function to separate the two.
echo "<option value=\"" . $row['category_id'] . "_" . $row['category_name'] . "\">" . $row['category_name'] . "\n";
With this method you are passing both values separated by an underscore. Use the explode function to separate the two into an array. Each value can then be read from the array.