HTML form – Drop down menu with data from MySQL datebase as options

Posted by Shek on May 8, 2008 under PHP | 3 Comments to Read | Total View: 27,773 views

To create a drop down menu is easy, just use the codes I mentioned in my past post: HTML form element – Drop down menu.

But what if you want to have the data in the MySQL database to be the options of the drop down menu and the options are over 100 or more?? It is very time consuming to create all. But I am going to show you an easy way to do so which by creating a drop down menu involving with PHP and query. The codes are:


First we have to connect to database.

<?php
mysql_connect("Host Name", "User Name", "User Password") or die("Connection Failed");
mysql_select_db("DataBase Name")or die("Connection Failed");
?>

Here then we have to create and run a query to select all from a field of a table where the options are located:

<?php
$query = "SELECT * FROM table";
$result = mysql_query($query);
?>

We have closed the PHP tag here because the drop down menu is HTML form element but not a PHP element. Here back to HTML codes and we create the drop down menu.

<select name="select1">

Here we are back to PHP again, and we will go through the MySQL table field which we selected with the query and we now will go through record by record to see if it meet the criteria of the query, the query is “select * from table” which means select all from table, therefore in this case, we will use every record of the field as option.

<?php
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
?>

Here, we close the PHP tag again and back to HTML, because we now have selected the option for the drop down menu: option consists of 2 parts, one is value and one is the text shows to the user, because the value and the text are both the record from the MySQL, therefore to put PHP variables in HTML, we have to echo it out. In the below example, replace the “field” with the field name of the field which you want the option from the MySQL database

<option value="<?php echo $line['field'];?>"> <?php echo $line['field'];?> </option>

end this loop:

<?php
}
?>

Close the drop down menu

</select>

All together we have:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?php
mysql_connect("Host Name", "User Name", "User Password") or die("Connection Failed");
mysql_select_db("DataBase Name")or die("Connection Failed");
$query = "SELECT * FROM table";
$result = mysql_query($query);
?>
<select name="select1">
<?php
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
?>
<option value="<?php echo $line['field'];?>"> <?php echo $line['field'];?> </option>
 
<?php
}
?>
</select>

Not what you want? Try a Search

  • Alex said,

    Thanks. I couldn't find any resource with this clear illustration. I appreciate you taking a second to write it out!

  • Chris said,

    Hi, firstly I just wanted to say many thanks for posting this. It was a really useful tutorial.

    I was also wondering whether you may be able to help me please with a problem I have around drop down menus. I would like to add another drop down menu to my form that only shows those values where a specific id field matches that from the selection made from the first drop down menu.

    I couldn’t find anything specific to this on your website, but I just wondered if it was at all possible that you may be able to help me with this please.

    Many thanks and regards

    Chris

  • Shek said,

    Hey Chris,
    Thank you for visiting my blog.
    In order to solve your problem, to use html and php alone might not be the best solution. Some JavaScript is needed:
    Here is a good example from http://www.tamilcodes.com for your reference:
    http://www.tamilcodes.com/demopages/jsdropdownlist.html
    To meet the criteria, you need to change the javascript function in there by using php+sql digging all of the relationship between first and the second drop down menu from the MySQL. here is how to include php in Javascript:
    http://www.phpsuperblog.com/php/php-variable-in-html/
    Hope this helps.
    Shek

Add A Comment

*