Populate triple drop down list from database using Ajax and PHP

• Thursday, January 24, 2008, 10:17
• ajax, php
• 157 comm 
•  
•  
•  
• I’ve got many email from people asking for populating triple drop down list from the database without refreshing page using Ajax and PHP after posting the first article related to the ajax dropdown list using php .In this post, I’ve put three drop down of country , state and city and the drop down’s value changes without refreshing the page. Now let show you how to create it quickly.

First create the following tables of country city and states,

CREATE TABLE `country` (
  `id` tinyint(4) NOT NULL auto_increment,
  `country` varchar(20) NOT NULL default '',
PRIMARY KEY  (`id`)
) TYPE=MyISAM   ;

CREATE TABLE `state` (
 `id` tinyint(4) NOT NULL auto_increment,
 `countryid` tinyint(4) NOT NULL,
`statename` varchar(40) NOT NULL,
PRIMARY KEY  (`id`)
) TYPE=MyISAM   ;

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`)
) TYPE=MyISAM   ;

Now place the following form in the index.php file

<form method="post" name="form1">
 <table border="0" cellpadding="0" cellspacing="0" width="60%"><tbody>
  <tr>
   <td width="150">Country</td>
   <td width="150"><select style="background-color: #ffffa0" name="country" onchange="getState(this.value)"><option>Select Country</option><option value="1">USA</option><option value="2">Canada</option>       </select></td>
  </tr>
 <tr>
  <td>State</td>
  <td>
  <p id="statediv">
  <select style="background-color: #ffffa0" name="state"><option>Select Country First</option>       </select></td>
</tr>
<tr>
  <td>City</td>
  <td>
  <p id="citydiv">
  <select style="background-color: #ffffa0" name="city"><option>Select State First</option>       </select></td>
</tr>
</tbody></table>
</form>

As you can see above, in the onChage event of the country drop down getState() function of the javascript is called which change the options values 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;
  } else {
       alert("There was a problem while using XMLHTTP:\n" + req.statusText);
  }
       }
      }
   req.open("GET", strURL, true);
   req.send(null);
   }
}

The code of the PHP file findState.php, which populate the options in the drop down of the state which is fetched from Ajax , is given below

<? $country=intval($_GET['country']);
$link = mysql_connect('localhost', 'root', ''); //changet the configuration in required
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db('db_ajax');
$query="SELECT id,statename FROM state WHERE countryid='$country'";
$result=mysql_query($query);

?>
<select name="state" onchange="getCity(<?=$country?>,this.value)">
 <option>Select State</option>
  <? while($row=mysql_fetch_array($result)) { ?>
    <option value=<?=$row['id']?>><?=$row['statename']?></option>
  <? } ?>
</select>

In the above state dropdown, getCity() function is called in onChage event with countryId and stateId parameter, 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("There was a problem while using XMLHTTP:\n" + req.statusText);
        }
      }
    }
    req.open("GET", strURL, true);
    req.send(null);
  }
}

In the above ajax function, 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']);
$link = mysql_connect('localhost', 'root', ''); //changet the configuration in required
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db('db_ajax');
$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><?=$row['city']?></option>
<?php } ?>
</select>

And thats all, the triple drop down list of city, country and state using Ajax and PHP will be populated.
VIew Live Demo
To download full source code, click here