Change dropdown list (options) values from database with ajax and php

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.

117 thoughts on “Change dropdown list (options) values from database with ajax and php

  1. Sumit

    one thing that i did not understand is when PHP code gets executed
    bcoz u had not called that php file in ur code

  2. Sumit

    Yes…i got it..Thanks for reply and code

  3. Roshan

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

  4. Roshan

    welcome man…

  5. Sumit

    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??

  6. Roshan

    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.

  7. Is there any way to expand this to 3 drop downs? Country => Region => City

  8. will

    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.

  9. Me

    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.

  10. will

    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.

  11. cool
    thanks man

  12. maba

    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?

  13. 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..

  14. maba

    Thanks Roshan, I got it working after fixing it how you said. Your scripts is great.

  15. Thanks maba…

  16. Greg Cocks

    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!

  17. please check the case of “innerHTML” it should be like this “innerHTML”. I think it will help good luck..

  18. mc

    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!

  19. mc

    i saw that my code was not properly shown in my previous post… to repeat, this is my code for the dropdown:

    Select Destination

  20. 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

  21. Roshan thank you very much for this script. Finally I got it working (I had some small errors on my side 😀 )

  22. Tonya

    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.

  23. 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:

  24. 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

  25. 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?

  26. well..the same problem of DOM…I think you need to form tag outside of div

    form
    div
    div
    form

    hope this helps….

  27. Hello I m not able to get the value of ajax select list when submit the form. can anyone help.

  28. hey gourav..if you go through the few previous comments you have the solution out there…its same problem with the format of DOM…

  29. 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 : *

       

  30. <!–

    Name *

    Address *

    Description *

    Coutry *

    Select Country
    Afghanistan
    Albania
    Algeria
    American Samoa
    Andorra
    Angola
    Anguilla

    State *

    Select State

    City *

    Select City

    Member Image : *

       

    –>

  31. Thanks, I solved the probs with ur help. Thanks again.

  32. Welcome gourav

  33. It works mate !!!

    Cheers
    Ranjeet

  34. Regarding the problems with the submit if the second droplist has no value. Here is my fix:

    Instead of:

    You should use:
    <option value=””>

  35. Tangier

    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!

  36. if you don’t see values in the post then you can see the comments and answer of this post

  37. thaks roshanbh.com

  38. Tangier

    This works in IE but not Firefox.

    DO you know why this may be? Thanx!

  39. 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

  40. I am surprised by this tutorial.
    Thank you very much is the minimun for this good.

  41. oh really George……nice to hear that…and I would like to surprise people more..

  42. thankssssssss

  43. Yang

    Thanks a looooot.

  44. 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,

  45. 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

  46. 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’]).

  47. thanks you…

  48. very very cool thankss

  49. bilgilendirme için çok çok te?ekkürler

  50. Ray

    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.

Comments are closed.