Populating Drop down list options collecting data or records from mysql table

We can get the options or the list items of a dropdown list box from a MySQL table. Here the records we will get from the table will be used to populate the dropdown list box. So the options are dynamically added to the list box, this way we get flexibility in selecting the options. Here we will first populate the list with options directly taking from the table. Then we will restrict the options by adding a WHERE Clause to the SQL statement we use for retrieving the data from the table.

Listbox with data from student table

Watch this Video Tutorial about this script

Populating drop down list box as options by using Data from MySQL table.

For example we are populating the list box with student names at the first stage. Then we will add a restriction and collect only the records of the boys from the table. This example you can extend to any other condition like displaying state list with population more than some value.

In the first stage we will learn how to get the data from MySQL table and then populate the list box. In second stage we will add restriction by using a period button to the list box. For our demo here we will use the student table and you can download / copy the SQL dump file to create your table with student data. The small PHP code we will discuss you can copy and use directly from here.

We are not discussing the connection to mysql part here and we assume connection is open for us. You can see different mysal connection strings used in different scripts here. We will start with the simple SQL select query used to collect the names and ids of the students from the table.

include "config.php"; // Database connection using PDO

//$sql="SELECT name,id FROM student"; 

$sql="SELECT name,id FROM student order by name"; 

/* You can add order by clause to the sql statement if the names are to be displayed in alphabetical order */

echo "<select name=student value=''>Student Name</option>"; // list box select command

foreach ($dbo->query($sql) as $row){//Array or records stored in $row

echo "<option value=$row[id]>$row[name]</option>"; 

/* Option values are added by looping through the array */ 

}

 echo "</select>";// Closing of list box

The list box is here

Selecting only male or female students

By using where condition we can restrict the list to only show male students. The SQL part is here.

$sql="SELECT name,id FROM student WHERE sex='male' order by name ";

Or we can display only female

$sql="SELECT name,id FROM student WHERE sex='female' order by name";

Pre selection of option in dropdown list box

While populating the listbox by taking data from a table , we can keep one option selected. for example the student name 'Big John' should be remain selected by default. For this we will use one if condition check inside the loop and if it matches then we will add 'selected' to that option.

Here is the foreach loop part only to show the change in code required.

foreach ($dbo->query($sql) as $row){//Array or records stored in $row

if($row[id]==10){

echo "<option value=$row[id] selected>$row[name]</option>"; 

}else{

echo "<option value=$row[id]>$row[name]</option>"; 

}

}

 echo "</select>";// Closing of list box

In this code we are populating the listbox one time while the page is loading. So this is not conditional listing. This loading can be changed so listbox will populate based on some user action.

Using MySQLi

Read more on MySQLi & database connection here

require "config.php";// Database connection
//////////////////////////////
 if($r_set = $connection->query("SELECT * from student")){

echo "<select id=name name=name class='form-control' style='width:100px;'>";
while ($row = $r_set->fetch_assoc()) {
echo "<option value=$row[id]>$row[name]</option>";
}
echo "</select>";
}else{
echo $connection->error;
}

Interlinked Multiple dropdowns in form

We often found more than one dropdown linked to each other. Like once a country is selected from one dropdown then matching states of that country is shown in second dropdown list.

If a car manufacturer is selected from one dropdown then 2nd dropdown will list all the models of that particular make.
Populating 2nd dropdown list based on selection of 1st one

Editing the data of dropdown list

We have used student table in this example. This script can be integrated with record edit script to change the table data.

Using jQuery and JSON

JSON is the data exchange string format and jQuery has several built in JavaScript libraries to reduce the client coding. By using these two we can populate a drop down list box.

Populating list box with data from student table by using jQuery

HTML 5 and Datalist

Datalist is added in HTML 5 . Like dropdown list users can select options from available choice and in addition to that they can add their own text.
Adding Options to datalist by using data from MySQL table

Troubleshooting

How to create student table?

Use the SQL dump of student table given here, create database ( if not there ) and then create the table using your phpMyAdmin.

I am getting database error

Open config.php file or read more here
Enter correct login details , userid and password.

My dropdown listbox is not populating.

Check your SQL, try the same query by using it in your PhpMyAdmin.
Check your connection string , you must connect with correct userid and password with proper database where our student table is present.
Try to display the student names first without keeping them inside listbox.

Run this simple code to display student name and id.

<?Php
require "config.php";// database connection
$sql="SELECT name,id  FROM student"; 

foreach ($dbo->query($sql) as $row) {
echo "$row[id] , $row[name]<br>";
}
?>

Questions

How do you fetch data from a MySQL table in PHP?
What is the process of populating a dropdown list with data from a MySQL table in PHP?
How do you establish a database connection in PHP to retrieve data from a MySQL table?
What are the different methods to retrieve data from a MySQL table in PHP, such as mysqli and PDO?
How do you retrieve specific columns from a MySQL table to populate a dropdown list?
How do you handle duplicate values in the MySQL table while populating the dropdown list?
How do you handle empty or null values from the MySQL table when populating the dropdown list?
Can you filter or sort the data from the MySQL table before populating the dropdown list? If yes, how?
How do you set a default selected option in the dropdown list based on a value from the MySQL table?
What are some best practices for securely handling database queries and preventing SQL injection when populating the dropdown list?

Editing and updating records used in dropdown list Managing list box elements by a radio button

Subscribe to our YouTube Channel here

yogesh	17-06-2014
I want to retrieve data from database to multiple select dropdown list using PHP.so that the user can select multiple items in that dropdown.Can any one help me. Any help is appreciated. Thanks.

Dee	15-07-2014
Hi, Thank you for the post. I have tried the code but its not working, The select list is blank. I am trying to come up with a code to select postal codes from the database. Below is the code; <?php include "connection.php"; // Database connection using PDO //$sql="SELECT name,id FROM student"; $sql="SELECT 'office_name','office_code' FROM postalcodes"; /* You can add order by clause to the sql statement if the names are to be displayed in alphabetical order / echo "<select name=office_name value=''>office_name</option>"; // list box select command foreach ($dbo->query($sql) as $row){//Array or records stored in $row echo "<option value=$row[office_code]>$row[office_name]</option>"; / Option values are added by looping through the array */ } echo "</select>";// Closing of list box ?>

Paul Reay	18-07-2014
I'm finding that unless the option is clicked on then its value isn't being stored, so what if i actually want to use the selected value and just tab to next field on my form, at the moment it wont store the value.

smo	25-09-2014
For this you need Ajax. By this when you use tab and move away to next field the value will automatically stored without submitting the form. You can also give a message asking user to select first before moving out or submit the form.

anusha	18-12-2014
plz send me the 4 dropdown list filter search in php using mysql

ross	18-01-2015
I get a drop box that is empty with no label What am I doing wrong??? $sql="SELECT supplier FROM supplier_name order by supplier"; /* You can add order by clause to the sql statement if the names are to be displayed in alphabetical order / echo "<select name=supplier value=Supplier>"; // list box select command foreach ($dbo->query($sql) as $row){//Array or records stored in $row echo "<option value=$row[id]>$row[name]</option>"; / Option values are added by looping through the array */ } echo "</select>";// Closing of list box

smo	18-01-2015
In your query you are asking only one column supplier, then while displaying you are asking $row[id] and $row[name]. You must select all the columns you will be using inside dropdown list box. Or you can call all the columns by using wildcard * . $sql="SELECT * FROM supplier_name order by supplier";

sheriff	10-02-2015
i need help!!! i have two items in drop down of my html page.so i need that if i selected first item in my dropdown,it should fetch some xyz data from the database and if i select second item from drop down,it should show other details from the same data base. what i have to do for that....

smo	10-02-2015
You can read double dropdown listbox to get the matching records from database. The

sara	21-05-2015
i want to fetch data from data base by choosing item from two dropdown list...............

mitch	25-11-2014
thanks so so much

Adam	03-12-2015
Question How would create a drop down menu with the id numbers instead of names Thanks

smo1234	04-12-2015
Check the line saying echo "<option value=$row[id]>$row[name]</option>"; Change this to echo "<option value=$row[name]>$row[id]</option>";

kishan	12-12-2015
Sir i can not retrive an image from mysql in dropdownlist

dhananjay	06-06-2016
search record by dropdown list from data base is should show in table format my table name is gn_user_record. columns in table are id, username, password, email, pan_number. it should show by pan number. code in php mysql

hackerz12on	24-11-2017
After the drop down is sucessfully created, if the user clicks a value from drop down for example. IF he clicks John Deo then Four needs to be displayed in <div> . How can i make this happen ??

smo1234	25-11-2017
You can use onselect event of the dropdown list to display the div with matching content. You can use JavaScript or JQuery. Here you can see dropdown list with JQuery.

xyz	26-01-2019
I want a code that will select only the selected value from drop down list and then match the value with the database available in php

Helly	29-05-2019
if i have a drop down of country,state,city and area after clicking on submit button it will show the societyname or apartment name , utility, charge and all data on table how can we do that

Creating a drop down list box using data from MySQL table