This 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.