Country State City Dropdown List using jQuery, Ajax & PHP

On 12/19/2016

Hi! Today we'll see how to create country state city drop down list using jquery, ajax, php & mysql. Sometime back I shared a tutorial on building dynamic dependent select box using ajax and codeigniter. And I got enquiries from readers about implementing similar thing with php. So I thought it would be better to write separate tutorial about creating dynamic dependent select box in php without the complex MVC structure.

Though the underlying concept would be same, implementing dynamic dropdown list in core php is way different from than in MVC like Codeigniter. I'm going to take the same country state city dropdown list example and explain the process here.

Dynamic Country State City Dropdown List using jQuery, Ajax and PHP:

The process goes like this. We have three drop down lists on a php form each one for 'country', 'state' & 'city' respectively. On page load 'country' dropdown alone will be populated with country names from mysql db. On choosing a country, the 'state' dropdown will be filled with state names based on country. Similarly selecting a state will populate relative city names in the 'city' dropdown box.

The entire process will be handled via ajax call without refreshing the page.

Don't Miss: AJAX Modal Login Form using PHP OOP, MySQL & jQuery

Create MySQL Database & tables:

CREATE DATABASE IF NOT EXISTS `db_demo`;

USE `db_demo`;

CREATE TABLE IF NOT EXISTS `country` (
  `country_id` int(9) NOT NULL AUTO_INCREMENT,
  `country_name` varchar(30) NOT NULL,
  PRIMARY KEY (`country_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6;

INSERT INTO `country` (`country_id`, `country_name`) VALUES
(1, 'Canada'), (2, 'China'), (3, 'India'), (4, 'United Kingdom'), (5, 'United States');

CREATE TABLE IF NOT EXISTS `state` (
  `state_id` int(9) NOT NULL AUTO_INCREMENT,
  `country_id` int(9) NOT NULL,
  `state_name` varchar(30) NOT NULL,
  PRIMARY KEY (`state_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10;

INSERT INTO `state` (`state_id`, `country_id`, `state_name`) VALUES
(1, 3, 'Bihar'), (2, 3, 'Delhi'), (3, 3, 'Maharashtra'), (4, 3, 'Tamil Nadu'), (5, 3, 'West Bengal'), (6, 1, 'Newfoundland'), (7, 2, 'Shanghai'), (8, 4, 'England'), (9, 5, 'Florida');

CREATE TABLE IF NOT EXISTS `city` (
  `city_id` int(9) NOT NULL AUTO_INCREMENT,
  `state_id` int(9) NOT NULL,
  `city_name` varchar(30) NOT NULL,
  PRIMARY KEY (`city_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10;

INSERT INTO `city` (`city_id`, `state_id`, `city_name`) VALUES
(1, 3, 'Kalyan'), (2, 3, 'Mumbai'), (3, 3, 'Nagpur'), (4, 3, 'Pune'), (5, 3, 'Thane'), (6, 6, 'St Johns'), (7, 7, 'Shanghai'), (8, 8, 'London'), (9, 9, 'Miami');

db_connect.php

This file handles database connectivity from php server. Here I have used PDO & prepared statements for database communication as it's more secure and portable.

<?php
// mysql connection
$hostname = "localhost";
$username = "root";
$password = "";
$database = "db_demo";

try {
    $pdo = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("Error! " . $e->getMessage());
}
?>

index.php

This is the main file for our demo containing the user interface. It consists of an html form with three drop downs. And the ajax script is kept in a separate JavaScript file called 'script.js' and loaded at the end of index file.

<?php
include_once("db_connect.php");

// fetch country details
$stmt = $pdo->prepare("SELECT * FROM country ORDER BY country_name ASC");
$stmt->execute();
?>

<!DOCTYPE html>
<html>
<head>
    <title>Dynamic Dependent Select Box using PHP & AJAX</title>
    <meta content="width=device-width, initial-scale=1.0" name="viewport" />
    <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" type="text/css" rel="stylesheet" />
</head>
<body>
<div class="container" style="margin-top: 30px;">
<div class="col-md-6 col-md-offset-3">
    <div class="panel panel-default">
        <div class="panel-body">
            <h2 class="text-center">Dynamic Dropdown Demo</h2><br/>
            <form id="demo-form">
            <div class="form-group">
                <select id="country" class="form-control input-lg">
                    <option value="">Select Country</option>
                    <?php while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { ?>
                    <option value="<?php echo $row['country_id']; ?>"><?php echo $row['country_name']; ?></option>
                    <?php } ?>
                </select>
            </div>
            <div class="form-group">
                <select id="state" class="form-control input-lg">
                     <option value="">Select State</option>
                </select>
            </div>
            <div class="form-group">
                <select id="city" class="form-control input-lg">
                     <option value="">Select City</option>
                </select>
            </div>
            <div class="form-group">
                <input id="submit" value="Submit" type="submit" class="btn btn-lg btn-danger" />
            </div>
            </form>
        </div>
    </div>
</div>
</div>

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<script src="js/script.js"></script>
</body>
</html>

populate_data.php

This is the server script that communicates with mysql db and populates dropdowns with data. This file is called through ajax() function. It retrieves state and city details from database based on the chosen 'country_id' and 'state_id' resp. and returns them to ajax call which in turn populates the drop down lists.

<?php
include_once('db_connect.php');

if(isset($_POST['cid'])) {
    // fetch state details
    $stmt = $pdo->prepare("SELECT * FROM state WHERE country_id=:cid ORDER BY state_name ASC");
    $stmt->execute(array(':cid' => $_POST['cid']));
    echo '<option value="0">Select State</option>';
    while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo '<option value="' . $row['state_id'] . '">' . $row['state_name'] . '</option>';
    }
}

if(isset($_POST['sid'])) {
    // fetch city details
    $stmt = $pdo->prepare("SELECT * FROM city WHERE state_id=:sid ORDER BY city_name ASC");
    $stmt->execute(array(':sid' => $_POST['sid']));
    echo '<option value="0">Select City</option>';
    while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo '<option value="' . $row['city_id'] . '">' . $row['city_name'] . '</option>';
    }
}
?>

script.js

Finally the javascript file. It contains the jquery ajax functions that are triggered when country or state name is chosen.

$('document').ready(function() {
    $('#country').change(function(){
        var country_id = $(this).val();
        $("#state > option").remove();
        $.ajax({
            type: "POST",
            url: "populate_data.php",
            data: "cid=" + country_id,
            success:function(opt){
                $('#state').html(opt);
                $('#city').html('<option value="0">Select City</option>');
            }
        });
    });

    $('#state').change(function(){
        var state_id = $(this).val();
        $("#city > option").remove();
        $.ajax({
            type: "POST",
            url: "populate_data.php",
            data: "sid=" + state_id,
            success:function(opt){
                $('#city').html(opt);
            }
        });
    });
});

Done! We have the required database and coding files in place. Now run index.php and you can see a form populated with country dropdown.

country-state-city-drop-down-list-jquery-ajax-php

Now choose a country to load corresponding states in the state drop down via ajax. And a state to load respective cities in the city dropdown list.

php-mysql-country-state-city-dropdown-list

And you can see the entire process happening in a discreet way without page refresh thanks to Ajax.

Also Read: Insert, Read, Update & Delete CRUD in PHP and MySQLi

That explains about creating country state city dropdown list in php & ajax. Please let me know if you have any queries through comments.

6 comments:

  1. Really nice and definitely it will be useful for many people. Kindly keep update like this.

    School Website Designs

    ReplyDelete
  2. Sure, it’s a good thing to enjoy your work, but there is such a thing as enjoying it too much.
    bejeweled
    tank trouble

    ReplyDelete
  3. it's awesome tutorial..thanks bro, but i was qustion.

    if i sumbiT the value to sql, and than i want to UPDATE dropdown menu, how to make it (UPDATE DROPDOWN AJAX SQL) bro ? please help...thank before (sorry me english bad :D )

    ReplyDelete
    Replies
    1. Hi! Glad you liked it. Here I have used mysql db. If you plan to use SQL just make the connectivity changes and the same logic would work.

      Delete

Contact Form

Name

Email *

Message *