Country-State-City Example: Cascading jQuery AJAX Dependent Dropdown in PHP

Last modified on October 10th, 2019 by Vincy.

Are you searching for creating a dependent country-state-city dropdown in your application? I will give you a free download for an AJAX-based dependent dropdown code. It loads data from the database depends on the selected parent entity.

dependent-data-loading

View Demo

There are many requirements around the country-state-city dropdown lists. For example, somebody requires to cascade existing dropdowns based on the data dependency. Sometimes, people need data sources for the country, state, city dropdown options.

There are solutions available on the web to sort out those needs. This article provides a custom solution for the data dependency between country-state-city inputs. We have done this before for country-state dropdowns.

What is inside?

  1. Existing libraries for creating dependent dropdowns
  2. About Country-State-City example
  3. Dependent dropdown HTML
  4. jQuery AJAX script to fetch dependent data
  5. PHP code to fetch the country’s states and state’s cities
  6. Country State City SQL script
  7. Country State City dependent dropdown example output

Existing libraries for creating dependent dropdowns

There are many plugins available in the market for creating dependent dropdowns. Let us see a couple of those plugins in this section.

Dynamic jQuery Cascading Dropdown Lists Plugin is a jQuery plugin. It provides Basic/Dynamic mode of dependent dropdowns variations. It supports to load both static or dynamic data.

Dynamic Cascading Dropdown For Bootstrap – Cascader is a jQuery based Bootstrap plugin. It helps to render sub-dropdowns in a hierarchical order.

jQuery Bootstrap Cascade

About Country-State-City example

Dropdown field with a lengthy list of options may confuse users most of the time. Dynamically loading options and populating UI fields with values on need basis will help the user and give a best user experience. If the dropdown options depend on any other data like user’s location, a region then we can load the specific list of options instead of loading as a whole. For example, instead of displaying a complete states list in a dropdown, we can show the applicable list of states based on the user’s location.  The user’s location can be computed programmatically or by using user input. When a dropdown list’s option depends on another input field, then this is informally called dependent dropdown

In a previous tutorial, we have seen an example to load options to a state dropdown based on the country name selected by the user. In this tutorial, we are going to see three dependent dropdowns country -> state -> city. I used jquery AJAX to dynamically load data for the dependent dropdown. Initially, I have shown the complete list of countries in a dropdown field. When the user selecting the country name, then I will load the state dropdown dynamically. Similarly, the city dropdown is loaded based on the selected state value. The dependent data options fetched from the database via jQuery AJAX. 

Dependent Dropdown HTML

In this code, it shows the HTML for displaying the country, state and the city dropdown. On page load, I fetch the data from the country database table for the country dropdown. The state and the city dropdown will initially be empty and later I will load data dynamically dependent on the value selected in the previous dropdown.

<head>
<style>
body {
    width: 610px;
    font-family: calibri;
}

.frmDronpDown {
    border: 1px solid #7ddaff;
    background-color: #C8EEFD;
    margin: 2px 0px;
    padding: 40px;
    border-radius: 4px;
}

.demoInputBox {
    padding: 10px;
    border: #bdbdbd 1px solid;
    border-radius: 4px;
    background-color: #FFF;
    width: 50%;
}

.row {
    padding-bottom: 15px;
}
</style>
</head>
<body>
    <div class="frmDronpDown">
        <div class="row">
            <label>Country:</label><br /> <select name="country"
                id="country-list" class="demoInputBox"
                onChange="getState(this.value);">
                <option value disabled selected>Select Country</option>
                <?php
foreach($results as $country) {
?>
                <option value="<?php echo $country["id"]; ?>"><?php echo $country["country_name"]; ?></option>
                <?php
}
?>
            </select>
        </div>
        <div class="row">
            <label>State:</label><br /> <select name="state"
                id="state-list" class="demoInputBox"
                onChange="getCity(this.value);">
                <option value="">Select State</option>
            </select>
        </div>
        <div class="row">
            <label>City:</label><br /> <select name="city"
                id="city-list" class="demoInputBox">
                <option value="">Select City</option>
            </select>
        </div>
    </div>
</body>
</html>

jQuery AJAX Script to Fetch Dependent Data

This jQuery script shows the code to send the AJAX request to the PHP for fetching data based on the selected value. This data will be loaded dynamically to the dependent dropdown. On changing country dropdown option, the selected country will be passed to the PHP code via AJAX. In PHP code, the state result will be fetched from the database based on the selected country.

<script src="jquery-3.2.1.min.js" type="text/javascript"></script>
<script>
function getState(val) {
	$.ajax({
	type: "POST",
	url: "getState.php",
	data:'country_id='+val,
	success: function(data){
		$("#state-list").html(data);
		getCity();
	}
	});
}


function getCity(val) {
	$.ajax({
	type: "POST",
	url: "getCity.php",
	data:'state_id='+val,
	success: function(data){
		$("#city-list").html(data);
	}
	});
}

</script>

PHP Code to Fetch Country’s States and State’s Cities

I have created two PHP programs getState.php and getCity.php. I call getState.php via AJAX by sending the selected country. This PHP code will fetch state based on the selected country from the database. In getCity.php I received the AJAX posted state value and fetch cities based on it.

getState.php

<?php
require_once ("dbcontroller.php");
$db_handle = new DBController();
if (! empty($_POST["country_id"])) {
    $query = "SELECT * FROM states WHERE countryID = '" . $_POST["country_id"] . "'";
    $results = $db_handle->runQuery($query);
    ?>
<option value disabled selected>Select State</option>
<?php
    foreach ($results as $state) {
        ?>
<option value="<?php echo $state["id"]; ?>"><?php echo $state["name"]; ?></option>
<?php
    }
}
?>

getCity.php

<?php
require_once ("dbcontroller.php");
$db_handle = new DBController();
if (! empty($_POST["state_id"])) {
    $query = "SELECT * FROM city WHERE stateID = '" . $_POST["state_id"] . "' order by name asc";
    $results = $db_handle->runQuery($query);
    ?>
<option value disabled selected>Select City</option>
<?php
    foreach ($results as $city) {
        ?>
<option value="<?php echo $city["id"]; ?>"><?php echo $city["name"]; ?></option>
<?php
    }
}
?>

DBController

This is the code dbcontroller.php. This PHP class contains functions to establish MySQL database connection and to fetch dependent data from the database.

<?php
class DBController {
	private $host = "localhost";
	private $user = "root";
	private $password = "test";
	private $database = "phpsamples";
	private $conn;
	
	function __construct() {
		$this->conn = $this->connectDB();
	}
	
	function connectDB() {
		$conn = mysqli_connect($this->host,$this->user,$this->password,$this->database);
		mysqli_set_charset($conn,"utf8");
		return $conn;
	}
	
	function runQuery($query) {
		$result = mysqli_query($this->conn,$query);
		while($row=mysqli_fetch_assoc($result)) {
			$resultset[] = $row;
		}		
		if(!empty($resultset))
			return $resultset;
	}
	
	function numRows($query) {
		$result  = mysqli_query($this->conn,$query);
		$rowcount = mysqli_num_rows($result);
		return $rowcount;	
	}
}
?>

Country State City SQL Script

This SQL script contains the structure and the data dump for the country, state and city database tables.

--
-- Database: `phpsamples`
--

-- --------------------------------------------------------

--
-- Table structure for table `city`
--

CREATE TABLE `city` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `stateID` int(11) NOT NULL
)

--
-- Dumping data for table `city`
--

INSERT INTO `city` (`id`, `name`, `stateID`) VALUES
(1, 'Diadema', 1),
(2, 'Mauá', 1),
(3, 'Rio Grande da Serra', 1),
(4, 'Angra dos Reis', 2),
(5, 'Barra Mansa', 2),
(6, 'Belford Roxo', 2),
(7, 'Cabo Frio', 2),
(8, 'Aquiraz', 3),
(9, 'Canindé', 3),
(10, 'Caucaia', 3),
(11, 'Crato', 3),
(12, 'Blumenau', 4),
(13, 'Chapecó', 4),
(14, 'Criciúma', 4),
(15, 'Lages', 4),
(16, 'Aracruz', 5),
(17, 'Cariacica', 5),
(18, 'Colatina', 5),
(19, 'Linhares', 5),
(20, 'Guangzhou', 6),
(21, 'Shanghai', 6),
(22, 'Chongqing', 6),
(23, 'Beijing', 6),
(24, 'Baoding', 7),
(25, 'Qinhuangdao', 7),
(26, 'Tangshan', 8),
(27, 'Sanhe', 8),
(28, 'Paris', 11),
(29, 'Poissy', 11),
(30, 'Torbes', 12),
(31, 'Rodrez', 12),
(32, 'Auger-Saint-Vincent', 13),
(33, 'Aumatre', 13),
(34, 'Belfort', 14),
(35, 'Dole', 14),
(36, 'Colmar', 15),
(37, 'Obernai', 15),
(38, 'Gurugram', 16),
(39, 'Panipat', 16),
(40, 'Rewari', 16),
(41, 'Chandigarh', 16),
(42, 'Tirupati', 17),
(43, 'Vijayvada', 17),
(44, 'Elluru', 17),
(45, 'Nellore', 17),
(46, 'New Delhi', 18),
(47, 'Faridabad', 18),
(48, 'Chennai', 19),
(49, 'Madurai', 19),
(50, 'Coimbatore', 19),
(51, 'Salem', 19),
(52, 'Ballia', 20),
(53, 'Varanasi', 20),
(54, 'Lucknow', 20),
(55, 'Kanpur', 20),
(56, 'Los Angeles', 21),
(57, 'San Francisco', 21),
(58, 'San Diego', 21),
(59, 'Oakland', 21),
(60, 'lowa city', 22),
(61, 'Ames', 22),
(62, 'Waterloo', 22),
(63, 'Mason city', 22),
(64, 'New york city', 23),
(65, 'Buffalo', 23),
(66, 'Albany', 23),
(67, 'Yonkers', 23),
(68, 'Trenton', 24),
(69, 'Princeton', 24),
(70, 'Atlantic city', 24),
(71, 'Paterson', 24),
(72, 'Boston', 25),
(73, 'Cambridge', 25),
(74, 'Springfield', 25),
(75, 'Lowell', 25);

-- --------------------------------------------------------

--
-- Table structure for table `country`
--

CREATE TABLE `country` (
  `id` int(11) NOT NULL,
  `country_name` varchar(255) NOT NULL
)

--
-- Dumping data for table `country`
--

INSERT INTO `country` (`id`, `country_name`) VALUES
(1, 'Brazil'),
(2, 'China'),
(3, 'France'),
(4, 'India'),
(5, 'USA');

-- --------------------------------------------------------

--
-- Table structure for table `states`
--

CREATE TABLE `states` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `countryID` int(11) NOT NULL
)

--
-- Dumping data for table `states`
--

INSERT INTO `states` (`id`, `name`, `countryID`) VALUES
(1, 'Sao Paulo', 1),
(2, 'Rio de Janeiro', 1),
(3, 'Ceara', 1),
(4, 'Santa Catarina', 1),
(5, 'Espirito Santo', 1),
(6, 'Beijing', 2),
(7, 'Hebei', 2),
(8, 'Jiangsu', 2),
(9, 'Guangdong', 2),
(10, 'Guangdong', 2),
(11, 'Ile-de-France', 3),
(12, 'Midi-Pyrenees', 3),
(13, 'Picardie', 3),
(14, 'Franche-Comte', 3),
(15, 'Alsace', 3),
(16, 'Haryana', 4),
(17, 'Andhra Pradesh', 4),
(18, 'Delhi', 4),
(19, 'Tamil Nadu', 4),
(20, 'Uttar Pradesh', 4),
(21, 'California', 5),
(22, 'Iowa', 5),
(23, 'New York', 5),
(24, 'New Jersey', 5),
(25, 'Massachusetts', 5);

Country State City Dependent Dropdown Output

This screenshot shows the dependent state options based on the value selected for the country dropdown. And also shows the dependent cities based on the value selected in the state dropdown.

country-state-city-dependent-dropdown-output

View DemoDownload

Leave a Reply

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

↑ Back to Top

Share this Article