03-Apr-2023
.
Admin
Hi Guys,
Today, I will learn you how to create php mysql country state city dropdown using ajax.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.
We 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.
I will 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.
Step 1: Create Country State City Table
In this step,open your database and run the following sql queries to create country, state and city table into the 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;
Here, 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.
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.
Here, 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
Here, 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
Now 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
}
?>
It Will help you..
#Jquery
#PHP
