country state city ajax dropdown

PHP MySQL country state city ajax drop down with database.

When you are creating triple or more than 2 dependent dropdown then you might need to take care of parents and child dropdowns. Means if you make changes to parent then your child nodes should get affected too.

Here in this country state city dropdown as you change country then it is required to change states. and when states get changed required to change cities too.

Below code works well in all modern browsers like Chrome, mozilla, IE9/8/7 and works well so copy and enjoy it

Database design/structure for country, state and city tables

---

CREATE TABLE IF NOT EXISTS `country` (

  `Country_Id` varchar(3) NOT NULL,

  `name` varchar(60) NOT NULL,

  `continent_code` varchar(30) NOT NULL,

  `tel_code` varchar(5) NOT NULL,

  PRIMARY KEY (`Country_Id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

`continent_code` and `tel_code` is not necessary yet you can avoid that fields.

CREATE TABLE IF NOT EXISTS `state` (
  `state_id` varchar(6) NOT NULL,
  `country_id` varchar(3) NOT NULL,
  `name` varchar(30) NOT NULL,
  PRIMARY KEY (`state_id`),
  KEY `Country_Id` (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `city` (

  `city_id` varchar(10) NOT NULL,

  `state_id` varchar(6) NOT NULL,

  `city_name` varchar(50) NOT NULL,

  PRIMARY KEY (`city_id`),

  KEY `state_id` (`state_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- foreign key constrains for table state and city (If you dont know simply avoid this)

ALTER TABLE `city`

  ADD CONSTRAINT `city_ibfk_1` FOREIGN KEY (`state_id`) REFERENCES `state` (`state_id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `state`

  ADD CONSTRAINT `state_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`Country_Id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- End of database structure.

Javascript Code

---

<script>

var xmlhttp;

function ajaxFunction(url,myReadyStateFunc)

{

   if (window.XMLHttpRequest)

   {

      // For IE7+, Firefox, Chrome, Opera, Safari

      xmlhttp=new XMLHttpRequest();

   }

   else

   {

      // For IE5, IE6

      xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");

   }

   xmlhttp.onreadystatechange= myReadyStateFunc;        // myReadyStateFunc = function

   xmlhttp.open("GET",url,true);

   xmlhttp.send();

}

function getState(x)

{

    // in second argument of ajaxFunction we are passing whole function (onreadystatechange function).
    // Goto getState.php code

    ajaxFunction("getState.php?cid="+x, function()

    {

        if (xmlhttp.readyState==4 && xmlhttp.status==200)

        {

var s = xmlhttp.responseText;    //   s = "1,2,3,|state1,state2,state3,"

s=s.split("|");                              //   s = ["1,2,3,", "state1,state2,state3,"]

sid = s[0].split(",");                    //  sid = [1,2,3,]

sval = s[1].split(",");                   //  sval = [state1, state2, state3,]

st = document.getElementById('state')

st.length=0

for(i=0;i<sid.length-1;i++)

{

st[i] = new Option(sval[i],sid[i])

}

getCity(-1) // emptying the city.

        }

    });

}

function getCity(x)

{

    // in second argument of ajaxFunction we are passing whole function.
   // Goto getCity.php code

   ajaxFunction("getCity.php?sid="+x, function()

   {

       if (xmlhttp.readyState==4 && xmlhttp.status==200)

       {

var c = xmlhttp.responseText;

c=c.split("|");

cid = c[0].split(",");

cval = c[1].split(",");

ct = document.getElementById('city')

ct.length=0

for(i=0;i<cid.length-1;i++)

{

ct[i] = new Option(cval[i],cid[i])

}

        }

    });

}

</script>

<style>

select{width:270px;size:10}

</style>

HTML Code

---

<table>

<tr>

<td>Country </td>

<td>:&nbsp;<select name="country" id="country" onchange="getState(this.value)">

<option value="-1">Select Country</option>

<?php

$cntry = mysql_query("SELECT `Country_Id`, `name` FROM country ORDER BY `name` ASC");

while($row = mysql_fetch_assoc($cntry))

{

echo '<option value="'.$row['Country_Id'].'">'.$row['name'].'</option>';

}

?>

</select>

</td>

</tr>

<tr>

<td>State </td>

<td>:&nbsp;<select name="state" id="state" onchange="getCity(this.value)">

<option value="-1">Select State</option>

</select>

</td>

</tr>

<tr>

<td>City </td>

<td>:&nbsp;<select name="city" id="city" >

<option value="-1">Select City</option>            

</select>

</td>

</tr>

</table>

PHP Code for getState.php

---

$link = mysql_connect("localhost","root","");

$dbSelected = mysql_select_db('pra_countrystate');

$id="-1,";

$sname="Select State,";

$cid = $_GET['cid'];

if($cid=="-1")

{

$response = $id."|".$sname;

echo $response;

}

else

{

$result = mysql_query("SELECT `state_id`, `name` FROM `state` WHERE `country_id` = ".$cid." ORDER BY `name` ASC");

while($row = mysql_fetch_assoc($result))

{

$id .=$row['state_id'].","; 

$sname .=$row['name'].","; 

}

$response = $id."|".$sname;

echo $response;

}

?>

PHP Code for getCity.php

---

<?php
$link = mysql_connect("localhost","root","");
$dbSelected = mysql_select_db('pra_countrystate');
$id="-1,";
$cname="Select City,";
$sid = $_GET['sid'];
if($sid=="-1")
{
$response = $id."|".$cname;
echo $response;
}
else
{
$result = mysql_query("SELECT `city_id`, `city_name` FROM `city` WHERE `state_id` = ".$sid." ORDER BY `city_name` ASC");

while($row = mysql_fetch_assoc($result))
{
$id .=$row['city_id'].",";
$cname .=$row['city_name'].",";
}
$response = $id."|".$cname;
echo $response;
}
?>