Country State City Dropdown in PHP MySQL Ajax

by

Country state city dropdown list in PHP + MySQL + Ajax ; Through this tutorial, i am going to show you how to create dynamic dependent country state city dropdown list in in PHP + MySQL + Ajax. And how to populate country state city list in HTML markup list.

Country State City Dropdown in PHP + MySQL + Ajax

Follow below following steps to create dynamic dependent country state city dropdown list using PHP and MySQL + Ajax:

  • Step 1 – Create PHP Project
  • Step 2 – Create Database Table And Connect App to DB
  • Step 3 – HTML Markup For Country State City Dropdown
  • Step 4 – Get State List By Country Id in PHP + MySQL
  • Step 5 – Get City List By State Id in PHP + MySQL

Step 1 – Create PHP Project

In step 1, Navigate to your local web server directory. And inside this directory, create one directory. And you can name this folder anything.

Here, I will “demo” the name of this folder. Then open this folder in any text editor (i will use sublime text editor).

Step 2 – Create Database Table And Connect App to DB

In step 2, you need to create database and table. So run the following sql query to create database and table:

CREATE DATABASE my_db;

CREATE TABLE `countries` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1=Active | 0=Inactive',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `states` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `country_id` int(11) NOT NULL,
 `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1=Active | 0=Inactive',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `cities` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `state_id` int(11) NOT NULL,
 `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1=Active | 0=Inactive',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `countries` VALUES (1, 'USA', 1);
INSERT INTO `countries` VALUES (2, 'Canada', 1);


INSERT INTO `states` VALUES (1, 1, 'New York', 1);
INSERT INTO `states` VALUES (2, 1, 'Los Angeles', 1);
INSERT INTO `states` VALUES (3, 2, 'British Columbia', 1);
INSERT INTO `states` VALUES (4, 2, 'Torentu', 1);


INSERT INTO `cities` VALUES (1, 2, 'Los Angales', 1);
INSERT INTO `cities` VALUES (2, 1, 'New York', 1);
INSERT INTO `cities` VALUES (3, 4, 'Toranto', 1);
INSERT INTO `cities` VALUES (4, 3, 'Vancovour', 1);

Then create a php file that named mydbCon. Which is used to connect phpmyadmin mysql database to project (demo).

So, now create mydbCon file and add the below given code into your file:

<?php

    $hName='localhost'; // host name

    $uName='root';   // database user name

    $password='';   // database password

    $dbName = "my_db"; // database name

    $conn= mysqli_connect($hName,$uName,$password,"$dbName");

      if(!$conn){
          die('Could not Connect MySql Server:' .mysql_error());
      }
?>
Recommended:-PHP + MySQL Registration Form with Validation

Step 3 – HTML Markup For Country State City Dropdown

In step 3, create a php file that named index.php. This file will display country state city dropdown list from MySQL database using jQuery Ajax. Now, you need to add the following html Markup code into index.php file:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Dynamic Country State City Dropdown in PHP + MySQL + Ajax - Laratutorials.COM</title>

        <link href="https://fonts.googleapis.com/css?family=Nunito:200,600" rel="stylesheet">

        <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" >

        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>

        <style>
            html, body {
                background-color: #fff;
                color: #636b6f;
                font-family: 'Nunito', sans-serif;
                font-weight: 200;
                height: 100vh;
                margin: 0;
            }

            .full-height {
                height: 100vh;
            }

            .flex-center {
                align-items: center;
                display: flex;
                justify-content: center;
            }

            .position-ref {
                position: relative;
            }

            .top-right {
                position: absolute;
                right: 10px;
                top: 18px;
            }

            .content {
                text-align: center;
            }

            .title {
                font-size: 84px;
            }

            .links > a {
                color: #636b6f;
                padding: 0 25px;
                font-size: 13px;
                font-weight: 600;
                letter-spacing: .1rem;
                text-decoration: none;
                text-transform: uppercase;
            }

            .m-b-md {
                margin-bottom: 30px;
            }
        </style>
    </head>
<body>
    <div class="container mt-5">
        <div class="row">
            <div class="card">
              <div class="card-header">
                    <h2 class="text-success">Country State City Dropdown List in PHP + MySQL + Ajax - Laratutorials.COM</h2>
                </div>
                <div class="card-body">
                 <form>
                    <div class="form-group">
                      <label for="country">Country</label>
                      <select class="form-control" id="country-dropdown">
                      <option value="">Select Country</option>
                        <?php
                        require_once "mydbCon.php";

                        $result = mysqli_query($conn,"SELECT * FROM countries");

                        while($row = mysqli_fetch_array($result)) {
                        ?>
                            <option value="<?php echo $row['id'];?>"><?php echo $row["name"];?></option>
                        <?php
                        }
                        ?>
                        
                      </select>
                    </div>
                    <div class="form-group">
                      <label for="state">State</label>
                      <select class="form-control" id="state-dropdown">
                        
                      </select>
                    </div>                        

                    <div class="form-group">
                      <label for="city">City</label>
                      <select class="form-control" id="city-dropdown">
                        
                      </select>
                    </div>
 
                </div>
              </div>
            </div>
        </div> 
    </div>
<script>
$(document).ready(function() {
    $('#country-dropdown').on('change', function() {
            var country_id = this.value;
            $.ajax({
                url: "states-by-country.php",
                type: "POST",
                data: {
                    country_id: country_id
                },
                cache: false,
                success: function(result){
                    $("#state-dropdown").html(result);
                    $('#city-dropdown').html('<option value="">Select State First</option>'); 
                }
            });
        
        
    });    

    $('#state-dropdown').on('change', function() {
            var state_id = this.value;
            $.ajax({
                url: "cities-by-state.php",
                type: "POST",
                data: {
                    state_id: state_id
                },
                cache: false,
                success: function(result){
                    $("#city-dropdown").html(result);
                }
            });
        
        
    });
});
</script>
</body>
</html>

The following ajax code will fetch states list and display in html markup dropdown by country id. And fetch cities ist form MySQL database and display in html markup dropdown by state id using PHP + AJAX.

<script>
$(document).ready(function() {
    $('#country-dropdown').on('change', function() {
            var country_id = this.value;
            $.ajax({
                url: "states-by-country.php",
                type: "POST",
                data: {
                    country_id: country_id
                },
                cache: false,
                success: function(result){
                    $("#state-dropdown").html(result);
                    $('#city-dropdown').html('<option value="">Select State First</option>'); 
                }
            });
        
        
    });    

    $('#state-dropdown').on('change', function() {
            var state_id = this.value;
            $.ajax({
                url: "cities-by-state.php",
                type: "POST",
                data: {
                    state_id: state_id
                },
                cache: false,
                success: function(result){
                    $("#city-dropdown").html(result);
                }
            });
        
        
    });
});
</script>
Recommended:-Form validation using jQuery in PHP

Step 4 – Get State List By Country Id in PHP + MySQL

In step 4, create one php file, which name states-by-country.php. And add the following code into it: 

<?php

	require_once "mydbCon.php";

	$country_id = $_POST["country_id"];

	$result = mysqli_query($conn,"SELECT * FROM states where country_id = $country_id");
?>

<option value="">Select State</option>
<?php
while($row = mysqli_fetch_array($result)) {
?>
	<option value="<?php echo $row["id"];?>"><?php echo $row["name"];?></option>
<?php
}
?>

The states-by-country.php file code will fetch all states list in dropdown form data into MySQL database using PHP + AJAX.

Recommended:-PHP Contact us Form with MySQL Database + Validation

Step 5 – Get City List By State Id in PHP + MySQL

In step 5, create one php file, which name cities-by-state.php. And add the following code into it: 

<?php

	require_once "mydbCon.php";

	$state_id = $_POST["state_id"];

	$result = mysqli_query($conn,"SELECT * FROM cities where state_id = $state_id");
?>

<option value="">Select City</option>
<?php
while($row = mysqli_fetch_array($result)) {
?>
	<option value="<?php echo $row["id"];?>"><?php echo $row["name"];?></option>
<?php
}
?>

The cities-by-state.php file code will fetch all cities list in dropdown from mysql database using PHP + Ajax.

Conclusion

Country state city dropdown list in PHP + MySQL + Ajax ; Through this tutorial,You have learned how to create dynamic dependent country state city dropdown list in in PHP + MySQL + Ajax.

Recommended PHP Tutorials

Recommended:-How to Fetch Data From Database in PHP and Display in Bootstrap Table
Recommended:-How to Update Data From Database in PHP
Recommended:-CRUD Operations in PHP MySQL with Bootstrap
Recommended:-Submit Form without Page Refresh Using Ajax in PHP MySQL
Recommended:-PHP MySQL Ajax Add Edit Delete Same Page
Recommended:-PHP MySQL Ajax Autocomplete TextBox From DB Example
Recommended:-PHP + MySQL Login Form with Session
Recommended:-PHP Ajax Form Submit with Validation Example
Recommended:-Ajax Get Data From MySQL Database in PHP
Recommended:-User Registration with Email Verification using PHP and MySQL
Recommended:-Find Nearest Location using Latitude Longitude PHP + MySQL
Recommended:-Google Places Autocomplete in PHP

Leave a Comment