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. Ramsey Smith

    Roshan,

    I tried new version and it works very well. What can I say rather that you’re a star. Thanks a lot.

  2. imran

    triple drop down list from database using Ajax and PHP

    this code is not running?

  3. Hi, Thank yooooooooooooooooooooou for this code, this work greatly. best wishes

  4. kero

    Thank you for the script. am a beginner so your script was very helpful. But I am having trouble passing the variables upon submit. Please help.

  5. What kind of problem you’re facing can you please explain it ?

  6. kero

    oic, let’s try one more time. so sorry for the ultra long post

    text field:

    bracket input type=text name=’score[score]’ value=” bracket

    submit button:
    bracketinput type=’hidden’ name=’action’ value=’submitscores’ bracket
    bracket input type=’submit’ class=’button’ name=’submit’ value=’Submit >>’ bracket

  7. dolly

    To the brainee himself,
    Thanks a lot Roshan,was looking exactly for the same thing,you made my life easier.
    Bye
    Regards,
    dolly

  8. Damo

    Hi Roshan

    Love your work.

    However, when I change the option value of USA to be “USA” and echo $country in findState.php I receive the value 0. It doesn’t seem to like text?

  9. Damo

    Got it

  10. @Damo – what was the problem and how did you solve it can you share it with us ?

  11. Damo

    The solution was simple. The first line in the php file has an intval function. This just needed to be removed.

    However, I have been playing with your code and think I have found an issue. When the db contains a value with a space, only the data before the space is returned.

  12. @Damo – intval() is there for preventing sql inject attack, do filter user input before using it with database.

  13. Hi Roshan,

    I just wanted to say thanks for sharing this script with us. I used it on my website to let the user select a category and then a genre thats in that category :)

    There is only 1 thing i can’t seem to “adjust”, because when i do so the page where the genres will be loaded, does not load.

    When i change:
    req.onreadystatechange = function() {
    if (req.readyState == 4) {
    indto:
    req.onreadygenrechange = function() {
    if (req.readyGenre == 4) {

    it doesnt work anymore. I also dont understand what that part axactly does, buy i am a real javascript n00bie 😉

    Greetz, Lessy

  14. Jim

    Hi, Roshan

    I am new to web programming, and really like the details you posted. However, I’m stuck when I try to populate ‘state’. I downloaded your source files and always encountered a blank ‘state’ > after selecting ‘country’. Thanks for some guidance.

  15. billy

    Hello,

    I am trying to use your code but I am having problems with the last list box. Instead of calling the city function I put onchange=”alert(‘hi’);” Nothing happens at all when I select something. The first part of the code works so I am stumped.

  16. karthick

    This is my problem..

    I tryed to use this ajax example into a project..
    but while doing this task it shows an error “There was a problem while using XMLHTTP:Not Found”

    Now i want to know how to debug this problem…
    Can u help me?

  17. Hey it is not working for me?

    Kindly visit this page and see http://www.shubhkriti.net/en/post.php

  18. jhd

    if i want to add the 4th and 5th dependent drop down. what will you suggest to do.
    Thank you

  19. venki

    Hi,

    I sent u number of comments but i didn’t received any reply or solution… this is final one…. actually i used the above code to populate the drop down list boxes it is working fine… but my problem is when i used to this code and submit the form the first drop down list box value only stored in my database…. second one is stored as empty… am using only two… plz help me…..

    advance thanks….

  20. jhd

    i am trying to add the 4th and 5th they depend on 1+2+3rd query please help

  21. John

    anyone here monitoring this forum because there is bunch question not answered:

    like

    i am trying to add the 4th and 5th they depend on 1+2+3rd query please help

  22. Pradeep

    Hi Roshan

    The first and the second drop downlist works absolutely fine but on changing the 2nd drop down items nothing happens for the 3rd drop down.
    basically the javascript “getcity” function is not able to execute while we select an item from the STATE.

    I need this urgently.. any help would be greatly appreciated.

    thanks!!

  23. Pradeep

    Hello Guys
    I found out the issue. In the findState.php file you need to make sure that the arguments are properly passed
    <select name=”state” onchange=”getCity(”,this.value)”>
    should be in ” single quotes if its a string.
    This worked for me
    thanks Roshan for the code !!

  24. Abs

    Hello,

    When I pass a countryid which DOES not have a state assigned to it in my database, I get a dialog which says “There was a problem using XMLHTTP. Not found”. Is there a way to suppress this error message the “state select” list is just empty ?

    Thanks Abs

  25. It worked for me. I think there must have been some browser problem. It is working fine for me. Visit my site at http://www.shubhkriti.net

  26. Raja M

    Hi Roshan!!!!
    I got your Triple combo ajax code. It’s working nice. thanks for your code

    By
    Raja M

  27. Rahul

    thanks buddy ….i’ve implemented 4th , 5th and 6th combo boxes.. you code is easy to understand.thanks for the demo.

  28. Michael

    Thanks for the code!!! It works great. Question? Once all the fields are selected (country, state, and city) how can you submit (button) the information to a detail page based on the city ID?

    I did read a post that referred to “you can use the javascript code document.location=’abc.php’; on the onchange event of that particular dropdown…” Is that to call just a URL, and can I add to it to provide page results based on any particular ‘ID’?

    Your help is appreciated.

    Thanks Again!!!

  29. OH YES!!! I figured it out. Thanks for not responding, because it made me more persistant.

    Thanks again!!!

  30. hello..
    Im facing the problem of my final year project. I dont know what to do until i found this blog. thanks alot to the owner! appreciate it so much. tq

  31. Vidya Kiran

    Dear Mr.Roshan,

    The code is very helpfull for me but i need a small change when i select country it is showing states and when i select states showing cities but when i change country again the cities list should not show previous cities it should ask the user to select state first. can u please change the code to work like that.

    Thanks in advance.
    Vidya Kiran.T
    Vijayawada
    Andhra Pradesh

  32. Vidya Kiran

    Dear Mr.Roshan,

    I have another question, by using this way i ab able to select country, state and city and store this along with some other data into database, when it comes to edit mode of a data how can i show it as selected country,state and city. please help me in this issue.

    Thanks in advance.
    Vidya Kiran.

  33. James

    Hi Roshan,

    I have a question, actually two.

    (1) I am using the country name as the actual string (instead of countrycode as integer value). This is because of requirement. I would like to know how to handle a string with a space (like “United States”). this.value is picking up only the first word “United” to hand over to javascript during the uri query string formation. How can I overcome this to include the space and any following words as well ?

    (2) So as based on my requirement if I am not using countrycode as integer value but as string/character data, what other php / filter check can I use to protect fro sql injection attack that may be applicable to string type data.

    any ideas with how to implement with a code example for both (1) and (2) would be great.

    Thanks for this wonderful tutorial. It is the best example I have found so far on the web and it works great except the issues I mentioned based on my requirements.
    -james.

  34. shree

    Hi Buddy
    I am using the script but its not working in IE , when i try to get the values by $_Post method it returns blank !
    its working fine in Mozila firefox
    Any Solution?
    Thankx in advance

  35. Roshan,

    Thank you for providing the script. I really appreciate it.

    Would you be so king as to provide the addtional script need to insert the selected text values into a table? I can insert the selected value into my table, but this is the integer associated with the user’s choice. How do you insert all three text values into three distinct columns in a table? i.e. Canada, British Columbia, Vancouver. not 2 , 2, 4.

    Thank you very much for your help.

    Steve

  36. Thanku very much

  37. sreenu

    it is very nice example. but i am facing one problem that is after posting the values i want to get the
    posted value as selected and remaing values in the drop down could you please pls help me….

    thanks,
    Sreenu

  38. Harshal

    Hi ,Thanx for ur excellent support.

  39. steve

    can you tell me how i can add multiple sets on the same page? i need for start and finish locations.

  40. aminay

    Hi,
    I would this code but with oracle database ,I make some modification to connect with oracle bu not work,
    please give me the full solution,
    thanks roshan!

  41. Andrew

    Hi I am using your triple drop down menu, but I would like to place more sets of 3 drop down menus on the same page. The problem I am facing is how to make them indipdendent from one another. If I just duplicate the drop down menus, then when I make a selection in the second set of menus the values from the first changes.
    How can I solve this? I believe it has to do with the ajax script that should “know” I need to handle more than one set of 3 drop down menus, but I am not familiar with ajax…
    can you please help me?
    thank you

  42. Sove

    The script is working successfully, thank you. But how can we combine this script with “Changing textbox value from dropdown list using Ajax and PHP” script. For example when I changed the first drop-down box, I want that second drop-down box is going to be changed and on another tag, the value of textbox is going to be changed.

  43. Ivo75

    Hi Roshan this is very good and work fine in Mozilla but have some litle mistake in IE7 when I insert new record in database this record isn’t view in IE7 but in Mozilla record is view. If You want I can post my code

  44. hello, thank you for your post. I wonder if you can help me with something. I’m using a similar function that I wrote to generate a list based on the previous selection just like yours, and just like yours it seems the AJAX function is echoing the code to the document in, kind of, a virtual way. When I view the source on my page, the code that is echoed back via AJAX does not display. The same happens with your code as well. This becomes a problem because I want to send the variables( the names of the options) to another php file to be inserted into a database or emailed. Since they aren’t really there…the values are never filled and the PHP script returns and error.

    Is there any way to do this?

  45. Dave

    Hi everyone. Great code and thanks alot, just what I need. I have modified it for a similar project i’m working on and it works fine until the third doprdown. Although the funtion is called and php script activated, the second variable (state in your case) is not recognised, so the dropdown remains empty. If I specify a value in the query instead its fine. The problem is passing the variable between the scripts. i don’t know why it doesn’t work for me, any light on how these variables are passed would be great. Thanks alot again great blog.

  46. Joseph

    @ Dave, did you find a solution to your problem yet as I’m facing the same issue.

  47. Thanks so much.. After long time i got your great tutorial.

  48. kenan

    Can someone convert this code into asp and java please?

  49. Hi Roshan..

    something cause my project need the 4th combobox that still have relation with 1st, 2nd and 3rd combobox..

    how can we simply customize your triple populate combobox..??

  50. cva

    Hi Roshan,

    Thanks a lot for u r script.
    Its worked for me like a charm…..

Comments are closed.