Auto-populating Select Boxes with AJAX and MYSQL
Chained Select Boxes Connected to a Database
I am currently working on feature for a website that requires browsing throught the database with select boxes. Ebay uses select boxes for categorization (see image below) and this is pretty much what I need. Naturally, I browsed the internet for some scripts that would provide such functionality. I wanted to use HTML with a little php for database communcation, and AJAX to make the calls to the server. I found a couple examples that did something similar to what I needed but did not use a database, did not have third box functinality, or did not work in all browsers. I encountered a known problem in some code that I found which uses innerHTML on SELECT tags and does not function in Internet Explorer (IE6 and IE7). Thus, I decided to write my own script that uses AJAX and MYSQL database, is damn simple to understand, and it works across browsers (well, I only tested IE6, IE7, and FireFox).
The goal of this script is to select a top level category in first select box and dynamically populate the sub category in next select box using the database. Next, the second select box needs to dynamically populate the sub-sub-category of the third select box. Finally, we need the output pushed into some div. These boxes need to clear after each new call and I also set it up to not clear the first option which is my title option. To clarify, first box has the first option as “Select Something One” which acts as the title of the first select box and this option never clears. I also needed the first select box to clear both the second select box and third select box so that everything works properly. Checkout the demo and click on letters M,N, and P for results as there is no data in the database for the other letters.
See the demo
This dynamic select box script works by first displaying some letter in the first select box which has an onchange call to the javascript function. The javascript uses AJAX to call the first php file and display the results in the second select box. The first php file connects to the database and pulls the relevant information according to the letter selected in the first box. The same logic continues for the third box and final div. So there is one index.html, three php files for each select box, and one small js file for the Ajax calls.
To install this script, download the file below and unzip it somewhere on your server. You need a mysql database and you need to create a database called “test” (or change the name of the database in the php files). I have my password unset and my username is “root” so if this is different for your installation, make the changes in the php files. In your database, you need to insert a table with some data to test the script, you can use the following to populate the database (its also commented in index.html):
Download It
I put this together pretty quick so if anyone has any recommendations on making this script more powerful or if there are any bugs in this script, please let me know in the comments. It needs a loading screen and I think its loading pretty slow, so if anyone knows how to make this faster, please let me know.
ANSWERS TO SOME QUESTIONS:
QUESTION: “I just wanted the first value that appears from the mysql query to appear in the second select box (of course after selecting something from the first). The problem is there is something in the script that always fills the second select box with an empty line. I can’t locate it.”
ANSWER: On line 73 and 74 of the index.html file you have prezelmt[o].text = prezar[o] and prezelmt[o].value = prezar[o] …change it to prezelmt[o-1].text = prezar[o] and prezelmt[o-1].value = prezar[o]… think about it, should make sense.
QUESTION: “Did anyone workout how to only show the last two select boxes? OR add a fourth select box?”
ANSWER: This should be simple, but I need more info. If you only want two select boxes what information do you want displayed in the first select box? (static info or dynamic info pulled from the database). As far as adding one more select box to have four select boxes, you need to modify the index.html file, add one more javascript function, and one more php file that pulls the next info from the database. Email me for specific info.
Popularity: 35%
If you enjoyed this post, please consider to leave a comment.
Comments
I just need a two level select and I want to post the id of the record to next page please help.
Geshan
Hi
Great work!
is there anyway where instead of having a third drop down, replace it for a text box, as the third drop down in my script currently will only ever have one option and so a drop down is not neccessary.
i am having difficulty in trying to implement this change.
any help would be great. thanks.
Hi Iwek, thanks for your reply.
what is your email address? posting all the code here maybe too big.
to be honest though the code is exactly the same as the download version on this site, all i have done is change the mysql queries to resemble my own database and tables.
the table is:
CREATE TABLE `mobiles` (
`mobile_id` int(10) NOT NULL auto_increment,
`manufacturer` varchar(45) NOT NULL default ”,
`model` varchar(45) NOT NULL default ”,
`handset_price` varchar(45) NOT NULL default ”,
PRIMARY KEY (`mobile_id`)
)
thanks
Thank you very much for this! One question, what would I need to change to get the current onChange=”getfirst(this);”
onChange=”getsecond(this);”
onChange=”getthird(this, document…
into a drop down instead of a table
I’ve other use of onChange=”cilcked();” but I do not know what I’d need to do to meld the two.
thanks again,
d
Hi All,
Did anyone workout how to only show the last two select boxes??
Really need it on a project I’m working on.
Thanks
How difficult would it be to add a fourth select box (dropdown)
basically
Family -> genus -> specie -> subspecie
Hi iwek,
I’m testing this locally but it only works in FF. In IE the second box sits as a line of text “Select Something Two” where as in FF it sits as a select box. In IE when I select from the first box I get a Javascript error of undefined is null or not an object?
Is it something you changed to make it work in FF? Also the SQL for the first query lacked a % in the LIKE?
Also is how would you get it to return 2 values to the second and third boxes so say you can select the primary_key and a textual representation for each record? (if your searching by numeric methods instead of text like in your demo)
Hi! I’ve tried your script but can’t seem to see where or how do I add values to options produced by this script? An email response would be greatly appreciated. Thanks alot and God bless you.
Hey,
I’d like to implement this as part of a project I’m working on and I need to have 4 boxes instead of the 3 and all of the values need to be populated from the DB. Needs to be:
DHB > Hospital > Department > Doctor
If you could email on how to do this?
Cheers
Hey,
A great script, does exactly the function I have been searching for!! I just have one question. I adapted the script (referencing different database tables etc) for my own purposes, it works fine on test, but when I uploaded it to my live site - I get the error “Cannot handle the AJAX call.” - when trying to retrieve the contents of the second list box. Wondering if this is a PHP compatibility issue (my own version is 5.0.41 and website version is 4.X)? Is this possible, I am a bit confused.
Cheers
Hello again,
I’ve tracked the problem down to a 406 Not Acceptable error on the XMLHttpRequest. Has anyone else encountered this error? Any ideas on how to get around this would be greatly appreciated.
Thanks in advance
Hi, great script!!
Changing prezelmt[o].text = prezar[o] and prezelmt[o].value = prezar[o] …change it to prezelmt[o-1].text = prezar[o] and prezelmt[o-1].value = prezar[o]
selects the first item in the second select menu. How do I get the the 3rd select menu based from the second one to get filled in? Unless I do the onChange for the 2nd box, the 3rd select doesn’t get updated. I want the 3rd select to be updated on page load.
I tried adding that onChange event to the body onLoad but it doesn’t seem to do anything.
Ideas?
Thanks!!
I’m also looking to populate the first listbox from the database, but adding the onLoad=”getfirst(this);” to the select tag doesn’t seem to do the trick. Any help would be much appreciated :)
This is really cool!
Is it easy to do this with Drop-down boxes instead of listboxes?
Please e-mail me any info! Thanks!!!!
These are actually drop-down boxes but I have added a size to the select. To make it look like a drop down box, just get rid of the size in the select element. It is currently set to 13.
if i change the script to work with my DB and i am not using city and state values like your DEMO, do i need to modify any of the javacode on the index.html page as well?
I would like to use this with coldfusion. I changed the .php files with .cfm files but it does not work. I do not know php but, as far as I understand it, the php file just writes lines on ’screen’ with a ‘|’ in front. Or is it doing something else? I can store the db reuslts in an array or a list if this is any help.
This is my first exposure to AJAx so an help would be much appreciated.
I have this setup so that after clicking on the first box, I want it to display the value and not the text of the item selected so that I get:
./alpha.php?alpha=2% instead of ./alpha.php?alpha=name%
After doing this, nothing happens when the option is selected, no errors or anything, it just doesn’t do anything, although I can see the result of changing the option does change the link mentioned above. So that part is happening.
How do I go from here, to get it to actually populate the second select box with both the “value” and “text” of the result of the query in the alpha.php file. And the same would apply for selecting from the 2nd box and populating the 3rd box.
thanks, this is much appreciated, I grew frustrated as you did in other examples lacking needed info. Feel free to email me if needed!
Hek, never did anything with ColdFusion so I am not sure where the problem exists. I’m sorry.
Bruce, it’s been a long time since I looked at this, but looking at firebug the ajax call is /alpha.php?alpha=M%. It gets the text because I only have text, there are no values. If you want to pull the value I would open up the html file and add values to the select dropdown, then when making ajax calls I would change
var first = dd1[idx].text to var first = dd1[idx].value
Sorry if this isn’t much of help…
I also get the “Cannot handle the AJAX call” error. Any ideas on how to fix this problem? I looked into the 406 error as mentioned by Rod back in Sept.07 but I cannot figure out a solution. Any help from someone? A full download would be nice.
Yeah same here , i get the “Cannot handle the AJAX call” error…so i switched the if condition to if ! just to see what it returns….it seems that var prezar = prezresponse.split(”|”); expect an echo from the php query page(alpha,beta,final)with “|”…it just prints everything after that (.$row[’state’];) no translation…although im not sure i fully understand the AJAX calls i understand there’s something wrong there…but any help would be apreciated..asap.
P.s By the time of the response ill probably have solved the problem..
@Y-Silver: Have you solved it yet?? I am getting no response to my emails from the code originator and I have not been able to make any headway with this script. Any help you can provide would be awesome. Bung me an email if you get a moment to see if we can work on this together. I can be reached at hector420@froni.us
Thank you for taking the time to post a comment.
Haven’t been able to get this script working. If anyone else has gotten it to work please contact me as I am still interested in this example. Thank you!
I’ve looked into this script with Hugo (HectorvonRichter) and it appears to be his server problem. I downloaded the script and tested it locally and did not run into any problems. If anyone notices any further issues, leave a comment. To test if your server is properly returning mysql calls, try to hit “alpha.php?alpha=N%” directly and see if you get any response.
I’ve been able to get the script working entirely from a database but have two problems.
First, I only need two lists for my use and can’t figure out how to drop the third one
Secondly, when I select something from the first list and then something from the second list and then go back and re-select something from the fist list, I get that “Cannot handle the AJAX call” error. I suspect it’s due, in part, to my ham-handed efforts to modify the script but would appreciate some ideas to fix it just the same.
Yes, Iwo is correct in that my server was causing the issue with the script not working properly. However, I have extra security kicked on and the script is causing the server to treat the second db call as a quasi-intrusion. running the ?alpha=N% code helped me to locate the issue as it throws a 503 “Server Temporarily Out of Service” error. It’s not that the server was malfunctioning, it’s that the AJAX requests were triggering this effect. I’ve used Xajax successfully as well as the W3C example for dynamic dropdown listboxes without throwing any error calls.
This script is great as it has no back-end framework. But it is not worth it if I have to permanently lower my security levels, albeit to ‘normal’, to make the script function as it should. If anyone has a solution to this problem please let me know. Here are some other examples that may be of use to others:
http://www.phpro.org/tutorials/Creating-Dropdowns-with-PHP-and-Xajax.html (Awesome script!)
http://www.w3schools.com/PHP/php_ajax_database.asp
(If anyone needs two or more dropdowns / listboxes using this example let me know)
http://www.meta-language.net/forms-examples.html?example=test_linked_select
(Manuels Lemos rocked on this one. Too complex for my minimalistic coding styles but awesome for those that can use it! I have isolated the code needed just for the chained listboxes if anyone is interested.)
Hi Man,
it’s an excelent work, well done
One thing it’s not working on Firefox any idea why?