How to Create Drop Down List in PHP with MySQL

I'll show you how to make a dynamic drop-down list in PHP using MySQL and Ajax today.

You saw a lot of online e-commerce applications, such as dropdown user interfaces for states and cities. The state list is automatically generated and the city data is filled in with the previously selected state and country information once users select a country from a drop-down list.

In this article, I used three dependent dropdowns Country, State, and City that dynamically load data from MySQL into drop-down options without requiring a page refresh. I also used PHP to implement jQuery and Ajax scripts.

Create a Database and Tables

Within PHPMyAdmin. I made a database called "drop_down_list_php_db" and three tables named "country," "state," and "city" inside of it.

The state table and the country table have links, and the city table and the state table have links as well.

Table: Country

In this table, I added two columns country_id and country_name.

 CREATE TABLE `country` (
  `country_id` int(11) NOT NULL,
  `country_name` varchar(14) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I have inserted two records in the country table.

 INSERT INTO `country` (`country_id`, `country_name`) VALUES
(1, 'Australia'),
(3, 'United States');

Table: State

I have three columns in this table: state_id, state_name, and country_id. The country_id column will function as a foreign key and will match the id in the country database to make the state select box dependent on the country's select box.

 CREATE TABLE `state` (
  `state_id` int(11) NOT NULL,
  `country_id` int(11) NOT NULL,
  `state_name` varchar(15) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I have inserted four data into the state table.

 INSERT INTO `state` (`state_id`, `country_id`, `state_name`) VALUES
(1, 1, 'South Australia'),
(2, 1, 'New South Wales'),
(3, 3, 'Alaska'),
(4, 3, 'Alabama');

Table: City

Three columns make up this table: city_id, city_name, and state_id. state_id will function as a foreign key for this table, just like it does for the state table.

 CREATE TABLE `city` (
  `city_id` int(11) NOT NULL,
  `state_id` int(11) NOT NULL,
  `city_name` varchar(15) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I've added eight data to the city table.

 INSERT INTO `city` (`city_id`, `state_id`, `city_name`) VALUES
(1, 1, 'Adelaide'),
(2, 1, 'Murray Bridge'),
(3, 2, 'Sydney'),
(4, 2, 'Newcastle'),
(5, 3, 'Angoon'),
(6, 3, 'Aniak'),
(7, 4, 'Abbeville'),
(8, 4, 'Alpine');

Here, I have provided the ERD diagram to see the foreign key relationship between the country, state, and city tables.

Check out this example to see how a dynamic drop-down list works.

Project File Structure

I have created four PHP files that incorporate jQuery and Ajax to create a dynamic drop-down selection option.

 xampp/
├── htdocs/
	├── Drop-Down-List-PHP-MySQL/
		├── bootstrap/
			├── css/
			│	└── bootstrap.min.js
			├── js/
			│	└── bootstrap.bundle.min
			
		├── js/jquery-3.6.0.min.js
		
		dbconfig.php
		index.php
		state.php
		city.php

dbconfig.php

This file contains PHP code that creates the connection to a MySQL database using the PDO extension.

 <?php
$db_host="localhost"; //localhost server 
$db_user="root";	//database username
$db_password="";	//database password   
$db_name="drop_down_list_php_db";	//database name

try
{
	$db=new PDO("mysql:host={$db_host};dbname={$db_name}",$db_user,$db_password);
	$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOEXCEPTION $e)
{
	$e->getMessage();
}

?>

index.php

In this file, I have put three select boxes and I retrieve country records from the country table in the country select box.

 <div class="container">
	<div class="row">        
		<div class="col-md-12 mt-5">
		<?php
			
		include 'dbconfig.php';
			
		$select=$db->prepare('SELECT * FROM country');
		$select->execute();
			
		?>
			
		<label class="form-label" style="font-size:20px; font-style:bold;">Select Country</label>
			
		<select id="country_change" class="form-select form-select-lg mb-3" aria-label=".form-select-lg example">
			
		<option>-- select country --</option>
			<?php
			while($row=$select->fetch(PDO::FETCH_ASSOC))
			{	
			?>
				<option value="<?php echo $row['country_id']; ?>"><?php echo $row['country_name']; ?></option>	
			<?php	
			}
			?>
			  
		</select>

		<label class="form-label" style="font-size:20px; font-style:bold;">Select State</label>
		<select id="state_change" class="form-select form-select-lg mb-3" aria-label=".form-select-lg example">
		  <option>-- select state --</option>
		</select>
	
		<label class="form-label" style="font-size:20px; font-style:bold;">Select City</label>
		<select id="city_result" class="form-select form-select-lg mb-3" aria-label=".form-select-lg example">
		  <option>-- select city --</option>
		</select>

        </div>
    </div>
</div>

Implement jQuery Ajax Code

I have retrieved state and city data from the database using Ajax without having to restart the page.

I have added the code below to the index.php file. When the dropdown option value is selected, it sends the country_id and state_id to the server-side scripts state.php and city.php via an Ajax call. We then receive the response and show the HTML data in the corresponding dropdown list.

 <script src="js/jquery-3.6.0.min.js"></script>
<script>
$(document).ready(function(){
	//country change event
	$('#country_change').change(function(){  
		var country = $(this).val();
		$.ajax({
			type: "POST",
			url: "state.php",
			data: "country_id="+country,
			success: function(response)
			{
				$("#state_change").html(response);		
			}
		});
	});
	
	//state change event
	$('#state_change').change(function(){
		var state = $(this).val();
		$.ajax({
			type: "POST",
			url: "city.php",
			data: "state_id="+state,
			success: function(response)
			{
				$("#city_result").html(response);		
			}
		});
	});
	
});


</script>

state.php

This file contains PHP code that, upon selection of the country box, loads data dynamically and pulls state data from the state table.

 <?php

include 'dbconfig.php';
			
if(isset($_POST['country_id']))
{
	$country_id = $_POST['country_id'];
	
	$select=$db->prepare('SELECT * FROM state WHERE country_id=:cid');
	$select->execute(array(':cid' => $country_id));	
	?>
	<option>-- select state --</option>
	<?php
	while($row=$select->fetch(PDO::FETCH_ASSOC))
	{
	?>
		<option value="<?php echo $row['state_id']; ?>"><?php echo $row['state_name']; ?></option>
	<?php
	}
}

?>

city.php

When a selection is made on the state box, this file's PHP code loads data dynamically and selects city records from the city table.

 <?php

include 'dbconfig.php';
			
if(isset($_POST['state_id']))
{
	$state_id = $_POST['state_id'];
	
	$select=$db->prepare('SELECT * FROM city WHERE state_id=:sid');
	$select->execute(array(':sid' => $state_id));	
	?>
	<option>-- select city --</option>
	<?php
	while($row=$select->fetch(PDO::FETCH_ASSOC))
	{
	?>
		<option value="<?php echo $row['city_id']; ?>"><?php echo $row['city_name']; ?></option>
	<?php
	}
}

?>

Learn More:

What does array splice do in PHP

What is POST variable in PHP

What is Custom PHP Development

How do you append to text in PHP

How to make OTP Verification in PHP

How to store textarea value in database in PHP

How to Fetch Data from Database in JSON format in PHP

How to get the class name in PHP

How to Run PHP Code in Browser

How to Check if a String is Null in PHP

How to Pass Dropdown Selected Value to Database in PHP

How to Process a Form using PHP

Download Codes