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

Posted on December 13, 2007
Filed Under ajax, 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.

The cisco certification gives great importance to the networking administrators so that they may get expertise to install and configure routing and switching system efficiently. The microsoft training is very important for the individuals to become skillful IT professionals. The Microsoft certified solution developer stands for mcsd is most priceless certification exams which is very career-oriented for advanced developers. Cisco Certified Design Associate, ccda is very helpful for providing the great apprenticeship for designing the networks of cisco. The Microsoft certified professionals, mcp is very significant certification for the individuals who want to get start their career in the domain of information technology. The comptia certification provides professional proficiency for the deployment of linux-based clients. For the database managers and administrator, mcdba certification exams are very vital to improve their professional skills.

Popularity: 68% [?]

If you like this post then please subscribe to my full RSS feed . You can also subscribe by email and have new posts sent directly to your inbox.And, You can also follow me on twitter at http://twitter.com/roshanbh.

» Populate triple drop down list from database using Ajax and PHP
» Ajax login validation system in PHP using jQuery
» Slider Using PHP, Ajax And Javascript
» 7 Useful functions to tighten the security in PHP

Comments

44 Responses to “Change dropdown list (options) values from database with ajax and php”

Sumit on December 27th, 2007 4:46 am

one thing that i did not understand is when PHP code gets executed
bcoz u had not called that php file in ur code
Sumit on December 27th, 2007 5:41 am

Yes…i got it..Thanks for reply and code
Roshan on December 27th, 2007 5:50 am

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..
Roshan on December 27th, 2007 5:54 am

welcome man…
Sumit on December 27th, 2007 9:36 am

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??
Roshan on December 27th, 2007 2:44 pm

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.
Kaganwine on January 8th, 2008 5:18 pm

Is there any way to expand this to 3 drop downs? Country => Region => City
will on January 16th, 2008 11:22 pm

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.
Me on January 17th, 2008 12:52 am

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.
will on January 17th, 2008 6:49 pm

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.
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 on January 24th, 2008 10:17 am

[...] 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. First create the following tables of country city and [...]
senol on February 20th, 2008 7:53 am

cool
thanks man
maba on February 22nd, 2008 12:20 am

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?
Roshan on February 22nd, 2008 8:30 am

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..
maba on February 22nd, 2008 2:47 pm

Thanks Roshan, I got it working after fixing it how you said. Your scripts is great.
Roshan on February 22nd, 2008 3:04 pm

Thanks maba…
Greg Cocks on March 4th, 2008 10:42 pm

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!
Roshan on March 6th, 2008 2:35 pm

please check the case of “innerHTML” it should be like this “innerHTML”. I think it will help good luck..
mc on March 8th, 2008 11:18 pm

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!
mc on March 8th, 2008 11:21 pm

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

Select Destination
Roshan on March 9th, 2008 4:23 am

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
fluidmoon on March 14th, 2008 3:04 pm

Roshan thank you very much for this script. Finally I got it working (I had some small errors on my side )
Tonya on March 17th, 2008 5:14 pm

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.
Chris on March 20th, 2008 10:29 pm

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:
Chris on March 20th, 2008 10:29 pm

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
Kjetil on April 6th, 2008 9:59 pm

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?
Roshan on April 7th, 2008 4:43 am

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

form
div
div
form

hope this helps….
gourav on April 19th, 2008 3:30 pm

Hello I m not able to get the value of ajax select list when submit the form. can anyone help.
Roshan on April 19th, 2008 4:43 pm

hey gourav..if you go through the few previous comments you have the solution out there…its same problem with the format of DOM…
gourav on April 19th, 2008 6:23 pm

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 : *
gourav on April 19th, 2008 6:28 pm

<!–

Name *

Address *

Description *

Coutry *

Select Country
Afghanistan
Albania
Algeria
American Samoa
Andorra
Angola
Anguilla

State *

Select State

City *

Select City

Member Image : *

–>
gourav on April 19th, 2008 6:31 pm

Thanks, I solved the probs with ur help. Thanks again.
Roshan on April 20th, 2008 5:23 am

Welcome gourav
Ranjeet on April 23rd, 2008 1:54 am

It works mate !!!

Cheers
Ranjeet
Alex on May 5th, 2008 1:25 pm

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

Instead of:

You should use:
<option value=”">
Tangier on May 26th, 2008 1:57 am

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!
Roshan on May 26th, 2008 4:08 am

if you don’t see values in the post then you can see the comments and answer of this post
nakliyat on May 27th, 2008 11:50 pm

thaks roshanbh.com
Tangier on May 28th, 2008 2:45 am

This works in IE but not Firefox.

DO you know why this may be? Thanx!
Roshan on May 28th, 2008 3:00 pm

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
George on June 2nd, 2008 11:48 am

I am surprised by this tutorial.
Thank you very much is the minimun for this good.
Roshan on June 2nd, 2008 12:02 pm

oh really George……nice to hear that…and I would like to surprise people more..
çiçek on July 4th, 2008 4:31 pm

thankssssssss
Yang on July 8th, 2008 6:51 pm

Thanks a looooot.