Populate triple drop down list from database using Ajax and PHP

Posted on January 24, 2008
Filed Under ajax, 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

Popularity: 34% [?]

Follow me on twitter at http://twitter.com/roshanbh.

» Changing textbox value from dropdown list using Ajax and PHP
» SQL Injection Attack - Examples and Preventions in PHP
» Change dropdown list (options) values from database with ajax and php
» Slider Using PHP, Ajax And Javascript

Comments

64 Responses to “Populate triple drop down list from database using Ajax and PHP”

PHP Coding School » Blog Archive » php tips [2008-01-24 12:29:26] on January 24th, 2008 12:30 pm

[...] Populate triple drop down list (change options value) from … By Roshan I’ve got many email from people asking for populating triple drop down list from the database using Ajax and PHP after posting the first article related to the ajax dropdown list using php Now let show you how to create it quickly. Roshan Bhattarai’s Blog - PHP… - http://roshanbh.com.np [...]
Babak on January 24th, 2008 9:57 pm

I really love and enjoy your blog, Thanks.
Roshan on January 28th, 2008 6:26 am

Thanks a lott babak….
Newbie on February 21st, 2008 10:52 am

Nice job. Thanks!
Fajar on March 29th, 2008 2:18 am

After almost 2 months searching and learning AJAX from the bottom for my job problem, I found your site, I think this is my search end. Thanks Roshan, now I have learning new knowledge and have idea how to fix my previous AJAX code. Thank you so much for your code share.
Roshan on March 29th, 2008 6:25 am

Thanks Fajar…thanks a lottt…and please let me know some negative aspects of this blog as well..which will help me to improve the quality of this blog…
Sean on March 31st, 2008 4:17 am

great blog with a TON of information.
looking for a suggestion on how to clear the 2nd and 3rd lists if the first is changed to prevent the possibility of submitting the wrong data.

thanks again

Sean
Roshan on March 31st, 2008 5:08 am

on that situation you can write conditions in fincity.php and findstate.php to clear the dropdown ..
hope this helps
Sean on April 1st, 2008 5:01 am

makes sense, i was thinking that when the month changes the state drop down it would change the city drop down, and it doesn’t
i guess need to learn more about java script
Kim Hauw on April 9th, 2008 9:48 am

Thanks u very much
swathi on April 10th, 2008 1:39 pm

I have done some modifications in database content and in php pages..
and i have run the same…its not working….
pls go thru my code…

function GetXmlHttpObject()
{
var xmlHttp=null;
try
{
xmlHttp=new XMLHttpRequest();
}
catch (e)
{
try{
xmlHttp=new ActiveXObject(”Msxml2.XMLHTTP”);
}
catch(e)
{
xmlHttp=new ActiveXObject(”Microsoft.XMLHTTP”);
}
}

return xmlHttp;
}
var xmlHttp;
function getstate(countryid)
{
xmlHttp=GetXmlHttpObject();

if(xmlHttp==null)
{
alert(”Browser Does not support HTTP request”)
return;
}
var url=”findstate.php?country=”+countryid;
xmlHttp.onreadystatechange=statechanged;
//alert(url);
xmlHttp.open(”GET”,url,true);

xmlHttp.send(null);
//alert(url);
}
function statechanged()
{ // alert(’url’);
if(xmlHttp.readyState==4 || xmlHttp.readyState==”complete”)
{
document.getElementById(’statediv’).innerHTML=xmlHttp.responseText;

}
else
alert(’wait’+xmlHttp.readyState);
}

function getcity(countryid,stateid)
{
xmlHttp=GetXmlHttpObject()
if(xmlHttp==null)
{
alert(”Browser Does not support HTTP request”)
return;
}
var url=”findcity.php?country=”+countryid+”&state=”+stateid;
xmlHttp.onreadystatechange=statechanged2;
xmlHttp.open(”GET”,url,true)
xmlHttp.send(null)

}
function statechanged2()
{
if(xmlHttp.readyState==4 || xmlHttp.readyState==”complete”)
{
document.getElementByID(’citydiv’).innerHTML=xmlHttp.responseText
}
}

Country
Select Country
India
USA
Canada
Stateselectstate
Cityselectcity

and my findstate.php code is like this
<?php
$countryid=$_GET["country"];
$con = mysql_connect(”localhost”,”root”,”root123″);
if(!$con)
{
die(”could not connect:”.mysql_error());
}
mysql_select_db(”testtool”,$con);

$query=”select id,statename from state where countryid=’”.$countryid.”‘;
$result=mysql_query($query) or die(mysql_error());

echo “<select name=’state’ onchange=’getcity(”.$countryid.”,this.value)”;
echo “”.count($result).”";
while($row=mysql_fetch_array($result))
{
“.$row['statename'].”
}
echo “;
mysql_close($con);
?>

After selecting country..it is displaying select option for state….
pls guide me….
Roshan on April 11th, 2008 2:29 pm

well…after selecting country, it does display the dropdown for stat, I couldn’t get what is your problem !!
Sachin on April 22nd, 2008 9:27 am

Thanks Roshan.
I love and enjoy your blog.
tell me some negative aspects of this blog as well,to improve myself.
Thanks u very much once again.
mario on April 30th, 2008 3:37 pm

thx roshan!
just what i was looking for…
i have to link this with editable grid what will easy my life a lot!
thx again… :-))
mario on April 30th, 2008 3:46 pm

btw…
i took a close look and noticed that i have no idea how can i make that after user makes his choice to, for example, open a page linked to what he choose?

nevertheless, this is still a good thing…
Roshan on April 30th, 2008 4:31 pm

well mario….you can use window.open to open a new link using javascript
mario on April 30th, 2008 4:50 pm

thx!
ill see what i can do…
im novice, so maybe a few line of code..(on your example)
City div is generated by php, and i really dont know where should i put it…
bye!
Roshan on April 30th, 2008 5:57 pm

ok tell me in detail how do you want it to happen in detail so that I can tell you in detail…
mario on April 30th, 2008 8:46 pm

ok thx!
i will use framesets so that combo boxes will remain visible…
in other words im planning to retain this kind of functionality as it seen on this site:
http://www.yxscripts.com/cs/examples/loader3.html

so basically when user chooses city (third combo option) script should automatically load some page in lower(down one) frame…
as list of the cities are automatically populated im wonder if that is even possible to do in this way…?

list is populated by admin directly to the database and then (data are) used on few pages of the same domain..

thx…
mario on April 30th, 2008 8:50 pm

and yes, this is tested web site…
it has a lot of things to do still..
mario on April 30th, 2008 9:30 pm

just to tell you that i found one php grid (php buider) that satisfied things that i needed..

thx anyway…
cheers!
Roshan on May 1st, 2008 4:11 am

nice to hear that mario..
mario on May 1st, 2008 2:43 pm

yes, thank you!
nice thing though!
cheers!
Doug on May 16th, 2008 5:59 pm

on that situation you can write conditions in fincity.php and findstate.php to clear the dropdown ..
hope this helps

Thanks for sharing this.

Would you provide some code snipits or give some suggestions as to th functions I should use to write the conditions.
Prince on May 19th, 2008 9:21 am

Hello Roshan. I am using same downloaded files (without change). After selecting country it shows empty city list in drop down. It does not get City Names. Why ?
Leah on June 25th, 2008 6:37 am

Thank you very much for the tutorial. I am trying to create 4 drop down lists so i am modifying your code to include another list, but i am having problems.

This code populates the first two drop downs but when i try to populate the third drop down it is not make a call to the script. Im not sure why. In your code you have the onchange call in your php script so i thought that since this is a server side script the onchange needed to be in the html, but that doesnt seem to work. Do you have any ideas?

Thank you for all your help.
xerxes on June 27th, 2008 3:58 am

Hi, I’ve had problems loading the page. I can’t load the codes with just 2 dropdown boxes by Roshan as well. The database is connected. After selecting the country, the next dropdown box does not load. I’m stucked.
x on July 2nd, 2008 3:30 am

gosh why doesn’t roshan or anyone reply anymore?
Roshan on July 2nd, 2008 4:49 am

Can you first check the the response in ajax by using alert(req.responseText); and you can see what is the response coming from that file
xerxes on July 2nd, 2008 7:04 am

<select name=”state” onchange=”getCity(,this.value)”>

Hi i got this error message when i used firebug. Am clueless. It looks fine. Are you able to help?
xerxes on July 2nd, 2008 7:15 am

does the demo codes have bugs? Because i am trying to debug the demo codes all these while.
Roshan on July 2nd, 2008 8:57 am

I think there is one “,” extra in the onchange=”getCity(,this.value)” just remove , before this.value
Sonny on July 2nd, 2008 2:39 pm

It won’t let me post my comment.
Roshan on July 2nd, 2008 3:47 pm

@Sonny - LOL .. then how were u able to post that comment??
Sonny on July 2nd, 2008 5:31 pm

Sorry, that was my second comment. Turns out my first comment was too long.

I ended up figuring it out. I had put a piece of code for you to look at with me. But like I said… I figured it out.
I will say that I just found your blog yesterday and spent about 3 hours on your site reading over all the different PHP/AJAX topics.
I love it.
It is one of the best learning resources I’ve seen for people who are new to AJAX but need to implement it now.
Thanks, keep up the good work.

Sonny.
Roshan on July 2nd, 2008 6:03 pm

@sonny - Thanks a lot for the appreciation. If you liked this blog so much then why don’t you subscribe for the RSS feed of this blog by email..

http://www.feedburner.com/fb/a/emailverifySubmit?feedId=1456759&loc=en_US

and help me to become the blogging idol..
Tony on July 9th, 2008 4:19 pm

Love the auto-populate scripts. One question, how can you post a url based upon the final result “City”.

For example, once you get to the final combobox and choose “Los Angeles” is there a way to redirect to a specific url, based upon final selection. Sorry if this sounds confusing.

Thanks in advance
Roshan on July 9th, 2008 5:23 pm

you can use the javascript code document.location=’abc.php’; on the onchange event of that particular dropdown…
Sanjeev on July 16th, 2008 8:10 am

I am listing County once the state is picked and it works like a charm but when I click on City- I get this message. There was a problem while using XMLHTTP:Not Found.

Web site is a work in progress. Please help.

Thanks
Roshan on July 16th, 2008 9:49 am

It is saying not found means…it not finding the the PHP file..can you check the location of the file and how you’re accessing it via javaScript
Sanjeev on July 17th, 2008 12:19 am

Found the mistake. Upper case and lower case issue - I was calling findCity.php but script was named findcity.php

Thank you for leading me in the right direction.
John on July 18th, 2008 6:10 pm

There is a problem with either the GetCity function or the findState.php. The problem is from this line
<select name=”state” onchange=”getCity(, this.value)”>

this.value is not being passed. The $country variable is. This is why so many people are having difficulty displaying the city. I am not sure why it is not being passing. The sample website works, so can you ensure that the findState.php on there is the same as you have listed?

Thank you in advance for your help.
Roshan on July 19th, 2008 4:53 am

@john - I think everything is right there with the code and only few people are having difficulty in using them due to some problem

Anyway, I’ll check the code again and see if something is wrong there or not
Bobik on July 24th, 2008 6:15 am

I had no problem testing the script (IE, Opera, FW) in a Windows XP PC with Apache, PHP, and Mysql installed manually. However, in another Windows PC where mysql is installed as a service, Apache and PHP installed manually, the second dropdown (State) will disappear after selecting an option from the country dropdown. It is only an observation. I hope some readers will find it useful.
Vec on July 30th, 2008 1:00 pm

Nice code, but it doesn’t seem to work for me. Nothing happens for me when I click on a value in the first list. I know all my stuff is set up correctly because using a different ajax script it works fine. I’m more then sure all of my variations of your code are correct, and I know the findstate.php works because if I type in findstate.php?country=2 or w/e It works fine. However there seems to be something wrong with it doing an auto refresh when you change the value.
Vec on July 30th, 2008 1:17 pm

Hmm, I seemed to have gotten it, your code in your zip file contains many illegal characters, using different ” errors out, and also your xml object is named differently, if you fixed that your code would be splendid
Roshan on July 30th, 2008 5:08 pm

@Vec - Ya I see this I’ll surely going to make the correction..
Ramsey Smith on August 3rd, 2008 9:32 pm

Hi,
Everything works fine in FIrefox nut in IE I am experiencing error ‘Object Not found”
Roshan on August 4th, 2008 5:52 pm

@Ramsey and @Vec - I’ve updated the code and mistake. You can download the source code now and it will work in IE properly as well you’ll not find “Object not found error”
prakash on August 5th, 2008 5:01 am

i have problem in Ajax code. The error is encounter as
“There was a problem while using XMLHTTP:not found ”
plz tell me how to resolve it.
Roshan on August 5th, 2008 5:31 am

property look at the path of the file …as it is giving 404 not found error. You can check the path as well as character case of PHP file
Ramsey Smith on August 5th, 2008 6:28 am

Roshan,

I tried new version and it works very well. What can I say rather that you’re a star. Thanks a lot.
imran on August 6th, 2008 12:18 pm

triple drop down list from database using Ajax and PHP

this code is not running?
Bardiy on August 21st, 2008 5:14 pm

Hi, Thank yooooooooooooooooooooou for this code, this work greatly. best wishes
kero on August 24th, 2008 6:58 am

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.
Roshan on August 24th, 2008 8:58 am

What kind of problem you’re facing can you please explain it ?
kero on August 25th, 2008 12:40 am

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
dolly on August 26th, 2008 7:03 am

To the brainee himself,
Thanks a lot Roshan,was looking exactly for the same thing,you made my life easier.
Bye
Regards,
dolly
Damo on September 28th, 2008 8:41 am

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?
Damo on September 28th, 2008 11:13 am

Got it
Roshan on September 28th, 2008 3:42 pm

@Damo - what was the problem and how did you solve it can you share it with us ?
Damo on September 30th, 2008 11:55 am

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.
Roshan on October 1st, 2008 5:13 am

@Damo - intval() is there for preventing sql inject attack, do filter user input before using it with database.
Lessy on October 4th, 2008 5:01 pm

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