Welcome to a tutorial on how to create an AJAX-driven country, state, city dependent dropdown list in PHP and MySQL. So you are working on a top-secret ninja project that will cascade load when the user chooses a country, state, or city? Well, let us walk through a step-by-step example in this guide – Read on!
TABLE OF CONTENTS
DOWNLOAD & NOTES
Here is the download link to the example code, so you don’t have to copy-paste everything.
EXAMPLE CODE DOWNLOAD
Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.
SORRY FOR THE ADS...
But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.
Buy Me A Coffee Code Boxx eBooks
DEPENDENT DROPDOWN
All right, let us now get started with the dependent dropdown – Countries, states, and cities.
TUTORIAL VIDEO
STEP 1) THE DATABASE
For the purpose of this demo, we will be creating 3 simple tables. These should be self-explanatory – Countries, states, and cities.
1A) COUNTRIES
CREATE TABLE `countries` (
`country_code` varchar(2) NOT NULL,
`country_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `countries`
ADD PRIMARY KEY (`country_code`),
ADD KEY `country_name` (`country_name`);
Field | Description |
country_code | The ISO country code, primary key. |
country_name | The country name. |
1B) STATES
CREATE TABLE `states` (
`country_code` varchar(2) NOT NULL,
`state_code` varchar(32) NOT NULL,
`state_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `states`
ADD PRIMARY KEY (`country_code`,`state_code`),
ADD KEY `state_name` (`state_name`);
Field | Description |
country_code | The ISO country code. Partial primary key. |
state_code | The state code or postal code. Partial primary key. |
state_name | The state name. |
1C) CITIES
CREATE TABLE `cities` (
`country_code` varchar(2) NOT NULL,
`state_code` varchar(32) NOT NULL,
`city_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `cities`
ADD PRIMARY KEY (`country_code`,`state_code`,`city_name`);
Field | Description |
country_name | The ISO country code. Partial primary key. |
state_code | The state code or postal code. Partial primary key. |
city_name | City name. Partial primary key. |
STEP 2) PHP AJAX HANDLER
<?php
// (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN!
if (!isset($_POST["segment"])) { exit(); }
$dbhost = "localhost";
$dbname = "test";
$dbchar = "utf8mb4";
$dbuser = "root";
$dbpass = "";
$pdo = new PDO(
"mysql:host=$dbhost;dbname=$dbname;charset=$dbchar",
$dbuser, $dbpass, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_NUM
]);
// (B) GET ENTRIES FROM DATABASE
switch ($_POST["segment"]) {
// (B1) COUNTRIES
case "country":
$stmt = $pdo->prepare("SELECT `country_code`, `country_name` FROM `countries`");
$stmt->execute();
break;
// (B2) STATES
case "state":
$stmt = $pdo->prepare("SELECT `state_code`, `state_name` FROM `states` WHERE `country_code`=?");
$stmt->execute([$_POST["country"]]);
break;
// (B3) CITIES
case "city":
$stmt = $pdo->prepare("SELECT `city_name`, `city_name` FROM `cities` WHERE `country_code`=? AND `state_code`=?");
$stmt->execute([$_POST["country"], $_POST["state"]]);
break;
}
echo json_encode($stmt->fetchAll());
$stmt = null; $pdo = null;
This next piece of PHP script should not be a mystery either.
- Simply send
$_POST["segment"]
to this AJAX handler to specify which segment to load – Thecountry
,state
, orcity
. - Of course, remember to also send the selected country
$_POST["country"]
when fetching the list of states. - Send
$_POST["country"]
and$_POST["state"]
when fetching the list of cities.
STEP 3) LOCATION SELECT PAGE
3A) THE HTML
<form>
<label>Country</label>
<select id="s-country" onchange="csc.load(1)"></select>
<label>State</label>
<select id="s-state" onchange="csc.load(2)"></select>
<label>City</label>
<select id="s-city"></select>
</form>
This is just your “regular” country-state-city dropdown selectors. Take note of the attached onchange
though.
- When
<select id="s-country">
is changed, it will cascade load the states and cities. - When
<select id="s-state">
is changed, it will cascade load the cities.
3B) THE JAVASCRIPT
var csc = {
// (A) INIT
segments : ["country", "state", "city"], // segment names
select : [], // html selectors
init : () => {
for (let s of csc.segments) {
csc.select.push(document.getElementById("s-"+s));
}
csc.load(0);
},
// (B) AJAX LOAD COUNTRY-STATE-CITY
// 0 = COUNTRY, 1 = STATES, 2 = CITIES
load : segment => {
// (B1) FORM DATA
var data = new FormData();
data.append("segment", csc.segments[segment]);
if (segment==1 || segment==2) { data.append("country", csc.select[0].value); }
if (segment==2) { data.append("state", csc.select[1].value); }
// (B2) AJAX FETCH
fetch("2-ajax.php", { method: "POST", body: data })
.then(res => res.json())
.then(res => {
// (B2-1) DRAW OPTIONS
csc.select[segment].innerHTML = "";
for (let r of res) {
let o = document.createElement("option");
o.value = r[0]; o.innerHTML = r[1];
csc.select[segment].appendChild(o);
}
// (B2-2) CASCADE LOAD NEXT SEGMENT
if (segment<2) { segment++; csc.load(segment); }
})
.catch(err => console.error(err));
}
};
window.onload = csc.init;
- On window load,
csc.init()
will run. Basically, put the HTML selectors intocsc.select = [COUNTRY, STATE, CITY]
and loads the list of countries. csc.load()
will get data from the server and update the HTML selectors.- (B1) Specify which segment to fetch (country/state/city), and also the selected country/state.
- (B2) Does an AJAX call to
2-ajax.php
to get the list of countries/states/cities. - (B2-1) When
2-ajax.php
responds, we draw the HTML<option>
. - (B2-2) Cascade load the next segment if required.
EXTRAS
That’s all for this project, and here is a small section on some extras that may be useful to you.
WHERE TO GET MORE STATES AND CITIES?
Unfortunately, no one offered a free list of states and cities of the entire world. I did manage to dig up a few decent spreadsheets on GitHub. But if you need more, your best bet is to check with the local postal services of the countries that you want to work with.
COMPATIBILITY CHECKS
- Fetch – CanIUse
- Arrow Functions – CanIUse
- Form Data API – CanIUse
This example works well across all modern browsers. Internet Exploders will… explode.
REFERENCES & CREDITS
THE END
Thank you for reading, and we have come to the end of this guide. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!
I’m so grateful, but How to store selected values to use them.
Thank you in advance From Syria
Don’t skip the basics and don’t spam. Just do a “get HTML field value in javascript” search on the Internet.
https://code-boxx.com/faq/#help “Answers can be found all over the Internet”
Sorry but the internet connection is so bad here so I had to resend it several times and I don’t know what moderation in the website here is and I reckoned my question will appear instantly.
Yes I can get it using js, but I need to know how to do it in PHP as I tried and it didn’t respond since AJAX is in the code maybe..
if(isset($_POST['submit']){
echo $_POST["country"];
echo $_POST["state"];
echo $_POST["city"];
echo $_POST["district"];
}
<select name="FIELD">
Same old advice – Don’t skip the basics.
Sending form data – MDN
PHP Form – PHP Tutorial
ebook – Blazing Fast Web Dev Basics
Thank you, it is good if you want to use the form to add a country, state and city to the database but when trying to use the script on a form with fields pre selected from database, the script is replacing them instead of showing the pre selected country, state and city generated from database by id. I don’t know how to make the script to show the selected country, state and city from database and only on click of the select form option to be able to choose another country, state and city from the list.
echo "<option ". ($row['country_code']==ID-FROM-DATABASE?"selected ":"") ...
Yes, this is the answer but now I have another issue.
If I declare the country, state, city id variables manually inside 2-ajax.php is working fine but my ids are declared in my main index (php file, page) by GET id, dinamically.
How can I pass the value from the index file to the file 2-ajax.php?
Have a nice day.
https://developer.mozilla.org/en-US/docs/Web/API/URLSearchParams
Not a good design, but it works.
Thanks for sharing. This article has been useful to create
Dependent Dropdown– URL LINK REMOVED – in my PHP project.https://code-boxx.com/faq/#nolink
I download the source and created the database with tables and edit the 2a-config.php for the database but it cannot connect to the database?
Please check that you have the PHP PDO extension enabled (this example does not use the “traditional” MySQLi) – https://www.php.net/manual/en/pdo.setup.php