Change dropdown list (options) values from database with ajax and php
- Thursday, December 13, 2007, 17:58
- ajax, php
- 109 comments
I’m going to show you a example in php and ajax to change the values of the dropdown’s options without refreshing the page. The values (options) of the dropdown are fetched from the database and the certain portion of the web pages is only refreshed without need to refresh the whole page.
Let’s start with creating the two tables country and city and insert some data
CREATE TABLE country ( id tinyint(4) NOT NULL auto_increment, country varchar(20) NOT NULL default '', PRIMARY KEY (id) ) TYPE=MyISAM;CREATE TABLE city ( id tinyint(4) NOT NULL auto_increment, city varchar(50) default NULL, countryid tinyint(4) default NULL, PRIMARY KEY (id) ) TYPE=MyISAM;
Now let’s look at the html code, let’s look at the code of the form and its elements
<form method="post" action="" name="form1"> Country : <select name="country" nChange="getCity('findcity.php?country='+this.value)"> <option value="">Select Country</option> <option value="1">USA</option> <option value="2">Canada</option> </select> <br />City : <div id="citydiv"> <select name="select"> <option>Select City</option> </select> </div> </form>
As you can see that when the dropdown named “country” is changed the “getCity” function is called. Look at the other dropdown carefully, it is inside the division called “citydiv”.
Now let’s look at the javascript function called “getCity”
function getCity(strURL) { var req = getXMLHTTP(); // fuction to get xmlhttp object if (req) { req.onreadystatechange = function() { if (req.readyState == 4) { //data is retrieved from server if (req.status == 200) { // which reprents ok status document.getElementById('citydiv').innerHTML=req.responseText; } else { alert("There was a problem while using XMLHTTP:\n"); } } } req.open("GET", strURL, true); //open url using get method req.send(null); } }
now we’ve to create the file called findcity.php and put the following PHP code
<? $country=$_GET['country']; $link = mysql_connect('localhost', 'root', ''); /change the configuration if required if (!$link) { die('Could not connect: ' . mysql_error()); } mysql_select_db('db_ajax'); //change this if required $query="select city from city where countryid=$country"; $result=mysql_query($query);?> <select name="city"> <option>Select City</option> <? while($row=mysql_fetch_array($result)) { ?> <option value><?=$row['city']?></option> <? } ?> </select>
Thats all, whenever you change the dropdown of country the values of the city dropdown is automaticalled changed without refreshing the page.
If you want to download full source code, Click here to download.
Security Note : If you want to use this code in your project then there is a security flaw in the PHP code, please use $country=intval($_GET['country']); in the php code instead of $country=$_GET['country']; in the findcity.php to prevent your site from sql injection attack.
Popularity: 44% [?]
Related Posts
» Changing textbox value from dropdown list using Ajax and PHP
» Populate triple drop down list from database using Ajax and PHP
» Slider Using PHP, Ajax And Javascript
» Ajax login validation system in PHP using jQuery
109 Comments on “Change dropdown list (options) values from database with ajax and php”
Trackbacks
- Populate triple drop down list (change options value) from database using Ajax and PHP - country, state and city drop down list in ajax and php
- Changing textbox value from dropdown list using Ajax and PHP
- pligg.com

one thing that i did not understand is when PHP code gets executed
bcoz u had not called that php file in ur code
Yes…i got it..Thanks for reply and code
As you can see drop down of country ,onChange event is called. And you can see clearly that i’ve called the filename and parameter from there..
welcome man…
hi i am getting this error
“There was a problem while using XMLHTTP:”
so how to fix this?
is there is any configuration for AJAX??
sumit are you using the older version of browser? or you’re placing the files in the different folder, the error you’re getting may be due to incorrect path or may be due to the older browser. if you get more errors then, e-mail me at bhattarairoshan[at]yahoo[dot]com.
Is there any way to expand this to 3 drop downs? Country => Region => City
In response to Sumit’s post on Dec 27th at 9AM:
Roshan’s AJAX XMLHTTPRequest only works in FireFox and Opera. Microsoft Internet explorer requires you to use their XML ActiveX extensions in the browser. If they aren’t enabled, it won’t work. (IMHO, Microsoft has gone mad with browser extensions, which are ruining IE).
The javascript must also be changed to account for this.
I don’t have the code needed on hand, but if you google “cross-browser ajax request”, i’m sure you can find it.
You should use mysql_real_escape_string on the $country variable before putting it into the query string. Any hacker could pass something like:
‘US’; DROP TABLE city;–
to your script, causing serious damage to your database.
mysql_real_escape_string is handy, but remember that it requires you to be connected to the database.
In this example it would not degrade performance to use it; however, in other situations, addslashes() is a more effective tool.
The reason I stress this is that if your application is in a query-building phase, ideally you want it to wait to connect to the database until the query is ready to run. Otherwise, the mysql connection resource is idle in memory and may degrade site performance under very high stress.
For this AJAX application, it may be more efficient to recall pulldown data from a cache. MySQL does support query caching, but again, you are required to connect to the database to utilize it. Since country, region, city, etc… do not change very often, it would be more effective to replace a pulldown file cache whenever the database is changed.
cool
thanks man
I have one problem, if this script is working in a form, on Firefox it works fine and replaces the drop down, but when the form gets submited it seems that it does not treat this drop down field as part of the form, therefore the city field can’t be submited.
At least I can’t get it in FF, works fine in IE6 + IE7
any solution for that?
well it seems that your html is not formatted correctly..try putting the form element outside of the table tag which contains the form elements..
Thanks Roshan, I got it working after fixing it how you said. Your scripts is great.
Thanks maba…
Any suggestions as to why the innerHML of the id is not being updated by the responsetext? I used some JS alerts ti display the responsetext and it looks good… just will not show… TIA!
please check the case of “innerHTML” it should be like this “innerHTML”. I think it will help good luck..
hi i saw your code and it is somehow similar to mine. could you help me with my error?..
you see i have a text field named source. once a user typed in in the said field, through ajax, it will check the contents of my database. the result will be enumerated in my dropdown named error_message..
now the problem comes when i submit the form. i can’t retrieve the one being selected in my drop down.. here is my code for the dropdown:
Select Destination
i tried to call :
in hopes that i could get the value of the select (drop down)…
but it did not print aanything…
anyone who knows how can i retrieve the data?
thnx!
i saw that my code was not properly shown in my previous post… to repeat, this is my code for the dropdown:
Select Destination
you must have the problem with the dom format i think..just try putting the form tag above the table tag..i.e create the form element first then put tables inside that form, hope this helps
Roshan thank you very much for this script. Finally I got it working (I had some small errors on my side
)
I am able to modify your code with my db and it works properly. When I integrate it into the complete form … which uses several tables inside of the form element, it reloads the entire page inside the tags. I’m new to Ajax … and this is frustrating me … please help.
senol and Maba, I just wanted to thank you for the answer. I have been hunting the net for a few days now trying to find an answer to the problem you encountered:
The problem: when replacing OPTION tags of a form using Ajax, the newly selected options do not get sent to the server, but only in FireFox.
The solution: make sure your FORM is properly formatted. A previous developer had set it up like this:
I changed it to this, and it fixed it:
Looks like my HTML didn’t make it. Here’s the gist of it:
table
form
/table
/form
changing it to this, fixed it:
form
table
/table
/form
Hi,
Thanks for a great script. However, I’m experiencing some problems with my implementation. Everything seems to work, but the value from the second drop-down doesn’t get sent with the form? I’m using a div-bades layout.
Any ideas?
well..the same problem of DOM…I think you need to form tag outside of div
form
div
div
form
hope this helps….
Hello I m not able to get the value of ajax select list when submit the form. can anyone help.
hey gourav..if you go through the few previous comments you have the solution out there…its same problem with the format of DOM…
hello thanks for reply. but i m not using div based layout. i m sending my code. can u plz help me to solve the issue of not sending the field in form submit.
Name *
Address *
Description *
Coutry *
Select Country
Afghanistan
Albania
Algeria
American Samoa
Andorra
Angola
Anguilla
State *
Select State
City *
Select City
Member Image : *
<!–
Name *
Address *
Description *
Coutry *
Select Country
Afghanistan
Albania
Algeria
American Samoa
Andorra
Angola
Anguilla
State *
Select State
City *
Select City
Member Image : *
–>
Thanks, I solved the probs with ur help. Thanks again.
Welcome gourav
It works mate !!!
Cheers
Ranjeet
Regarding the problems with the submit if the second droplist has no value. Here is my fix:
Instead of:
You should use:
<option value=”">
This code works, so thanx. But when I click submit, I cannot see the city POST value. I have a feeling is has to do with:
‘findcity.php?country=’+this.value
Something needs to be added to this.
Any help implementing this solution using submit would help. Thanx!
if you don’t see values in the post then you can see the comments and answer of this post
thaks roshanbh.com
This works in IE but not Firefox.
DO you know why this may be? Thanx!
this question is already answered before in the comment…but here is the answer once again..
this is the same problem of DOM…I think you need to form tag outside of table
form
table
table
form
hope this helps
I am surprised by this tutorial.
Thank you very much is the minimun for this good.
oh really George……nice to hear that…and I would like to surprise people more..
thankssssssss
Thanks a looooot.
Why I can’t use it with WAMP (windows apache mysql, php) ? But it’s fine under uni* server. Is there any limitation so it cant be used in localhost like WAMP ?
regards,
No man there is nothing to with WAMP…I’ve developed it under XAMPP..try to check the configuration .. there is no problem with platform
I have tried and the example is worked. But when I changed to my db, the list is not exactly work, it shows all ‘city’ instead of some that choosed by ‘country’. I use intval($_GET['country']).
thanks you…
very very cool thankss
bilgilendirme için çok çok te?ekkürler
Thank you very much for your work with this script.
I do have a question regarding the second onchange (“onchange=”getCity(,this.value)”>”). For my purposes I’m trying to use a alpha in place of the $country value, which is an integer in the stock script form. When I try this, I get a Javascript error, basically that, whatever the value of $country is, is undefined within the Javascript.
To easily test, try hard-setting the first value in the onchange as follows – “onchange=”getCity(abc,this.value)”>”, and “abc” will be the undefined error in the Javascript.
Has anyone else come across this. I can work around it, but would like to know if I’m missing something else logic-wise.
thanku you
The code is good enough. But I have added some functionality to it as I have submitted the form by inserting some extra fields and validations into it. When I submit a form after selecting country and state but keeping other fields unfilled then a validation arises. In this case, country remains selected but the selection of state gets lost. How can I maintain the relationship between country and state after form submission.
online çiçek sipari?iyle ?imdi çiçek göndermek çok daha kolay.çiçekçilik sektörönü sanata dönü?türen tek çiçekçi..
thankyouuuu
Nice script, does just what I was looking for.
For anyone having those problems with the ajax generated select value not being submitted, you do have to add value=”" to each option.
@Bryson – thanks for the suggestion…….
For sharing thank you very much good very beautiful work
hey there, i was having some problems with the display of options, but i found a solution
instead of this code:
Select City
i added php after the ?, and a ; after each variable, here’s the solution
Select City
hope this helps out some fellow programmers (:
thanks
thanks I have found this so informative to me….. thanks alot again ….
Thank you, this code was very helpfull
great imformatipn
Hey,
After ages of breaking my head, i came across your code, thanks so much!
Could you help me with this small query though –> Suppose i have a submit button, and i call a js function, How do I pick up the selected value from the city drop down?? I’ve struggled with this with… please help!
Thanks
Thanks for you article. This will work great if JavaScript is enabled in the browser.
If JavaScript is disabled in the browser, i want to submit the form with post when the country is changed then in the php file i want to get the country id from the post and filter the cities and update the city fields. Please help me on how to post form data with country id when the country selection is changed.
I want to use Ajax (if JavaScript is enabled) or form submit (if JavaScript is disabled).
How can I find out the JavaScript is enabled or not in the browser? I want this functionality in both situations.
Thanks .
hey hameed checkout this post know how to handle disabled javascript in browser..hope it helps
http://roshanbh.com.np/2008/04/how-to-know-handle-disabled-javascript.html
Thanks for the code
my options are usually:
switch
how do i get this to work with your tool?
when i try:
it doesn’t show the 2nd value in the select box
thanks
okay, i did actually read the comments – they just didn’t stick in
so i have a different problem
i am sending the value to the external php page just fine, and i am using that value for two different queries and two different pull down boxes
trouble is, i can’t seem to get both select boxes to show up as i want them to
how do i make sure my divs are separated?
does that make sense?
thanks
The script is working perfectly on one of my webhosting account I bought from USA. But it’s not working on my server at Turkey. Everything is same. Files, accounts, databases.. but it’s giving me the javascript error. I think there is some misconfiguration on the server at Turkey. Server administrator is my friend.
You can see the error: http://193.255.145.15/1/
Do you know any wrong setting on php or apache that can be cause that problem? (buffer etc.)
You can check the phpinfo from the link I wrote below:
http://193.255.145.15/phpinfo.php
I am waiting your replies. Thanks and sincerely,
Onur
Meanwhile further tribulations i expect
I was using this for two drop-downs, Make and Model of cars. When I choose ‘Citroen’ or ‘Chrysler’ from the first list nothing would appear in the second but all other makes worked fine. When I changed the query string to ‘citroen’ with a small ‘c’ it worked so I added a strtolower() when outputting the value of the option in the first dropdown.
This resolved the problem but i have no idea why it happened.
How would I change the code to be able and populate the first drop down for the database instead of USA
Canada
have tried several options and can get it to populate the first drop down but then does not populate the second.
sorry I mean
“USA”
” Canada”
sorry I mean
“USA”
” Canada”
Hey there!
Amazing script!!!!
Question for you, Im trying to use this in connection with another one of my scripts that I use to “duplicate forms”
essentially I have a form with 2 inputs, that can be “duplicated” when you duplicate the inputs, i have it adding [ ] to the end of each variable in order to be able to have an array caputure data on the other side for each of the inputs, Im having trouble incorporating your script with mine because of the [ ] , do you happen to have a way where I could have multiple of these auto dropdowns on a single page?
Thanks for you article. This will work great if JavaScript is enabled in the browser.
If JavaScript is disabled in the browser, i want to submit the form with post when the country is changed then in the php file i want to get the country id from the post and filter the cities and update the city fields. Please help me on how to post form data with country id when the country selection is changed.
I use your script for a car make /model dropdown. This wroks great, accept for Citroën.
The first dropdown shows Citroën but the second dropdown shows no models. In the DB there are several models. Looks like it doesn’t recognize Citroën in the DB. Has it something to do with the trema on the e.
Any solution?
Hey dudes I figured it out. I finally understood. There should be a main table containing all the field controls. And the form tags should be outside that table tags.
Thanks for this code roshan keep it up…!
Hey dudes I figured it out. I finally understood. There should be a main table containing all the field controls. And the form tags should be outside that table tags.
Thanks for the post, quite a nice tutorial. Will definitely use in the future
Thanks for you article. This will work great if JavaScript is enabled in the browser.
If JavaScript is disabled in the browser, i want to submit the form with post when the country is changed then in the php file i want to get the country id from the post and filter the cities and update the city fields. Please help me on how to post form data with country id when the country selection is changed.
What about the tables content???
Anyone has the SQL table code for the entries????
you must have the problem with the dom format i think..just try putting the form tag above the table tag..i.e create the form element first then put tables inside that form, hope this helps
thanks very good !
i love this blog
want to submit the form with post when the country is changed then in the php file i want to get the country id from the post and filter the cities and update the city fields. Please help me on how to post form data with country id when the country selection is changed.
Not working!!!!!!!!!!!!!!!!!!!!!!!!!!!!
i download your code and modified for me
actually i wanted to use 2 combo
1 for Master and another if master A is selected then it will populate
its correspondence child in second list
but it will not displaying by using your code
i m working in php+mysql
is dere any other code for the same problem
hei it’s cool and very nice to use. but i need to populate more than one textbox by selecting a value in the drop down list. pls help me. let me clear abt my probs
by selecting the employee id in the dropdown list, the employee name and employee email id and employee tel no. must be displayed in the corresponding textboxes. kindly help me with your clear cut example style.
Hi
I got the code to work and would like to open a webpage when the final selection is made. Is this possible ? What additional code would I need.
hi all,
i hv a problem wd this script…
the 2nd dropdownlist for city is not populating anything after selecting the first one…
olz help me………
can i get code for the contact us from using ajax and php?
i need to display the content of contact us form in other page using php and ajax. can anyone help me?
Hi all,
Code is great! But… does anyone know how to pass back a second field? (not only the city name in this example but the city id as well)????
please heeeeelp !!!!
Thank you very much for everything
thanks very good !
thanks admin very good !
thank you very much admin good very beautiful work
hi, i got this error “object expected”. How to solve this problem? Thanx in advance
Any ideas if there’s a different way of doing this using the JQuery library which will get around the browser issues?
Very nice. I just wonder how could it be for NON mysql
im total newbie into ajax and js. Could you tell me howto make it without mysql?
for ex.> USA -> 3 options
ASIA -> 5 options and so on
Very nice good article Thanks admin
thanks for all it is very nice blog
Very simple and descriptive narration. Thank you very much.
3 years after you share your code, I find it and download source files. It works great! I googled so much I got warnings it though I was a bot, lol. Yours was the only ajax/php code I found for populating a dropdown box while not requiring the php echo function. You are very talented. Thank you so much for sharing it with all of us.
want to submit the form with post when the country is changed then in the php file i want to get the country id from the post and filter the cities and update the city fields. Please help me on how to post form data with country id when the country selection is changed.
want to submit the form with post when the country is changed then in the php file i want to get the country id from the post and filter the cities and update the city fields. Please help me on how to post form data with country id when the country selection is changed….