Country State City Dependent Dropdown In PHP MySQL (Simple Example)

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!

ⓘ I have included a zip file with all the example source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

TABLE OF CONTENTS

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

QUICK NOTES

  • Create a database and import 1-database.sql. This only contains US states and cities… Because nobody is giving away a complete world database for free. It will be grossly massive too.
  • Change the database settings in 2-ajax.php to your own.
  • Access 3a-index.html in your browser – Take note, use http:// and not file://.
If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

SCREENSHOT

 

EXAMPLE CODE DOWNLOAD

Click here to download the source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

DEPENDENT DROPDOWN

All right, let us now get started with the dependent dropdown – Countries, states, and cities.

 

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

1-database.sql
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

1-database.sql
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

1-database.sql
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

2-ajax.php
<?php
// (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN!
if (!isset($_POST["segment"])) { exit(); }
$dbhost = "localhost";
$dbname = "test";
$dbchar = "utf8";
$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 – The country, state, or city.
  • 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

3a-index.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

3b-script.js
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;
  1. On window load, csc.init() will run. Basically, put the HTML selectors into csc.select = [COUNTRY, STATE, CITY] and loads the list of countries.
  2. 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.

 

 

EXTRA BITS & LINKS

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

This example works well across all modern browsers. Internet Exploders will… explode.

 

REFERENCES & CREDITS

 

TUTORIAL VIDEO

 

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!

12 thoughts on “Country State City Dependent Dropdown In PHP MySQL (Simple Example)”

      1. 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"];
        }

  1. 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.

      1. 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.

  2. Thanks for sharing. This article has been useful to create Dependent Dropdown – URL LINK REMOVED – in my PHP project.

  3. 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?

Leave a Comment

Your email address will not be published. Required fields are marked *