PHP MySQL country state city ajax drop down with database.
`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;
PHP Code for getState.php
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
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;
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
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
// 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
// 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>: <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>: <select name="state" id="state" onchange="getCity(this.value)">
<option value="-1">Select State</option>
</select>
</td>
</tr>
<tr>
<td>City </td>
<td>: <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;
}
?>
$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;
}
?>
