in this article, we will learn how to make the country, state, and city dropdown using PHP MySQL and Ajax
Database structure
We need a country state city table in a database so We can get the values and pull them out in HTML
location.sql
-- phpMyAdmin SQL Dump
-- version 5.0.4
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Jan 29, 2021 at 06:56 PM
-- Server version: 10.4.17-MariaDB
-- PHP Version: 7.3.25
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `location`
--
-- --------------------------------------------------------
--
-- Table structure for table `cities`
--
CREATE TABLE `cities` (
`id` int(11) NOT NULL,
`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'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Dumping data for table `cities`
--
INSERT INTO `cities` (`id`, `state_id`, `name`, `status`) VALUES
(1, 2, 'Los Angales', 1),
(2, 1, 'New York', 1),
(3, 4, 'Toranto', 1),
(4, 3, 'Vancovour', 1);
-- --------------------------------------------------------
--
-- Table structure for table `countries`
--
CREATE TABLE `countries` (
`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'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Dumping data for table `countries`
--
INSERT INTO `countries` (`id`, `name`, `status`) VALUES
(1, 'USA', 1),
(2, 'Canada', 1);
-- --------------------------------------------------------
--
-- Table structure for table `states`
--
CREATE TABLE `states` (
`id` int(11) NOT NULL,
`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'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Dumping data for table `states`
--
INSERT INTO `states` (`id`, `country_id`, `name`, `status`) VALUES
(1, 1, 'New York', 1),
(2, 1, 'Los Angeles', 1),
(3, 2, 'British Columbia', 1),
(4, 2, 'Torentu', 1);
--
-- Indexes for dumped tables
--
--
-- Indexes for table `cities`
--
ALTER TABLE `cities`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `countries`
--
ALTER TABLE `countries`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `states`
--
ALTER TABLE `states`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `cities`
--
ALTER TABLE `cities`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
--
-- AUTO_INCREMENT for table `countries`
--
ALTER TABLE `countries`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
--
-- AUTO_INCREMENT for table `states`
--
ALTER TABLE `states`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
Project structure
php-tutorial/
┣ ajax.php
┣ db_config.php
┣ index.php
db_config.php
<?php
$servername='localhost';
$username='root';
$password='';
$dbname = "location";
$conn=mysqli_connect($servername,$username,$password,"$dbname");
if(!$conn){
die('Could not Connect MySql Server:' .mysql_error());
}
?>index.php
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<title>Country State City</title>
<!-- Latest compiled and minified CSS -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<style>
body{
background: #ccc;
}
form{
background: #fff;
padding: 30px;
margin-top: 30px;
}
form h3{
margin-top: 0;
}
</style>
</head>
<body>
<div class="container">
<div class="row">
<!--Course -->
<form action="" name="frm" method="post">
<h3>Country State City Dropdown</h3>
<section class="courses-section">
<div class="row">
<div class="col-md-4">
<label for="country">Country</label>
<select type="text" name="country" id="country" class="form-control">
<option>Select Country</option>
</select>
</div>
<div class="col-md-4">
<label for="state">State</label>
<select type="text" id="state" name="state" class="form-control"></select>
</div>
<div class="col-md-4">
<label for="city">City</label>
<select name="city" id="city" class="form-control"></select>
</div>
</div>
</div>
</section>
</form>
</div>
</div>
<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
$('#country').change(function() {
loadState($(this).find(':selected').val())
})
$('#state').change(function() {
loadCity($(this).find(':selected').val())
})
});
function loadCountry() {
$.ajax({
type: "POST",
url: "ajax.php",
data: "get=country"
}).done(function(result) {
$(result).each(function() {
$("#country").append($(result));
})
});
}
function loadState(countryId) {
$("#state").children().remove()
$.ajax({
type: "POST",
url: "ajax.php",
data: "get=state&countryId=" + countryId
}).done(function(result) {
$("#state").append($(result));
});
}
function loadCity(stateId) {
$("#city").children().remove()
$.ajax({
type: "POST",
url: "ajax.php",
data: "get=city&stateId=" + stateId
}).done(function(result) {
$("#city").append($(result));
});
}
// init the countries
loadCountry();
</script>
</body>
</html>
ajax.php
<?php
include('db_config.php');
$countryId = isset($_POST['countryId']) ? $_POST['countryId'] : 0;
$stateId = isset($_POST['stateId']) ? $_POST['stateId'] : 0;
$command = isset($_POST['get']) ? $_POST['get'] : "";
switch ($command) {
case "country":
$statement = "SELECT id,name FROM countries";
$dt = mysqli_query($conn, $statement);
while ($result = mysqli_fetch_array($dt)) {
echo $result1 = "<option value=" . $result['id'] . ">" . $result['name'] . "</option>";
}
break;
case "state":
$result1 = "<option>Select State</option>";
$statement = "SELECT id,name FROM states WHERE country_id=" . $countryId;
$dt = mysqli_query($conn, $statement);
while ($result = mysqli_fetch_array($dt)) {
$result1 .= "<option value=" . $result['id'] . ">" . $result['name'] . "</option>";
}
echo $result1;
break;
case "city":
$result1 = "<option>Select City</option>";
$statement = "SELECT id, name FROM cities WHERE state_id=" . $stateId;
$dt = mysqli_query($conn, $statement);
while ($result = mysqli_fetch_array($dt)) {
$result1 .= "<option value=" . $result['id'] . ">" . $result['name'] . "</option>";
}
echo $result1;
break;
}
exit();
?>Output:
