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']."</option>\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.
Ed Says:
Success! I’ve got it working with the explode, Thanks !
Carlos Says:
Excellent! was very helpful.
Dana Says:
Wow, this saved my bacon! Am presently working on a school project and this was…no kidding…exactly what I was looking for. I tinkered with the code a bit though, since I’m using mysqli statements. I suspect I had to, since I used mysqli_connect and mysqli_select_db. Here’s the syntax with mysqli ($dbConn is my database connection)
$query = “SELECT id, name FROM games ORDER BY name”;
$resultSet = mysqli_query($dbConn, $query);
while($row = mysqli_fetch_array($resultSet, MYSQLI_ASSOC)){
echo “”.$row['name'].”\n”;
}
Box Says:
Nice example, very clear explanation and easy to implement.
The situation that I’m trying to apply such a thing is a bit more complicated though.
I am trying to use multiple selectboxes on the same site, and the data in these selectboxes all depend on the other selectbox’ selection.
So for example:
Selectbox1: “Select a product”
Selectbox2: “Select a productversion”
As soon as you select a product, the data in selectbox2 has to be updated to the chosen product, coming from a different SQL table.
Any ideas would be greatly appreciated.
Doogie Says:
A multiple select box like you describe is done with JavaScript. You can use PHP and MySQL to generate the JavaScript, but what you want to do requires client-side code (code that runs in a user’s browser). It is not as simple to implement as the example shown above.
We plan to do a tutorial regarding how to do this, but we haven’t gotten to it yet.
Box Says:
Yeah figured that would be the case.
It has no high priority or something, so I’ll just check back in every now and then.
Thanks for your quick reply Doogie.
Jamie Says:
in sql code please!!
Simion- Romania Says:
This is great !!!! Thank you a lot !!!! Is very useful !!
Thank you !!
Senthil Says:
Thanks lord i am struggling for this select box for every time thanks for helping me !
trey Says:
This code worked great for generating the drop down box. Thanks for tat. Although I do have another issue.
I use the drop down box to select a db entry on my ‘test’ table. I then want to have a submit button take me to another page or even load the data into the same page, containing the entire entries data.
For example.
The ‘test’ table contains 14 fields. The drop down box queries a particular field and displays the result. Upon clicking submit, I would like to be able to edit all of the 14 fields associated with the selection.
So far I have this. It isn’t exactly how you have it but it’s in essence the same. Please help!
<option value ="” >
Doogie Says:
Hi trey
The easiest way is to use JavaScript to redirect the user to a different page when they make a selection.
Try this:
Drop Down Box with automatic redirect
You could also use PHP to process the URL after the selection is submitted and then redirect the user to the new page.
Martin Says:
this script works great for me but i have a different problem i cant seem to get my head round, i have some form checking variables in place and reload the form through a processing script after posting whilst retaining any selected values by using
value=”"
these work fine for text areas but need to retain the value for the drop down box as well.
here is my edited version, you’ll see im also not using the row id just the name, i have the distinct data selection and connection code at the top of the page then use this in the form,
<?php
while($row = mysqli_fetch_assoc($result))
{
echo "”.$row['venueName'].”\n “;
}
?>
i am new to php so my knowledge is limited, any help would be appreiciated thanks.
Craig Mazur Says:
@Martin
I don’t know if any of your code got stripped out. WordPress has a tendency to do that. I don’t see the HTML option code in the sample code that you submitted.
If you want to retrieve the selected value from the select box used in ther example, use the following PHP code on the page that the form gets submitted to. This will retrieve the selected value from categoryID and plug it into a variable named $categoryID. This assumes that you are using the POST method.
if (isset($_POST['categoryID'])) $categoryID = $_POST['categoryID'];
If you are trying to retain the selected value from the drop-down and display that if the form fails the error checking, then you need to change the code for line with the the option tag. Split the line with the option code into three lines.
echo “<option value=\”".$row['category_id'].”\”";
if ($categoryID == $row['category_id']) echo ” selected=\”selected\”";
echo “>”.$row['category_name'].”</option>\n “;
This compares the value assigned to $categoryID when you did the error checking and compares it to the current option value. If they match, it inserts the selected=”selected” attribute, which displays the last selection made with the drop-down box.
Charles Says:
Hi All!
Would like to ask for your help. Im currently using a multiple select box and i’m already done with the inserting part but i’m having problems on populating the data the was inserted
MySQL Data:
A, B, C, D
how can i use the data to be inserted to the edit multiple select box?
Thanks Guys!
frank Says:
Just to say that after days of struggling through the garbage that people put on the internet for the simplest things this explanation is super great fantastic clean simple works and I finally get it. Thanks a zillion!
Ramiro Says:
Thank you very much. The code is working.
Ramiro
derpsn Says:
great tutorial, was really helpfull!
but what if you have multiple entries with the same category? it will show you then the category to select for each individual one. any possibilities to group them togehter?
in my case im doing a sort by date. so i have a timestamp in the database which is like : 2011-08-23 08:45:01 which using substr will show me then the date only. but if there where 10 entries with that date i got like 10 times the same option.
Steve Says:
Thanks for this code. It works great. I want to add a hidden field based on the option selected but can’t seem to get the code correct. This is what I have:-
<?php
$sql = "SELECT ID_day, day FROM day ".
"ORDER BY ID_day";
$rs = mysql_query($sql);
while($row = mysql_fetch_array($rs))
{
echo '’.$row['day'].”\n “;
}
?>
I want the page to also pass the ID_date field to the one selected.
Thanks
Steve
Abhay Says:
Hi this is a great tutorial thanks, is there a way I populate the select box from database as shown and also incorporate “Any” that passes out empty value, so that the output gives all results when selected ANY or as per the selected one.
echo”Any”;
wasnt able to send an empty / null value..
sorry if i am not been able to put it perfectly, as am not an PHP expert, just trying to correct a given code..
Thanks
Abhay
Doogie Says:
Hi Abhay
If I understand you correctly, you want to substitute the word Any whenever a field is empty. You will still need an ID for a unique identifier, but if the text field is empty, you can test it and substitute the word Any. In the case of the code sample above, you could do something like this for the code within the WHILE loop.
if(empty($row['category_name'])) $catName = "Any"; else $catName = $row['category_name'];
echo "<option value=\"".$row['category_id']."\">".$catName."</option>\n";
You may end up with multiple instances where the choice is ‘Any’.
Beosma Says:
Great code guys! I already have the code written to populate my drop down box from a MySQL query. I want the value I click on to be used as a search parameter in a MySQL database. My problem is that any value in the drop down box that is more than one word, doesnt work. Why does this happen and how can I get around it? I am assuming that the selection is treated as one variable, if it isn’t, do I use an array to concatenate all the parts? I am just stuck here and could really use some help.
I am using php to populate the list and calling another php file to process the search…which of course is coming up blank due to no matches!
Looking forward to some input here. Thanks!
Doogie Says:
Hi Beosma
It doesn’t matter if there is one word or multiple words. All of the words in the selected element get passed as a variable value. In the code above, that variable would be read like this:
if (isset($_POST['categoryID'])) $categoryID = $_POST['categoryID']; else $categoryID = 0;
You could substitute the category name for the category ID in the code and it should assign it to $categoryID on the page that processes the form data.