Country State City Dropdown using Ajax in PHP MySQL

Country State City Dropdown using Ajax in PHP MySQL

Dynamic country state city database using Ajax in MySQL PHP. In this tutorial, we will show you how to dynamically populate country state city dropdown list in PHP using jQuery ajax from MySQL database.

This tutorial will guide you step by step on how to populate country state city in dropdown list onchange in PHP from MySQL using Ajax or populate the first, second, and third dropdown based on the first, and second selection of dropdown in PHP. As well as learn, how to fetch data from the database in the dropdown list in PHP using jQuery ajax.

In this dependent country state city dropdown using ajax in PHP MySQL, we will show states and cities in the dropdown list based on selected country and state value in PHP using ajax from the database.

This ajax country state city dropdown list using php & mysql (dynamic) will look like:

country state city dropdown using ajax in php
AJAX country state city dropdown in php Mysql

Country State City Dropdown List in PHP MySQL using Ajax

Now, follow below given simple and easy steps to retrieve and display country, state, and city dropdown list onchange in PHP using jQuery ajax from MySQL database:

  • Step 1: Create Country State City Table
  • Step 2: Insert Data Into Country State City Table
  • Step 3: Create DB Connection PHP File
  • Step 4: Create Html Form For Display Country, State and City Dropdown
  • Step 5: Get States by Selected Country from MySQL Database in Dropdown List using PHP script
  • Step 6: Get Cities by Selected State from MySQL Database in DropDown List using PHP script

Step 1: Create Country State City Table

First of all, open your database and run the following sql queries to create country, state and city table into the database:

Run this following sql query to create country table into your database:

CREATE TABLE `countries` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1=Active | 0=Inactive',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Next, Run this following sql query to create state table into your database:

CREATE TABLE `states` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `country_id` int(11) NOT NULL,
 `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1=Active | 0=Inactive',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Now, Run this following sql query to create city table into your database:

CREATE TABLE `cities` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `state_id` int(11) NOT NULL,
 `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1=Active | 0=Inactive',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Step 2: Insert Data Into Country State City Table

In this step, run the following sql queries to insert countries, states and cities into MySQL database in php:

INSERT INTO `countries` VALUES (1, 'USA', 1);
INSERT INTO `countries` VALUES (2, 'Canada', 1);


INSERT INTO `states` VALUES (1, 1, 'New York', 1);
INSERT INTO `states` VALUES (2, 1, 'Los Angeles', 1);
INSERT INTO `states` VALUES (3, 2, 'British Columbia', 1);
INSERT INTO `states` VALUES (4, 2, 'Torentu', 1);


INSERT INTO `cities` VALUES (1, 2, 'Los Angales', 1);
INSERT INTO `cities` VALUES (2, 1, 'New York', 1);
INSERT INTO `cities` VALUES (3, 4, 'Toranto', 1);
INSERT INTO `cities` VALUES (4, 3, 'Vancovour', 1);

Step 3: Create DB Connection PHP File

In this step, create a file name db.php and update the following code into db.php file:

<?php
	$servername='localhost';
	$username='root';
	$password='';
	$dbname = "my_db";
	$conn=mysqli_connect($servername,$username,$password,"$dbname");
	  if(!$conn){
		  die('Could not Connect MySql Server:' .mysql_error());
		}
?>

Note that, This code is used to create a MySQL database connection in PHP project.

Step 4: Create Html Form For Display Country, State and City Dropdown

In this step, create an index.php file and update the below PHP and HTML code into index.php file.

Note that, This HTML code shows the country, states and cities in dropdown list. And the PHP and ajax script on this file will dynamic populating states and cities in the dropdown list based on the selected country and states in the dropdown list.

Now, update the following php mysql ajax and html form into index.php file:

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Dynamic Dropdown List Country State City in PHP MySQL using Ajax - Tutsmake.COM</title>
<!-- Fonts -->
<link href="https://fonts.googleapis.com/css?family=Nunito:200,600" rel="stylesheet">
<!-- Styles -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" >
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<style>
html, body {
background-color: #fff;
color: #636b6f;
font-family: 'Nunito', sans-serif;
font-weight: 200;
height: 100vh;
margin: 0;
}
.full-height {
height: 100vh;
}
.flex-center {
align-items: center;
display: flex;
justify-content: center;
}
.position-ref {
position: relative;
}
.top-right {
position: absolute;
right: 10px;
top: 18px;
}
.content {
text-align: center;
}
.title {
font-size: 84px;
}
.links > a {
color: #636b6f;
padding: 0 25px;
font-size: 13px;
font-weight: 600;
letter-spacing: .1rem;
text-decoration: none;
text-transform: uppercase;
}
.m-b-md {
margin-bottom: 30px;
}
</style>
</head>
<body>
<div class="container mt-5">
<div class="row">
<div class="card">
<div class="card-header">
<h2 class="text-success">Country State City Dropdown List in PHP MySQL Ajax - Tutsmake.COM</h2>
</div>
<div class="card-body">
<form>
<div class="form-group">
<label for="country">Country</label>
<select class="form-control" id="country-dropdown">
<option value="">Select Country</option>
<?php
require_once "db.php";
$result = mysqli_query($conn,"SELECT * FROM countries");
while($row = mysqli_fetch_array($result)) {
?>
<option value="<?php echo $row['id'];?>"><?php echo $row["name"];?></option>
<?php
}
?>
</select>
</div>
<div class="form-group">
<label for="state">State</label>
<select class="form-control" id="state-dropdown">
</select>
</div>                        
<div class="form-group">
<label for="city">City</label>
<select class="form-control" id="city-dropdown">
</select>
</div>
</div>
</div>
</div>
</div> 
</div>
<script>
$(document).ready(function() {
$('#country-dropdown').on('change', function() {
var country_id = this.value;
$.ajax({
url: "state-by-country.php",
type: "POST",
data: {
country_id: country_id
},
cache: false,
success: function(result){
$("#state-dropdown").html(result);
$('#city-dropdown').html('<option value="">Select State First</option>'); 
}
});
});    
$('#state-dropdown').on('change', function() {
var state_id = this.value;
$.ajax({
url: "city-by-state.php",
type: "POST",
data: {
state_id: state_id
},
cache: false,
success: function(result){
$("#city-dropdown").html(result);
}
});
});
});
</script>
</body>
</html>

Step 5: Get States by Selected Country from MySQL Database in Dropdown List using PHP script

Now, create a new PHP file named states-by-country.php. This php script will fetch and show states (second dropdown) based on selected country (previous dropdown selection) in PHP.

To update the following PHP and Html code into the states-by-country.php file:

<?php
require_once "db.php";
$country_id = $_POST["country_id"];
$result = mysqli_query($conn,"SELECT * FROM states where country_id = $country_id");
?>
<option value="">Select State</option>
<?php
while($row = mysqli_fetch_array($result)) {
?>
<option value="<?php echo $row["id"];?>"><?php echo $row["name"];?></option>
<?php
}
?>

Step 6: Get Cities by Selected State from MySQL Database in DropDown List using PHP script

In this step, create an again new PHP file named cities-by-state.php. This PHP code will fetch and show a cities (third dropdown list) based on selected state and country (previous dropdown selection) in PHP.

To update the following php and html code into cities-by-state.php file:

<?php
require_once "db.php";
$state_id = $_POST["state_id"];
$result = mysqli_query($conn,"SELECT * FROM cities where state_id = $state_id");
?>
<option value="">Select City</option>
<?php
while($row = mysqli_fetch_array($result)) {
?>
<option value="<?php echo $row["id"];?>"><?php echo $row["name"];?></option>
<?php
}
?>

Conclusion

PHP get country state and city dropdown list using ajax, you have learned how to dynamic populating states, and cities in the dropdown list based on the selected country and states in PHP MySQL using Ajax.

AuthorAdmin

My name is Devendra Dode. I am a full-stack developer, entrepreneur, and owner of Tutsmake.com. I like writing tutorials and tips that can help other developers. I share tutorials of PHP, Javascript, JQuery, Laravel, Livewire, Codeigniter, Vue JS, Angular JS, React Js, WordPress, and Bootstrap from a starting stage. As well as demo example.

Leave a Reply

Your email address will not be published. Required fields are marked *