Are you looking for dependent country state and city dropdown for your application. In this post we have exlained how to create dropdown dependent select list using AJAX and MYSQL.
You have found country-state-city dropdown UI interface in many ecommerce or course based web applications. Where user first choose country from dropdown list, then state list automaticlly produced and then cities data populate on the previous selected country and state data.
I have seen many tutorial also published on web, but they are showing dependent country state and city dropdown by Id value. I mean when your submit the form, you will get country, state and city IDs, instead of country code or state code.
In this post, we have explained the process to create Ajax dynamic dependent country state city dropdown using PHP and MYSQL. In which you will get Country ISO Code, State ISO Code and City name on form post submit.
What’s Inside:
- Create Database Tables
- Make Database connection
- Create Dropdown Elements in HTML Form
- Write jQuery Ajax Code to Get Element on Change Event
- PHP code to fetch Dependent dropdown data
- Get Selected data on Form Submit
Steps to Create Dependent Country State City Dropdown
1) Create Database Table:
a) Countries Database Table Structure: First create ‘countries’ database table using below MySql command. This table includes the iso3, iso2, phonecode, capital, currency and currency symbols.
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE `countries` ( `id` mediumint(8) UNSIGNED NOT NULL, `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `iso3` char(3) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `iso2` char(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `phonecode` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `capital` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `currency` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `currency_symbol` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `flag` int(2) NOT NULL DEFAULT '1' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; |
b) Create State Database Table: use below SQL command to create ‘state’ database table structure.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE `states` ( `id` mediumint(8) UNSIGNED NOT NULL, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `country_id` mediumint(8) UNSIGNED NOT NULL, `country_code` char(2) COLLATE utf8mb4_unicode_ci NOT NULL, `iso2` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `flag` tinyint(1) NOT NULL DEFAULT '1' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPACT; |
c) City database table structure:
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE `cities` ( `id` mediumint(8) UNSIGNED NOT NULL, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `state_id` mediumint(8) UNSIGNED NOT NULL, `state_code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `country_id` mediumint(8) UNSIGNED NOT NULL, `country_code` char(2) COLLATE utf8mb4_unicode_ci NOT NULL, `flag` tinyint(1) NOT NULL DEFAULT '1' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPACT; |
2) Make Database connection:
Now create a database.php file to make MySQLi connection.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<?php /* database configuration */ $servername = 'localhost'; $username = 'DB_User'; $password = 'Password'; $db = 'DB_Name'; // Create connection $link = new mysqli($servername, $username, $password, $db); // Check connection if ($link->connect_error) { die("Connection failed: " . $link->connect_error); } ?> |
3) Create Dropdown Elements in HTML Form:
Now we need to create dropdown select HTML elements for country, state and cities data.
Create index.php file and include ‘database.php’ in it.
First, fetch all countries database data from database which have flag = ‘1’, means all active ones. Show all countries data in first country dropdown list.
Index.php
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 |
<?php // Include the database config file include('database.php'); $query = "SELECT * FROM countries WHERE flag = 1 ORDER BY name ASC"; $result = $link->query($query); ?> <html> <head> <meta charset="UTF-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1"> <title>Dropdown Dependent List</title> <!-- Google Font --> <link href='https://fonts.googleapis.com/css?family=Source+Sans+Pro:400,300,600,200' rel='stylesheet' type='text/css'> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script> <style> .form select{ width:100% !important; } </style> </head> <body> <div class="container"> <div class="row"> <div class="col-lg-12" style="padding-right:0px;padding-left:0px;padding-top:50px"> <div class="page-hero d-flex align-items-center justify-content-center"> <!-- multistep form --> <form class="form-horizontal form" method="POST" action=""> <div align="center"> <h5 class="fs-subtitle">Dynamic Dropdown Dependent List</h5> </div> <div class="form-group"> <div class="col-sm-12"> <select id="country"> <option value="">Select Country</option> <?php if($result->num_rows > 0){ while($row = $result->fetch_assoc()){ echo '<option value="'.$row['iso2'].'">'.$row['name'].'</option>'; } }else{ echo '<option value="">Country not available</option>'; } ?> </select> </div> </div> <div class="form-group"> <div class="col-sm-12"> <select id="state" name="state"> <option value="">Select state</option> </select> </div> </div> <div class="form-group"> <div class="col-sm-12"> <select id="city" name="city"> <option value="">Select city</option> </select> </div> </div> <div class="form-group"> <div class="col-sm-6"> <input type="submit" name="submit" value="Submit"/> </div> </div> </form> </div> </div> </div> </div> </div> <script src="./js/custom.js" type="text/javascript"></script> </body> </html> |
4) Write jQuery Code to Get Dropdown On Change Event:
Now create a ‘custom.js’ file, in which write jQuery code to perform action on select on change event.
In this we have called Ajax Post method to show data in dropdown lists.
Note: Must include this custom.js file in Index.php script file.
custom.js
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
$(document).ready(function(){ $('#country').on('change', function(){ var iso2 = $(this).val(); if(iso2){ $.ajax({ type:'POST', url:'getAjaxData.php', data:'iso2_val='+iso2, success:function(html){ $('#state').html(html); $('#city').html('<option value="">Select state first</option>'); } }); }else{ $('#state').html('<option value="">Select country first</option>'); $('#city').html('<option value="">Select state first</option>'); } }); $('#state').on('change', function(){ var countryIdVal = $('#country').val(); var iso2ID = $(this).val(); if(iso2ID){ $.ajax({ type:'POST', url:'getAjaxData.php', data:'state_iso2='+iso2ID+'&sel_country_id='+countryIdVal, success:function(html){ $('#city').html(html); } }); }else{ $('#city').html('<option value="">Select state first</option>'); } }); }); |
5) Write Code to Fetch dependent dropdown data:
Copy below ‘getAjaxData.php’ script code to show country state and city dropdown data.
getAjaxData.php
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
<?php // Include the database config file include('database.php'); if(!empty($_POST['iso2_val'])){ // Fetch state data based on the specific country $getiso2 = $_POST['iso2_val']; echo $Singlequery = "SELECT * FROM countries WHERE iso2 = '".$getiso2."' AND flag = 1"; $GetIdResult = $link->query($Singlequery); $singlerow = $GetIdResult->fetch_assoc(); $GetCountryID = $singlerow['id']; // Fetch state data based on the specific country $query = "SELECT * FROM states WHERE country_id = ".$GetCountryID." AND flag = 1 ORDER BY name ASC"; $result = $link->query($query); // Generate HTML of state options list if($result->num_rows > 0){ echo '<option value="">Select State</option>'; while($row = $result->fetch_assoc()){ echo '<option value="'.$row['iso2'].'">'.$row['name'].'</option>'; } }else{ echo '<option value="">State not available</option>'; } }elseif(!empty($_POST['state_iso2'])){ $getStateiso2 = $_POST['state_iso2']; $sel_country_Val = $_POST['sel_country_id']; // Fetch state id data based on the specific state iso2 and country code value $Singlequery = "SELECT * FROM states WHERE iso2 = '".$getStateiso2."' AND country_code = '".$sel_country_Val."' AND flag = 1"; $GetIdResult = $link->query($Singlequery); $singlerow = $GetIdResult->fetch_assoc(); $GetStateID = $singlerow['id']; // Fetch city data based on the specific state id $query = "SELECT * FROM cities WHERE state_id = ".$GetStateID." AND flag = 1 ORDER BY name ASC"; $result = $link->query($query); // Generate HTML of city options list if($result->num_rows > 0){ echo '<option value="">Select city</option>'; while($row = $result->fetch_assoc()){ echo '<option value="'.$row['name'].'">'.$row['name'].'</option>'; } }else{ echo '<option value="">City not available</option>'; } } ?> |
6) Get Selected data on Form Submit:
On form submit, you can get each selected dropdown list data via $_POST. This will return country code, state code and city name value.
|
1 2 3 4 5 6 7 |
<?php if(isset($_POST['submit'])){ echo 'Selected Country Code: '.$_POST['country']; echo 'Selected State Code: '.$_POST['state']; echo 'Selected City Name: '.$_POST['city']; } ?> |
Conclusion:
By following above steps, you can easily implement dependent dropdown UI interface feature in your web application. To get all countries, state and cities MySQL data, get our complete code package from belwo given button link.
