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 ajax country state city dropdown list using PHP & MySQL will look like this:
Country State City Dropdown List using jQuery Ajax in PHP and MySQL
Here are steps:
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: "states-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: "cities-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.
Thank you very much, very fruitful codes
You are requested to edit it and give an example to update
how to pass name instead of id in this to database
Thank you. It worked really well.!!
Great website
god bless u sir/madam
thankyou