/* Create table country */
CREATE TABLE `country` (
`id` tinyint(4) NOT NULL auto_increment,
`country` varchar(20) NOT NULL default '',
PRIMARY KEY (`id`)
)
/*create table state*/
CREATE TABLE `state` (
`id` tinyint(4) NOT NULL auto_increment,
`countryid` tinyint(4) NOT NULL,
`statename` varchar(40) NOT NULL,
PRIMARY KEY (`id`)
)
/* Create table city */
CREATE TABLE `city` (
`id` tinyint(4) NOT NULL auto_increment,
`city` varchar(50) default NULL,
`stateid` tinyint(4) default NULL,
`countryid` tinyint(4) NOT NULL, PRIMARY KEY (`id`)
)
Now to test the demo in working we have to add some records in each of the tables. So run the following queries to add the records.
/* Inserting records into country table */
INSERT INTO `country` VALUES (1, 'USA');
INSERT INTO `country` VALUES (2, 'Canada');
/* Inserting records into state table */
INSERT INTO `state` VALUES (1, 1, 'New York');
INSERT INTO `state` VALUES (2, 1, 'Los Angeles');
INSERT INTO `state` VALUES (3, 2, 'British Columbia');
INSERT INTO `state` VALUES (4, 2, 'Torentu');
/* Inserting records into city table */
INSERT INTO `city` VALUES (1, 'Los Angales', 2, 1);
INSERT INTO `city` VALUES (2, 'New York', 1, 1);
INSERT INTO `city` VALUES (3, 'Toranto', 4, 2);
INSERT INTO `city` VALUES (4, 'Vancovour', 3, 2);
Now make one file index.php and paste the below code.
<form method="post" action="" name="form1">
<center>
<table width="45%" cellspacing="0" cellpadding="0">
<tr>
<td width="75">Country</td>
<td width="50">:</td>
<td width="150">
<select name="country" onChange="getState(this.value)">
<option value="">Select Country</option>
<?php while ($row=mysql_fetch_array($result)) { ?>
<option value=<?php echo $row['id']?>><?php echo $row['country']?></option>
<?php } ?>
</select>
</td>
</tr>
<tr style="">
<td>State</td>
<td width="50">:</td>
<td >
<div id="statediv">
<select name="state" >
<option>Select State</option>
</select>
</div>
</td>
</tr>
<tr>
<td>City</td>
<td width="50">:</td>
<td >
<div id="citydiv">
<select name="city">
<option>Select City</option>
</select>
</div>
</td>
</tr>
</table>
</center>
</form>
In the onChage event of the country drop down we have called getState() function of the javascript. Which change the options values of the State drop down, let’s look at the code the getState() function.
<script type="text/javascript" src="http://code.jquery.com/jquery-1.8.2.js"></script>
<script type="text/javascript">
function getState(countryId) {
var strURL="findState.php?country="+countryId;
var req = getXMLHTTP();
if (req) {
req.onreadystatechange = function() {
if (req.readyState == 4) {
// only if "OK"
if (req.status == 200) {
document.getElementById('statediv').innerHTML=req.responseText;
document.getElementById('citydiv').innerHTML='<select name="city">'+
'<option>Select City</option>'+'</select>';
} else {
alert("Problem while using XMLHTTP:
" + req.statusText);
}
}
}
req.open("GET", strURL, true);
req.send(null);
}
}
<script>
As you can see in the above code we are passing the countryid to the file findState.php, which populate the options in the drop down of the state which is fetched from Ajax , is given below.
<?php $country=intval($_GET['country']);
$con = mysql_connect('localhost', 'root', '');
if (!$con) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db('test');
$query="SELECT id,statename FROM state WHERE countryid='$country'";
$result=mysql_query($query);
?>
<select name="state" onchange="getCity(<?php echo $country?>,this.value)">
<option>Select State</option>
<?php while ($row=mysql_fetch_array($result)) { ?>
<option value=<?php echo $row['id']?>><?php echo $row['statename']?></option>
<?php } ?>
</select>
In the above code for the state dropdown, getCity() function is called on its onChage event having parameters countryId and stateId, now let’s look at the code of the getCity() function
<script type="text/javascript" src="http://code.jquery.com/jquery-1.8.2.js"></script>
<script type="text/javascript">
function getCity(countryId,stateId) {
var strURL="findCity.php?country="+countryId+"&state="+stateId;
var req = getXMLHTTP();
if (req) {
req.onreadystatechange = function() {
if (req.readyState == 4) {
// only if "OK"
if (req.status == 200) {
document.getElementById('citydiv').innerHTML=req.responseText;
} else {
alert("Problem while using XMLHTTP:
" + req.statusText);
}
}
}
req.open("GET", strURL, true);
req.send(null);
}
}
As you can see in the above ajax function, one file findcity.php is called and this PHP file populate the city dropdown according to the supplied parameters country and state from get method. Now let’s look at the code of findcity.php,
<?php
$countryId = intval($_GET['country']);
$stateId = intval($_GET['state']);
$con = mysql_connect('localhost', 'root', '');
if (!$con) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db('test');
$query="SELECT id,city FROM city WHERE countryid='$countryId' AND stateid='$stateId'";
$result=mysql_query($query);
?>
<select name="city">
<option>Select City</option>
<?php while($row=mysql_fetch_array($result)) { ?>
<option value=<?php echo $row['id']?>><?php echo $row['city']?></option>
<?php } ?>
</select>
