How to Create Drop Down List in PHP with MySQL - onlyxcodes

Friday 31 May 2024

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.


how to create drop down list in php with mysql

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.


erd diagram to see the foreign key relationship between the city, state, and country tables

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


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
	}
}

?>

Download Codes

No comments:

Post a Comment

Post Bottom Ad