Tuesday 13 November 2012

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;
}
?>

3 comments:

  1. wow thats cool :) thank u so much for that awesome code :)

    ReplyDelete
  2. I am the same anonymous above.. :)

    i m facing a minor problem. tried to solve it since few days but cudnt figure out hw to do it.

    "I need to count the number of rows fetched from the database after every ajax selection.how to do it??? "

    I tried putting a count variable in javascript,den in php code inside the javascipt function, also tried php couting in getcity files , but i cudnt succesfully retrieve the number of records returned.

    Please help me with it

    ReplyDelete
    Replies
    1. Try to use mysql_num_rows($result). in ajax php after you execute your query will give you total number of rows. And while you are getting response just add to your returned response with some separator like '||'.
      in javascript when you receive success response using split function with that separator ( response.split("||")) you will get array of that and one of them will be your count as per you concated sting in php.

      possibly give code so i may able to answer well. where you made mistake

      Delete