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

Last modified on November 13th, 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.

When dropdown options depend on another input, we can say it as a dependent dropdown. Instead of user selection, some dropdowns depend on location, timezone and more. If you want to know how to find users’ locations, then the linked article will help you with an example.

Country-State-City Example Cascading jQuery Dependent Dropdown

View Demo

In a web application, it may have many requirements around the dropdown feature. For example, it requires to display cascading dropdowns with group options. In some applications, it needs dependent dropdowns with multi-select.

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. Country-State-City example file structure
  4. Dependent dropdown HTML
  5. jQuery AJAX script to fetch dependent data
  6. PHP code to fetch the country’s states and state’s cities
  7. Country State City SQL script
  8. 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

The dependent dropdown feature is relevant for a group of related dropdowns elements. This example has a HTML form with country, state, city dropdown. I will explain how to make them as dependent dropdowns.

These dropdown options are from the database. Instead of getting all the options, let us fetch data on a need basis for the dependent dropdown.

This type of dynamic loading will give relevant data based on user selection. Also, it will reduce the user effort to search for a suitable option.

In a PHP jQuery example, we have seen already how to load dynamic options for a select box.

I used jQuery AJAX with PHP to load data for the dependent dropdowns. The country dropdown will show all its options on page load. On its change event, I invoke AJAX to get dependent results for the state dropdown.

As same as the country-state dependent data load, the state-city will work. There are separate AJAX handlers and endpoints to get the dependent state and city.

Country-State-City example file structure

Country State City Example File Structure

Dependent Dropdown HTML

This code is to display the country state city dependent dropdowns to the user.

It includes PHP code to load the initial options for the country dropdown. The PHP code connects the database and retrieves the country result in an array format.

Each dropdown field’s change event invokes jQuery method to get the dependent data. The jQuery method requests PHP for the dependent data via AJAX.

This HTML code has the dependent dropdown as a target container. In this HTML target, the AJAX response loads the dependent data dynamically

In a previous article, we have seen how to show dependent dropdown options with multi-select. It increases the search criteria and retrieves more data for the select box options.

<?php
namespace Phppot;

use Phppot\CountryState;
require_once __DIR__ . '/Model/CountryStateCity.php';
$countryStateCity = new CountryStateCity();
$countryResult = $countryStateCity->getAllCountry();
?>
<html>
<head>
<TITLE>jQuery Dependent DropDown List - Countries and States</TITLE>
<head>
<link href="./assets/css/style.css" rel="stylesheet" type="text/css" />
<script src="./vendor/jquery/jquery-3.2.1.min.js" type="text/javascript"></script>
<script src="./assets/js/ajax-handler.js" type="text/javascript"></script>
</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 ($countryResult 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>Select City</option>
            </select>
        </div>
    </div>
</body>
</html>

jQuery AJAX Script to Fetch Dependent Data

AJAX is not mandatory to implement a dependent dropdown in an application. We can also submit the form with selected data to get the dependent data result.

But, it will give a good user experience with AJAX compared to the form submit. We have to ensure about the importance of using AJAX before coding it. Because unnecessary AJAX usage is an overload for an application.

In this example, I have used a simple AJAX code in jQuery. You can see the JavaScript methods getState(), getCity() in the below code. It has the AJAX script to get the dependent data from the server-side.

These methods call appropriate PHP files for the dependent dropdown results. These results are from the database retrieved using PHP MySQL.

This is the getState() method definition. It sends selected country id to the get-state-ep.php endpoint.

function getState(val) {
	$.ajax({
		type: "POST",
		url: "./ajax/get-state-ep.php",
		data:'country_id='+val,
		beforeSend: function() {
			$("#state-list").addClass("loader");
		},
		success: function(data){
			$("#state-list").html(data);
			$('#city-list').find('option[value]').remove();
			$("#state-list").removeClass("loader");
		}
	});
}

This JavaScript method is like the getState method we have seen above. It passed the state id to the get-city-ep.php to get the city option in a HTML format.

On changing the state dropdown values, this method executes the jQuery AJAX script.

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

The success callback handles the response returned from the server-side.

The jQuery code inside the success callback method handles the AJAX response. It updates the dependent dropdown options as it is from the PHP in a HTML format.

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

I have created two PHP programs get-state-ep.php and get-city-ep.php.

I call get-state-ep.php via AJAX by sending the selected country. This PHP code will fetch the state result based on the selected country from the database.

In get-city-ep.php I received the state id parameter posted via AJAX. The SELECT query in the following code uses the parameter to fetch cities based on it.

The PHP code to execute the database fetch is running on a conditional basis. In that condition, it checks if the country or state id passed via AJAX is not empty.

It returns group HTML <option></option> tags as a response to update the dependent dropdown. We can also return JSON response and handle it on the client-side. 

get-state-ep.php

<?php
namespace Phppot;

use Phppot\CountryState;
if (! empty($_POST["country_id"])) {
    
    $countryId = $_POST["country_id"];
    
    require_once __DIR__ . '/../Model/CountryStateCity.php';
    $countryStateCity = new CountryStateCity();
    $stateResult = $countryStateCity->getStateByCountrId($countryId);
    ?>
<option value="">Select State</option>
<?php
    foreach ($stateResult as $state) {
        ?>
<option value="<?php echo $state["id"]; ?>"><?php echo $state["name"]; ?></option>
<?php
    }
}
?>

get-city-ep.php

<?php
namespace Phppot;

use Phppot\CountryState;
if (! empty($_POST["state_id"])) {
    
    $stateId = $_POST["state_id"];
    
    require_once __DIR__ . '/../Model/CountryStateCity.php';
    $countryStateCity = new CountryStateCity();
    $cityResult = $countryStateCity->getCityByStateId($stateId);
    ?>
<option>Select City</option>
<?php
foreach ($cityResult as $city) {
        ?>
<option value="<?php echo $city["id"]; ?>"><?php echo $city["name"]; ?></option>
<?php
    }
}
?>

CountryStateCity.php

<?php
namespace Phppot;

use Phppot\DataSource;

class CountryStateCity
{
    private $ds;
    
    function __construct()
    {
        require_once __DIR__ . './../lib/DataSource.php';
        $this->ds = new DataSource();
    }
    
    /**
     * to get the country record set
     *
     * @return array result record
     */
    public function getAllCountry()
    {
        $query = "SELECT * FROM country";
        $result = $this->ds->select($query);
        return $result;
    }
    
    /**
     * to get the state record based on the country_id
     *
     * @param string $countryId
     * @return array result record
     */
    public function getStateByCountrId($countryId)
    {
        $query = "SELECT * FROM states WHERE country_id = ?";
        $paramType = 'd';
        $paramArray = array(
            $countryId
        );
        $result = $this->ds->select($query, $paramType, $paramArray);
        return $result;
    }
    
    /**
     * to get the city record based on the state_id
     *
     * @param string $stateId
     * @return array result record
     */
    public function getCityByStateId($stateId)
    {
        print $query = "SELECT * FROM city WHERE state_id = ?";
        $paramType = 'd';
        $paramArray = array(
            $stateId
        );
        $result = $this->ds->select($query, $paramType, $paramArray);
        return $result;
    }
}

Country State City SQL Script

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

It also shows the ALTER queries for adding the required indexes and constraints.

Import this script in your environment while setting this example in local. The sample data given in the SQL is dependent on each other. It will help to make the example work by getting expected dependent results.

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

CREATE TABLE `city` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `state_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

INSERT INTO `city` (`id`, `name`, `state_id`) 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
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- 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,
  `country_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

INSERT INTO `states` (`id`, `name`, `country_id`) 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);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `city`
--
ALTER TABLE `city`
  ADD PRIMARY KEY (`id`),
  ADD KEY `state_id` (`state_id`);

--
-- Indexes for table `country`
--
ALTER TABLE `country`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `states`
--
ALTER TABLE `states`
  ADD PRIMARY KEY (`id`),
  ADD KEY `country_id` (`country_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `city`
--
ALTER TABLE `city`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=76;

--
-- AUTO_INCREMENT for table `states`
--
ALTER TABLE `states`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=26;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `city`
--
ALTER TABLE `city`
  ADD CONSTRAINT `city_ibfk_1` FOREIGN KEY (`state_id`) REFERENCES `states` (`id`);

--
-- Constraints for table `states`
--
ALTER TABLE `states`
  ADD CONSTRAINT `states_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`id`);

Country State City relationship with ERD 

This ERD shows the entity relationship between the country, states, city database tables.

It will explain the interdependency between the entities taken for this example.

Country State City Database Relationship Diagram

Country State City Dependent Dropdown Output

The below screenshot shows the dependent dropdown example output.

We can see the inter-dependancy between the selected option shown in this screenshot.

On choosing the USA for the country dropdown, it shows the corresponding states. When we select a state of California from the options, then it populates the dependent cities.

So, each entity depends on its parent to display the related result from the database.

Country-State-City Dependent Dropdown Example Output

Conclusion

We have enhanced the dependent dropdown code by adding one more dropdown to the cascade. The cascading dropdowns behave based on their parent selection.

With or without AJAX which doesn’t matter while creating dependent dropdown. But, this code will give the best model to provide a good user experience with AJAX.

By enabling multi-select for these dropdowns helps to widen the dependent data search. I herewith interlinked the other dependent dropdowns code from previous articles.

I hope this example code and the related interlinks added in this article will help you.

View DemoDownload

Comments to “Country-State-City Example: Cascading jQuery AJAX Dependent Dropdown in PHP”

Leave a Reply

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

↑ Back to Top

Share this Article