Friday, 12 September 2014

AJAX driven related drop downs


Today we are going to investigate how  we can make a triple related  drop down of country, county and city using AJAX.

You may have seen this in various registration form.

When you select any country, the next drop down will populated with that country’s counties names.
When we select a county the last drop down will be filled with the cities for the  selected county.

Run the following sql scripts in your favorite sql manger to create the tables in your database.
We recoomend the free Workbench application for use with MySQL server.

/* 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`)
)

For testing we need some data, run the three sql scripts below to populate your tables with some data.

/* 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 we create a test page called  test.php.Which will produce the example below:

Country :
State :
City :


<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 onChange event of the country drop down we will call the getState() JavaScript function. This function will change or populate the options values of the State drop down, let’s look at the code for 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 example we are passing the countryid to the file findState.php, which populate the options in the drop down of the state which is retrieved with AJAX server side code. Below is the code for findState.php.


 
<?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 drop down, the getCity() function is called on each  onChange event passing the countryid and stateid, which will help us retrieve all cities for the now selected country and state.

Now we still need to create the JavaScript code for 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 JavaScript function, a file findcity.php is called and this PHP file populate the city drop down based on the supplied country and state id.

Now all that is left to do is the creation of the code for 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>

Referenced articles:

http://www.thesoftwareguy.in/multiple-dropdown-with-jquery-ajax-and-php/
http://www.dreamincode.net/forums/topic/321253-postback-in-php-with-jqueryajax-and-json/

Let us hope this was helpful and let me wish you good luck.

Charlie :-)

Total SEO Services

No comments:

Post a Comment