Country State City Dependent Dropdown With PHP MySQL – Simple Example

Welcome to a tutorial on how to create an AJAX-driven country, state, city dependent dropdown list with PHP and MySQL. So you are working on a top-secret ninja project that will cascade load once the user chooses the country, state, or city? Well, it is actually not that difficult.

To create a country, state, city dependent dropdown with PHP and MYSQL:

  1. We need to create a database to hold the full list of countries, states, and cities.
  2. A PHP AJAX handler to load the list of countries, states, cities from the database.
  3. Finally, an HTML form with country/state/city selectors that cascade load. I.E. When the country is changed, load the states. When the state is changed, load the cities.

Just how is this done exactly? Read on for the step-by-step example!

ⓘ 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 Dependent Dropdown Useful Bits & Links
Tutorial Video The End

 

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.

 

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.

 

COUNTRIES

1-database.sql
CREATE TABLE `countries` (
  `country_code` varchar(2) NOT NULL,
  `country_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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.

 

 

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=latin1;

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.

 

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=latin1;

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 = "";
try {
  $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_NAMED
    ]
  );
} catch (Exception $ex) { exit($ex->getMessage()); }
 
// (B) GET ENTRIES FROM DATABASE
switch ($_POST["segment"]) {
  // (B0) INVALID
  default: exit();
 
  // (B1) COUNTRIES
  case "country":
    $stmt = $pdo->prepare("SELECT * FROM `countries`");
    $stmt->execute();
    while ($row = $stmt->fetch()) {
      printf("<option value='%s'>%s</option>", $row["country_code"], $row["country_name"]);
    }
    break;
 
  // (B2) STATES
  case "state":
    $stmt = $pdo->prepare("SELECT * FROM `states` WHERE `country_code`=?");
    $stmt->execute([$_POST["country"]]);
    while ($row = $stmt->fetch()) {
      printf("<option value='%s'>%s</option>", $row["state_code"], $row["state_name"]);
    }
    break;
 
  // (B3) CITIES
  case "city":
    $stmt = $pdo->prepare("SELECT * FROM `cities` WHERE `country_code`=? AND `state_code`=?");
    $stmt->execute([$_POST["country"], $_POST["state"]]);
    while ($row = $stmt->fetch()) {
      printf("<option value='%s'>%s</option>", $row["city_name"], $row["city_name"]);
    }
    break;
}

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.
  • Remember to send $_POST["country"] and $_POST["state"] when fetching the list of cities.

 

STEP 3) LOCATION SELECT PAGE

THE HTML

3a-index.html
<form id="sel-form" onsubmit="return false;">
  <label for="sel-country">Country</label>
  <select id="sel-country"></select>
 
  <label for="sel-state">State</label>
  <select id="sel-state"></select>
 
  <label for="sel-city">City</label>
  <select id="sel-city"></select>
 
  <input type="submit" value="Go"/>
</form>

There is nothing special on the HTML page itself. Just a regular Joe HTML form with the country, state, and city selectors.

 

 

THE JAVASCRIPT

3b-script.js
// (A) AJAX LOAD COUNTRY-STATE-CITY
//  1 = LOAD COUNTRY
//  2 = LOAD STATES
//  3 = LOAD CITIES
function cscload (entry) {
  // (A1) INIT
  var data = new FormData(), target, next;

  // (A2) LOAD COUNTRIES
  if (entry==1) {
    data.append("segment", "country");
    target = document.getElementById("sel-country");
    next = 2; // LOAD STATES AFTER COUNTRIES ARE POPULATED
  }

  // (A3) LOAD STATES
  else if (entry==2) {
    data.append("segment", "state");
    data.append("country", document.getElementById("sel-country").value);
    target = document.getElementById("sel-state");
    next = 3; // LOAD CITIES AFTER STATES ARE POPULATED
  }
  
  // (A4) LOAD CITIES
  else {
    data.append("segment", "city");
    data.append("country", document.getElementById("sel-country").value);
    data.append("state", document.getElementById("sel-state").value);
    target = document.getElementById("sel-city");
  }

  // (A5) GO!
  fetch("2-ajax.php", { method: "POST", body: data })
  .then((res) => { return res.text(); })
  .then((options) => {
    target.innerHTML = options;
    if (next) { cscload(next); }
  });
}

// (B) ON PAGE LOAD
window.addEventListener("DOMContentLoaded", function () {
  // (B1) UPDATE STATES WHEN COUNTRY CHANGED
  document.getElementById("sel-country").onchange = () => { cscload(2); };
 
  // (B2) UPDATE CITIES WHEN STATE CHANGED
  document.getElementById("sel-state").onchange = () => { cscload(3); };
  
  // (B3) INIT LOAD COUNTRIES
  cscload(1);
});

Finally, this is the “confusing waterfall loading sequence”. But keep calm and look carefully – There is only one function cscload (ENTRY) that drives the entire country/state/city AJAX loading. Just trace through the code and you will do fine. In essence:

  • We are using the ENTRY parameter to determine which segment to load.
    • cscload(1) will load the list of countries.
    • cscload(2) will load the list of states.
    • Lastly, cscload(3) will load the list of cities.
  • Captain Obvious cascade loading sequence.
    • When the country is changed, we will cascade load and refresh the states/cities – cscload(1) will automatically call cscload(2), then cscload(3).
    • When the state is changed, we will need to refresh the list of cities – cscload(2) will automatically call cscload(3).

That’s about it.

 

 

USEFUL 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!

8 thoughts on “Country State City Dependent Dropdown With PHP MySQL – Simple Example”

  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.