Populate triple drop down list from database using Ajax and PHP

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

157 thoughts on “Populate triple drop down list from database using Ajax and PHP

  1. Any Help for 4 populate combobox…?
    Please Help..

  2. Naveed

    Hi Roshan:

    Thanks for this wonderful tutorial+script. I am a novice to PHP but well experienced with HTML. Is it possible to open up a new web page upon clicking the “Submit” button? If so, does that web page need to be created manually in HTML or could that be created dynamically based on what selections users have made. For example, if users have selected, US –> New York –> Buffalo then the page should only show the information specific to Buffalo, New York. Can an admin interface be applied to this script so that adding/deleting countries, states, and cities be made easier?

  3. maleli

    Hi and thanks for this great script. It worked great for me.
    I only have one little issue. My application is entirely in spanish and some words have accents on them. When I retrieve them I get a question mark. I tried getting results directly from getCity.php for example, and it displays correctly. Only when it’s displayed through the AJAX request I get this error.
    Is there any way to fix this?

  4. meir

    HI

    Your code is very usefull exactly what I need for an upcoming project. My question is that since the form is posting to itself how can I transfer the final selected data to another form?

    In the past I used the following

    if ($_POST[‘Submit’]==’Login’)
    {

    But its not working

  5. No matter what i try i cannot get it to work on IE7 or IE8

    i have amended the code somewhat but it works on ALL other browsers but those two.. what can i do???
    help!!!!!!!!!

  6. Shannon

    Hello Roshan,

    I have utilized this script to only use the country and state part. It seems to be working great. My problem that I am having is that the Country and State ids that are selected are not going into the DB correctly.

    For example, If I choose United Kingdom for the country which has an ID of 190 (you can view source to see that the value of this option is 190) and then select Scotland as the state which has an ID of 3007, it inserts 127 for both country and state IDs in the table of my DB.

    I don’t know why this could be? Have you heard of this before?

    One more question too. What does “if (req,readyState == 4)” and “if (reg.status == 200)” mean in the javascript?

    Thanks for all you have done with this code the selections work great. Just need to find out the id issues.

    Shannon

  7. Shannon

    Hey Roshan,

    I found the problem. It was a DB field type mistake.

    Thanks anyway! Great Script once again.

    Shannon

  8. jco

    Great code!

    Quick question. I’ve got it set up in an project I’m working on but I ran into a problem. Is there a way to pass alpha stings? I.E. if the values on the first drop down were alpha instead of numeric. I had been trying to pass alpha strings, but it only passes “0”. I looked back at your code and noticed that you always used numeric values so I tested my code with a numeric value and it passed it just fine. I hope that made sense.

    Is there a way to pass an alpha string?

    Thanks for the help!

  9. jco

    moments later, problem solved.

    didn’t think about the intval bit of php code. Changed to strval and all is well.

  10. kwame

    Is there a way we can get this to work with more than just three select, perhaps increase it unlimited

  11. vishnu

    very bad very bad its not working for me

  12. First of all thanks a lot for the code, it works like a charm.

    I have a question thou. When I submit to another page from the form… the state of the country dialog box is not retained. The value is not passed.

    Any help would be greatly appreciated. Thanks.

  13. Sorry to disturb again. My issue is exactly as follows.

    Since when the submit button is clicked. The changes and contents of the state dialog box are not retained… and if there is an error, the user will be forced to re-select the country again.

    Is there anything I could do about it please?

  14. mariane

    Hi, nice tutorial… can i ask what if i like to add another dropdown after city like schools… do i need to modify the function getXMLHTTP() {}?

  15. shambhu sharan

    nyc blog to help new and novie web developers…thanks

  16. Hey, thanks for the code.

    I currently need help understanding the last part of findstate.php.

    I notice you put a new dropdown menu for the state yet in index.php their is already a drop down for state.

    but in findstate.php you added on the onchange javascript function.
    When I tried the code it shows the states dropdown within the states dropdown. So their is 2 state drop downs but one is inside the other.

    When I take out the tags and Select A State I would get a working state drop down showing the states being populated from the database.

    The problem is that I can’t populate the third drop down menu. Since I didn’t use the onchange.

    Can you explain the last part of the findstate.php where you populate the drop down menu.
    I want to know why you added the select tags? Also explain how it works .like in your index.php you already created the state drop down menu. It looks to me you create 2 state drop downs.

    I am not clear on this part. So if you can explain how we can put a onchange on the states drop down menu. That would be great.

    Thank you for your time.

  17. Hi, I now can’t pass the values. I am trying to pass them with post from form to another php page.

    I am not getting the values on the other page. So the form becomes useless. How do I fix this?

    how does the values stay put.

  18. Azawe

    Thank you man for the script But i wana know how to change encoding coz the alphabet look like this ?????

  19. phyu aung

    i test this but i cannot reach findState.php

  20. Melinda

    Hi, I just started using PHP last week and this week I’m attempting to create an advanced search form. I want them to link together like the state/city/zip example and had the hardest time finding a good example online of how to handle the situation once there are more than two selection boxes available. Your post has been great!

    I’m having an issue with my second selection box. If I select something in my first box it filters the second box correctly. If I select something in my second box I’m getting an error somewhere but I’m not sure how to debug it. If I change the code of my second box so that it works like the first (ie it doesn’t pass along the data from the first box as a second parameter) everything works fine so I know I don’t have an obvious typo somewhere.

    My second selection box looks like this:
    <select multiple size=5 name="Package" style="width: 15em;" onchange="setPackage(, this.value)”>
    …………..

    I think the issue is with the “”. All of the other opening brackets I have in my code are “<?php" but if I try doing "<?php=" Eclipse gives me an error.

    My function looks like this:
    function setPackage(str, blah)
    {
    xmlhttp=GetXmlHttpObject();
    if (xmlhttp==null)
    {
    alert ("Browser does not support HTTP Request");
    return;
    }
    var url="getVendor.php?";
    url=url+"package="+str;
    url=url+"&partType="+blah;
    url=url+"&sid="+Math.random();
    xmlhttp.onreadystatechange=stateChanged2;
    xmlhttp.open("GET",url,true);
    xmlhttp.send(null);
    }

    Any help would be greatly appreciated.

  21. Davide

    Hi Roshan,

    a quick question: is it possible to retrieve the after al the selections have been made? I looked at the html code after making the selections, and it only shows the initial state of the options before any selection is made (ie. options for and “Select Country First” and “Select State First”. Any tip?

    Tnx

  22. Davide

    Quick add:
    I need the option values to save/process the inputs given by the user in the dropdowns.

    Tnx again.

  23. vijayan

    very nice job thanks exactly get the result

  24. Rohit

    very useful source for me…

    thanks roshan…

  25. Does not work =( Roshan pls visit my site and look at the error: http://www.lightbending.webhop.net
    I have download your sourcecode and batch your sql file in mysql 5

  26. Leo Zhang

    It’s work, but when I apply in my website, it isn’t work….

    I think, the problem is about condition
    –> WHERE countryid=’$countryId’ AND stateid=’$stateId'”;
    Can you explain about that condition?

  27. Leo Zhang

    Or I send all my code to Mr Roshan, so you can check where the problem is?

  28. dee

    i figured out a nice 3 dropdown select

    it is working on all browsers but ie6

    i understand it is an onchange issue

    your help thanks

  29. tuankaka

    Thank you Roshan, i am doing things the same way as you did but my problems is that i am generating the dropdownlist within the backend of joomla frameworks. When the dropdownlist is generated, not only the dropdownlist itself but also the joomla backend banner generated. What can i do with this? Thanks again!

  30. zia

    still waiting to know about 4 or 5 pair combox box code… i got success in 3 combo box… please no one answered about more than 3 combox box

  31. Ben

    Is it possible to get the data from box 1 out of mysql database? Just like box 2 and 3

  32. Max

    Hey Roshan, I tried the code given above but its not working, I mean its not flagging any error but its not working. Whenever I select a country it shows nothing in the state combo list. Though I’ve added some records manually in my database table. but its not retrieving any info. You help would be highly appreciated.

    Thanks in advance!

  33. john Tz

    I have a problem, if i wanted to post the selected values in to another page i get only the first option country i don know what is the problem because i rembered to put my action page all names are correct, and when i tried to get the values on the same page by removing the action page it works,
    can any one help me

  34. Refrenced in the blog post is the proof that it is not possible to parse recursive structures with regular expressions. There is no point in arguing about that. Regular expressions might, of course, work in some cases for such examples, but won’t work in every case.

  35. Markus P.

    I like this script and i was able to get it run. but on the side i need it, i also use moocheck.js and now your script is “disabled”.
    The message i get in Error-console of Firefox:
    “reference to undefined property c.onmousedown
    reference to undefined property c.onmouseup
    reference to undefined property c.onclick”

    Is there a solution for my problem or can you help me?

  36. alferg

    Hi Roshan, theres no data when i submitted the form.

    Please help

  37. Markus P.

    Hi Roshan!

    I solved my problem. I only had to change the “onChange” to “onmouseover” like in the moocheck.js and now it is working fine.

  38. amit

    Hello,

    I download and run the code. But it is not working. Country names are coming. When I select the country from the list the state list is not filled. Please let me know why it is happening.

    Thanks & Regards.

  39. I want to extend you triple down down using AJAX up to six drop downs in a very similar fashion. So what will i have to do? My deadline is about to reach please do something roshan, I will be very thankful to you.

  40. tasha

    Hi i have downloaded the zip file containing the source code provided, created the database tables but the state and city drop down list boxex don’t get populated with data as it should…..Any suggestions as to why this happens???

  41. gnawz

    I have followed the tips on HTML tag placement of forms and tables, however, data in my second drop down is not posted to the dataabase. What else may I do?

  42. gnawz

    Hi

    I have formatted my HTML tags well but still cannot get the values of the second drop down to post to the database.

    Any more help?

  43. danyal

    very helpful article… exactly this is what i was lookin for

  44. danyal

    @tasha:plz check the path of the page that u r calling

  45. rutvij

    cool tutorial……
    thnx…. it’s easy to understand & implement

  46. Great stuff, just what I was looking for. You make ajax very simple to use !

  47. n747

    I go through ur tripple drop down ajax based note…It’s really really good…But it not work properly…I also got error not properly load status and city…So If you have corrected code please send me it…Bcz Its very urgent to me…That is great help…

    Thanks dear….You r a real good guy…I appreciate this service..All the Best

  48. Vignesh

    Thanks a lot friend…………i had to do a little modification on the code, now it works fine, thanks

  49. lumz

    Thanks man this helped

  50. Anil Gomez.D

    It’s really really good

Comments are closed.