Dynamic Country State city selection using PHP, Jquery , Mysql and Ajax

Hello, friends hoping you are liking my tutorial . In this tutorial, I am creating a simple tutorial in which you can select Country than corresponding state and city. I have the complete database of all country corresponding their state and city. This section is also used in many websites where registration of the user is required . So, I have added the tutorial to benefit the user they can use this in their project or assignment . Hopefully, you will like this tutorial. I will try to completely elaborate the tutorial so, that it will become easy to understand the concept. Happy Coding…

php,mysql,ajax,dynamic selection,country,state,city

PHP Dynamic Selection using PHP, MYSQL and Ajax

1 ) Database Config and Tables : —
a) I have created three table i.e Country, State and Country.
Country Will Contain the country name with their shorthand code.
State table will contain the coressponding state of that country.
City will Contain the cities of that particular state.
Following are the code for creating the table
b) Country :-

        CREATE TABLE `blog_countries` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `sortname` varchar(3) NOT NULL,  `name` varchar(150) NOT NULL,  PRIMARY KEY (`id`))
       

c) State:-

      CREATE TABLE `blog_states` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(30) NOT NULL,                   
      `country_id` int(11) NOT NULL DEFAULT '1',  PRIMARY KEY (`id`))
      

d) Cities:-

         CREATE TABLE `blog_cities` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(30) NOT NULL,  `state_id` int(11) NOT NULL,  PRIMARY KEY (`id`)) 
      

2) Database connection

:-
In this file we are Creating the Database connection using PDO. This file is common in all the
Tutorial.

             <?php

/**
 * @author Abhay Pandey (abhayhk29@gmail.com)
 * Date : 14 May 2016
 * Config class containing the database connectivity
 */

class DBConnection{
    
    //Please specify your own database credentials
    
    private $host = "localhost";
    private $db_name = "blog";
    private $user_name = "root";
    private $password = "";
    public $connection = null;
    
    public function getConnectData(){
        $this->connection = null;
        try {
            $this->connection = new PDO("mysql:host=". $this->host . ";dbname=".$this->db_name, $this->user_name, $this->password);
        } catch (Exception $exc) {
            echo "connection error please check your credentials ". $exc->getTraceAsString();
        } 

        return $this->connection;
    }
    
    public static function disconnection(){
        self::$connection = null;
    } 
}
       

3) Model or file where all the database query is taking place.

I have added the needed comments in the file.For further detail please see the given below code.

<?php

/**
 * @author Abhay Pandey <abhayhk29@gmail.com>
 * Date : 14 MAy 2016
 */

class Location{
    
    // DataBase connection Name and table NAme
    private $conn;
    private $tableState = 'blog_states';
    private $tableCity = 'blog_cities';
    private $tableCountry = 'blog_countries';
    
    //table data objects 
    
    public $name;

    public function __construct($db) {
        $this->conn = $db;
    }

    /**
     * Give the all Country Name
     * @return array
     */
    public function readCountry(){
        $query = "select * from " . $this->tableCountry . " ORDER BY id";
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        $row = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return $row;
                
    }
    
    /**
     * Return all the state of that country
     * @param type $countryID
     * @return array
     */
    public function readAllStateByCountryID($countryID){
        $query = "select * from " . $this->tableState . " where country_id = ? ORDER BY id";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1,  $countryID);
        $stmt->execute();
        $row = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return $row;
                
    }
    
    /**
     * 
     * @param type $StateID
     * @return array of that city
     */
    public function readAllCityByCountryStateID($StateID){
        $query = "select * from " . $this->tableCity . " where state_id = ? ORDER BY id";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1, $StateID);
        $stmt->execute();
        $row = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return $row;
    }
    
}

4) Controller file where interaction of model and view is happening

Here we are getting ajax call from the index file and we are calling the model file for fetching the required data from the database.Please see below code for understanding.

   <?php

/**
 * @author Abhay <abhayhk29@gmail.com>
 * @copyright (c) 2016, Abhay Pandey
 * @version 1.0
 */

    if (isset($_POST["action"]) && !empty($_POST["action"])) { //Checks if 
      $action = isset($_POST["action"]) ? $_POST["action"] : '';
        
      include 'includes/config.php';
      include 'includes/location.php';
      
      $dataBaceConnection = new DBConnection();
      $db = $dataBaceConnection->getConnectData();
      $location = new Location($db);
      
        switch ($action) {
            case 'country':
                    $countryID = isset($_POST['countryID']) ? $_POST['countryID'] : '';
                    $state = $location->readAllStateByCountryID($countryID);
                    $return["json"] = json_encode($state);
                    echo json_encode($return);                    
                    exit();
               break;
            case 'state';e $stateID = isset($_POST['stateID']) ? $_POST['stateID'] : '';
                    $city = $location->readAllCityByCountryStateID($stateID);
                    $return["json"] = json_encode($city);
                    echo json_encode($return);                    
                    exit();
                break;;

            default:
                break;
        }
    }

  

5) Index file

In this file we are collecting the input from the user where user can give input and we proccessed the requesst and we will display the result as the output.Please see the code below for further details.

     <!DOCTYPE html>
<!--
@author Abhay Kumar Pandey
email: abhayhk29@gmail.com
Date : 22 July 2016 9:45 Pm 
-->
<html>
    <head>
        <meta charset="UTF-8">
        <title>State Country City PHP OOPS PDO</title>
        
            <meta charset="utf-8">
    <script src="js/jquery.min.js"></script>
    <link href="css/bootstrap.min.css" rel="stylesheet" media="screen">
    <script src="js/bootstrap.min.js"></script>
    <script src="js/main.js"></script>
    </head>
    <?php 
      include 'includes/config.php';
      include 'includes/location.php';
      
      $dataBaceConnection = new DBConnection();
      $db = $dataBaceConnection->getConnectData();
      $location = new Location($db);
      $country = $location->readCountry();
    ?>
    <body>
        <div class="container">
            <div class="row">
                <h3>Country State City with PHP OOPS PDO</h3>
                
                <div class="form-group col-md-4">
                    <label for="sel1">Country</label>
                    <select class="form-control" id="country" onchange="getStatae();">
                        <option>Select the country</option>
                        <?php
                            foreach ($country as $key => $value) { ?>
                        <option value="<?php echo $value['id'];?>"><?php echo $value['name']; ?></option>
                            <?php }
                        ?>
                    </select>
                </div>
                <div class="form-group col-md-4">
                    <label for="sel1">State</label>
                    <select class="form-control" id="state" onchange="getCity();">
                    </select>
                </div>
                <div class="form-group col-md-4">
                    <label for="sel1">City</label>
                    <select class="form-control" id="city" onchange="getCityText();">
                    </select>
                </div>
            </div>
            <div class="row"> 
                <div class="form-group col-md-4">
                    <label>Selected Country : </label>
                    <label class="selected_country"> </label>
                </div>
                <div class="form-group col-md-4">
                    <label>Selected State : </label>
                    <label class="selected_state"> </label>
                </div>
                <div class="form-group col-md-4">
                    <label>Selected city : </label>
                    <label class="selected_city"> </label>
                </div>
            </div>
        </div>
    </body>
</html>

  

6) Java Script file

I have created the different file for javascript. Actually, it is good practice to create a different file for
the javascript and another file. By doing this it is easy to maintain in long and easy to maintained the code and also easy to locate the bug :). We have used the jquery for each to iterate the loop. Please see carefully.Please find the code below:-

/**
 * @author Abhay Kumar Pandey
 * @email abhayhk29@gmail.com
 */

function getStatae(){
    var countryID = $('#country').val();
    var countryText = $('#country option:selected').text();
    var data = {
        "action": "country",
        "countryID": countryID,
    };
    $.ajax({
            type: "POST",
            dataType: "json",
            url: "ajax.php", 
            data: data,
            success: function(data) {
                var state = $.parseJSON(data.json);
                var stateHtml = '<option>Select State</option>';
                $.each(state, function( index, value ) {
                    stateHtml += '<option value="'+ value.id +'">'+ value.name +'</option>';
                });
                $('#state').html(stateHtml);
                $('.selected_country').text(countryText);
            }
    });
}

function getCity(){
    var stateID = $('#state').val();
    var stateSelected = $('#state option:selected').text();
    var data = {
        "action": "state",
        "stateID": stateID,
    };
    $.ajax({
            type: "POST",
            dataType: "json",
            url: "ajax.php", 
            data: data,
            success: function(data) {
                var city = $.parseJSON(data.json);
                var cityHtml = '<option>Select City</option>';
                $.each(city, function( index, value ) {
                    cityHtml += '<option value="'+ value.id +'">'+ value.name +'</option>';
                });
                $('#city').html(cityHtml);
                $('.selected_state').text(stateSelected);
            }
    });
}

function getCityText(){
    var citySelected = $('#city option:selected').text();
    $('.selected_city').text(citySelected);
}

Hopefully you like the tutorial .If you need the code please comment your emailid . I will mail the code.
In future I will add the download and demo link in coming weekend. Thank you. Happy coding…..

26

4 Responses

  1. Abhishek Bhatt
    August 25, 2016
  2. Nikhil Agrawal
    October 6, 2016
    • abhay kumar Pandey
      December 3, 2016

Write a response