Country State City Dropdown using Ajax in PHP MySQL
This tutorial will explain you how to make triple dropdown of country state and city using ajax. You will find them implemented in various sites registration form. On the selection of any country value, the very next dropdown will populated with that country’s states names. On selection of the state, last dropdown will be filled with the cities of that selected state value.
One thing you notice here that all this we are doing happens without page refresh. To accomplish this we have to use ajax so that page will not get refreshed during selection process. So let’s have a look behind this whole process how this happens.
First of all we must have the database having three tables for country , state and city. So your first step would be create the required tables. To create the tables, just run the sql statements given below.
CREATE TABLE `country` (
`id` int(11) NOT NULL auto_increment,
`country` varchar(20) NOT NULL default '',
PRIMARY KEY (`id`)
);
CREATE TABLE `state` (
`id` int(11) NOT NULL auto_increment,
`countryid` tinyint(4) NOT NULL,
`statename` varchar(40) NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `city` (
`id` int(11) 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=mysqli_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 style="">
<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.
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:\n" + req.statusText);
}
}
}
req.open("GET", strURL, true);
req.send(null);
}
}
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']);
$conn = new mysqli("localhost", "root", "password", "test");
// Check connection
if ($conn->connect_errno) {
echo "Failed to connect to MySQL: " . $conn->connect_error;
exit();
}
$query = "SELECT id,statename FROM state WHERE countryid='$country'";
$result = mysqli_query($conn, $query);
?>
<select name="state" onchange="getCity(<?php echo $country?>,this.value)">
<option>Select State</option>
<?php while ($row = mysqli_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
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:\n" + 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']);
$conn = new mysqli("localhost", "root", "password", "test");
// Check connection
if ($conn->connect_errno) {
echo "Failed to connect to MySQL: " . $conn->connect_error;
exit();
}
$query = "SELECT id, city FROM city WHERE countryid = '$countryId' AND stateid = '$stateId'";
$result = mysqli_query($conn, $query);
?>
<select name="city">
<option>Select City</option>
<?php while($row = mysqli_fetch_array($result)) { ?>
<option value=<?php echo $row['id']?>><?php echo $row['city']?></option>
<?php } ?>
</select>
UPDATE
If you want to access these Dropdowns values in another form to insert in database or any purpose then what you need to do is:
(1) Define the action in the form of this page.
Example : <form method="post" action="save.php" name="form1">
now submitted form data can be accessed in the file named save.php
(2) Create one file with any name you like or what you have defined in your action.
(3) Now simply write the following code in the newly created file.
if (!empty($_POST)) {
print_r($_POST);
}
The above code will generate the following output (with values what you have selected from the dropdowns):
Array
(
[country] => 2
[state] => 3
[city] => 4
[submit] => submit
)
Now you can simply access like below,
$country = $_POST['country'];
$state = $_POST['state'];
$city = $_POST['city'];
You can now use these variables for what you want.Use them in sql query etc..That’s it. This is how you can make this type of triple dropdown. Just paste the code in the appropriate files.