Bootstrap 5
https://getbootstrap.com/docs/5.0/getting-started/introduction/
https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css
CREATE TABLE `countries` (
`id` int(6) NOT NULL,
`name` varchar(250) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `countries` (`id`, `name`) VALUES
(1, 'Afghanistan'),
(171, 'Philippines'),
(227, 'United States of America');
CREATE TABLE `states` (
`id` int(11) NOT NULL,
`name` varchar(80) NOT NULL,
`country_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `states` (`id`, `name`, `country_id`) VALUES
(1, 'Manila', 171),
(2, 'Zambales', 171),
(3, 'Pampanga', 171),
(4, 'Bulacan', 171),
(9, 'Colorado', 227),
(10, 'California', 227),
(11, 'Arizona', 227),
(12, 'Alaska', 227),
(13, 'Alabama', 227);
CREATE TABLE `cities` (
`id` int(11) NOT NULL,
`name` varchar(80) NOT NULL,
`state_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `cities` (`id`, `name`, `state_id`) VALUES
(1, 'Manila', 1),
(2, 'Pasay', 1),
(3, 'Quezon City', 1),
(4, 'Olongapo City', 2),
(5, 'Angeles City', 3),
(6, 'Malolos', 4),
(7, 'Denver', 9),
(8, 'Los Angeles', 10),
(9, 'Phoenix', 11),
(10, 'Anchorage', 12),
(11, 'Huntsville', 13);
//index.php
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>PHP Mysqli Populate Dropdown using JavaScript AJAX</title>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css">
</head>
<body>
<div class="container"><br/><br/>
<div class="row">
<div class="col-lg-10">
<h2>PHP Mysqli Populate Dropdown using JavaScript AJAX</h2>
</div>
</div>
<div class="row">
<?php
include "config.php";
$sql = "SELECT * from countries order by name";
$stmt = $con->prepare($sql);
$stmt->execute();
$result = $stmt->get_result();
?>
<div class="row mb-3">
<label class="col-sm-2 col-form-label">Country:</label>
<div class="col-sm-10">
<select id="country" onchange="getStates(this.value);" class="form-select">
<option value="0" >– Select Country –</option>
<?php
while ($row = $result->fetch_assoc() ){
$id = $row['id'];
$name = $row['name'];
echo "<option value='".$id."' >".$name."</option>";
}
?>
</select>
</div>
</div>
<div class="row mb-3">
<label class="col-sm-2 col-form-label">State:</label>
<div class="col-sm-10">
<select id="state" onchange="getCities(this.value);" class="form-select">
<option value="0" >– Select State –</option>
</select>
</div>
</div>
<div class="row mb-3">
<label class="col-sm-2 col-form-label">City:</label>
<div class="col-sm-10">
<select id="city" class="form-select">
<option value="0" >– Select City –</option>
</select>
</div>
</div>
</div>
</div>
<script type="text/javascript">
function getStates(country_id){
// Empty the dropdown
var stateel = document.getElementById('state');
var cityel = document.getElementById('city');
stateel.innerHTML = "";
cityel.innerHTML = "";
var stateopt = document.createElement('option');
stateopt.value = 0;
stateopt.innerHTML = '-- Select State --';
stateel.appendChild(stateopt);
var cityopt = document.createElement('option');
cityopt.value = 0;
cityopt.innerHTML = '-- Select City --';
cityel.appendChild(cityopt);
// AJAX request
var xhttp = new XMLHttpRequest();
xhttp.open("POST", "ajaxfile.php", true);
xhttp.setRequestHeader("Content-Type", "application/json");
xhttp.onreadystatechange = function() {
if (this.readyState == 4 && this.status == 200) {
// Response
var response = JSON.parse(this.responseText);
var len = 0;
if(response != null){
len = response.length;
}
if(len > 0){
// Read data and create <option >
for(var i=0; i<len; i++){
var id = response[i].id;
var name = response[i].name;
// Add option to state dropdown
var opt = document.createElement('option');
opt.value = id;
opt.innerHTML = name;
stateel.appendChild(opt);
}
}
}
};
var data = {request:'getStates',country_id: country_id};
xhttp.send(JSON.stringify(data));
}
function getCities(state_id){
// Empty the dropdown
var cityel = document.getElementById('city');
cityel.innerHTML = "";
var cityopt = document.createElement('option');
cityopt.value = 0;
cityopt.innerHTML = '-- Select City --';
cityel.appendChild(cityopt);
// AJAX request
var xhttp = new XMLHttpRequest();
xhttp.open("POST", "ajaxfile.php", true);
xhttp.setRequestHeader("Content-Type", "application/json");
xhttp.onreadystatechange = function() {
if (this.readyState == 4 && this.status == 200) {
// Response
var response = JSON.parse(this.responseText);
var len = 0;
if(response != null){
len = response.length;
}
if(len > 0){
// Read data and create <option >
for(var i=0; i<len; i++){
var id = response[i].id;
var name = response[i].name;
// Add option to city dropdown
var opt = document.createElement('option');
opt.value = id;
opt.innerHTML = name;
cityel.appendChild(opt);
}
}
}
};
var data = {request:'getCities',state_id: state_id};
xhttp.send(JSON.stringify(data));
}
</script>
</body>
</html>
config.php
//config.php
<?php
$host = "localhost";
$user = "root";
$password = "";
$dbname = "projectdb";
// Create connection
$con = new mysqli($host, $user, $password, $dbname);
// Check connection
if ($con->connect_error) {
die("Connection failed: " . $con->connect_error);
}
ajaxfile.php
//ajaxfile.php
<?php
include 'config.php';
// Read POST data
$postData = json_decode(file_get_contents("php://input"));
$request = "";
if(isset($postData->request)){
$request = $postData->request;
}
// Get states
if($request == 'getStates'){
$country_id = 0;
$result = array();$data = array();
if(isset($postData->country_id)){
$country_id = $postData->country_id;
$sql = "SELECT * from states WHERE country_id=?";
$stmt = $con->prepare($sql);
$stmt->bind_param("i", $country_id);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()){
$id = $row['id'];
$name = $row['name'];
$data[] = array(
"id" => $id,
"name" => $name
);
}
}
echo json_encode($data);
die;
}
// Get cities
if($request == 'getCities'){
$state_id = 0;
$result = array();$data = array();
if(isset($postData->state_id)){
$state_id = $postData->state_id;
$sql = "SELECT * from cities WHERE state_id=?";
$stmt = $con->prepare($sql);
$stmt->bind_param("i", $state_id);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()){
$id = $row['id'];
$name = $row['name'];
$data[] = array(
"id" => $id,
"name" => $name
);
}
}
echo json_encode($data);
die;
}
