Chained Select Boxes using PHP / MySQL / AJAX
UPDATE
Version 2 is now available. I’ve had a lot of people ask for this and now its ready. 2 and 3 tier chained dropdowns. 3 tier files included in the download.
Im currently working on a chained dropdown generator that will give you the ability to create them on the fly with unlimited dropdowns. As soon as that is done I will post it.
2 and 3 Dropdowns available in the download.
It is fairly simple and consists of only 3 files. An index, database connection and a function file. So lets start.
Make your database table and fields. you can use mine for now.
CREATE TABLE IF NOT EXISTS `two_drops` ( `id` int(11) NOT NULL auto_increment, `tier_one` varchar(255) NOT NULL, `tier_two` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM ; INSERT INTO `two_drops` (`id`, `tier_one`, `tier_two`) VALUES (1, 'Colors', 'Red'), (2, 'Colors', 'Blue'), (3, 'Colors', 'Green'), (4, 'Colors', 'Yellow'), (5, 'Colors', 'Black'), (6, 'Shapes', 'Square'), (7, 'Shapes', 'Circle'), (8, 'Shapes', 'Triangle'), (9, 'Shapes', 'Rectangle'), (10, 'Shapes', 'Oval');
Make your connection to your database. db.php
mysql_connect("localhost", "username", "password") or die(mysql_error());
mysql_select_db("database") or die(mysql_error());
Next we will add the jQuery, loading, results and main form in index.php
add your includes at the top of the page.
//include your database connection and function files.
include('db.php');
include('func.php');
and the rest of the page.
//jquery framework
$(document).ready(function() {
$('#wait_1').hide();
$('#drop_1').change(function(){
$('#wait_1').show();
$('#result_1').hide();
$.get("func.php", {
func: "drop_1",
drop_var: $('#drop_1').val()
}, function(response){
$('#result_1').fadeOut();
setTimeout("finishAjax('result_1', '"+escape(response)+"')", 400);
});
return false;
});
});
function finishAjax(id, response) {
$('#wait_1').hide();
$('#'+id).html(unescape(response));
$('#'+id).fadeIn();
}
Ok, that was easy enough. Now for the functions func.php
//**************************************
// Page load dropdown results //
//**************************************
function getTierOne()
{
$result = mysql_query("SELECT DISTINCT tier_one FROM two_drops")
or die(mysql_error());
while($tier = mysql_fetch_array( $result ))
{
echo ''.$tier['tier_one'].'';
}
}
//**************************************
// First selection results //
//**************************************
if($_GET['func'] == "drop_1" && isset($_GET['func'])) {
drop_1($_GET['drop_var']);
}
function drop_1($drop_var)
{
include_once('db.php');
$result = mysql_query("SELECT * FROM two_drops WHERE tier_one='$drop_var'")
or die(mysql_error());
echo '
';
echo '
';
}
And thats it. Download the files below.
Want more helpful and free scripts? Buy me a cup of coffee to keep me goin!
57 Responses to “Chained Select Boxes using PHP / MySQL / AJAX”
Leave a Reply
might have to setup some kind of onload. most dropdowns dont work well with page refresh’s let alone chained dropdowns.
Rob,
I have a question for you. I am using your two tiered chained select and it is awesome. The question I have is that when I post, I can get the first menu to start off where it left off, but I cannot get it to load the 2nd tier. can you help?
Example, I am using Industry > Skill. I select the Auto industrty, then ASE Certified from the skill. I post it. I retrieve the industry to start it back at Auto automatically, but the Skill menu does not appear. Can you assist?
Patrick
can you help me , the code dont support utf-8 characters. but i realy need it supports, have tryed to add meta tags and headers, nothing still works. tnx
sure you could, now do it your way with 3 or more dropdowns. the way i have it its just relationships to result names. its really easier how i have it.
nice tool
but, you can use normal db like this:
id parent name
1 0 Chevy
2 0 Ford
3 0 Honda
4 1 Camaro
5 1 Trailblazer
6 2 Mostang
7 2 F-350
8 3 Civic
9 3 Prelude
10 4 Black
11 4 White
12 5 Red
13 5 Blue
…
.
.
and use condition for checking a child:
function get_child($id){
q=”SELECT * FROM tablename WHERE parent=$id;”;
if ($q=null){it is last combo and show this $id row}
else{
extract()
foreach(…){
read each row and
if ($q=!null) {
call the get_child($)
}
else{show the stack for contracting a combo}
}
}
I want to chain selector but when I use it in my script it don’t work and stay in one select I use a multi template like header.tpl.php
main.tpl.php
and footer and I add script and ajax script to the head in header.tpl.php and in main.tpl.php add form and this not work and I use php mysql system
anybody can help me ?
Hi Rob,
Not sure if your most recent post was directed at mine or not.
What I’m wondering if there is a way to have the first and second tier ‘bubble up’ if the value of the third tier is known. Hope I’m explaining that well enough. Or, is the answer staring me in the face and I’m not recognizing it.
Thanks.
the 3 dropdown code is in the download
How can I implement this with the third select box selected value known? Thanks.
Wow this is excelent, but since i`m newbie i get confused when i want to make both 3 chain select & 2 chain select on the same HTML form.
could you help me?
done.
i catch selected box id, for the next select i use this id for select by tier_one column. in function getTierOne() last string:
echo ''.$tier['tier_one'].'';select in function drop_1:
$result = mysql_query("SELECT DISTINCT t1.tier_twoFROM three_drops AS t1, three_drops AS t2
WHERE t1.tier_one = t2.tier_one
AND t2.id = \"$drop_var.\"")
yes, but it’s not possible, data in tables are loaded from csv file. i’ll try to think about post/get method and about trigger to fill columns with new id.
you would have to put a ID relative to the column, like column 1 would have a id associated with the name..
like
1 – cars – 1 – ford
1 – cars – 2 – honda
1 – cars – 1 – ford
2 – colors – 10 – blue
2 – colors – 11 – red
and so on, if that makes sence.
Hi Rob! =)
Thank you for so quick response! I agree with you about russian encoding. Will try to reright… I thought it work with id, otherwise, what is the reason of this column? I have russian encoding, some body else will use very long strings or chinese…
you can submit the data like any other form. Just send the variables to retrieve the data with post or get.
New version is available for download.
Hi Alice, I viewed your page and the responce of the dropdown, it looks like the russian might be confusing jQuery with the encoding. I cant be 100% sure BUT thats my only hunch.
It is tryin to retrieve the data but this is what its sending.
%D0%A4%D0%BB%D0%B0%D0%BD%D0%B5%D1%86+%D0%BE%D0%B1%D0%B6%D0%B8%D0%BC%D0%BD%D0%BE%D0%B9+%D0%B4%D0%BB%D1%8F+%D1%87%D1%83%D0%B3%D1%83%D0%BD%D0%BD%D1%8B%D1%85+%D1%82%D1%80%D1%83%D0%B1
Instead of
Фланец обжимной для чугунных труб
maybe a ID for each would be better in this case, unless you can find a way to make jquery encode the russian. Also try playing with the html char set.
Hi, Rob. I tried to use your example, but second select don’t catch any data from db. where does first select send a constant to the drop_var variable? where and when does php fix it?
i have changed in source only db configuration and table name
=(
Thanks
Great script clean and elegant
I am going to write and implement
and Thank you again
Im really need ur code about 3 tier..
Its can very helpfull for me..
Can you attach me about that code..???
But all of the tier, the value get from mysql..
Thanx before
Say what if I’d like to use this to display a database table (i.e products catalog stuff) after the last selection is made, is there any work around to accomplish that?
is it possible to have a submit button which could have different links to each selection made…
i just use 3 columns on this
example
INSERT INTO `dialogue` (`id`, `category`, `text`) VALUES(1, 'Complements', 'The Palace is so beautiful'),
(2, 'Complements', 'I love being in Princess World'),
(3, 'Complements', 'You are so sweet'),
(4, 'Complements', 'I just wanted to tell you that you are my best friend'),
(5, 'Complements', 'I love your new princess picks!'),
(6, 'Complements', 'Live laugh and love'),
(7, 'Complements', 'I wish you the best'),
(8, 'Complements', 'Stopping by to say hi'),
(9, 'Complements', 'Thanks for adding me as your friend.'),
(10, 'Complements', 'I wanted to wish you and your family a great weekend'),
(11, 'Complements', 'I love your outfit'),
(12, 'Complements', 'Thank you so much!'),
(13, 'Complements', 'I love your song'),
(14, 'Complements', 'You rock!'),
(15, 'Complements', 'Best Wishes!'),
(16, 'Complements', 'My Palace rocks!'),
(17, 'Complements', 'Peace, Faith and Love'),
(18, 'Complements', 'Good job!!'),
(19, 'Complements', 'I like your hair'),
(20, 'Complements', 'You look so pretty'),
(21, 'Complements', 'It smells good'),
(22, 'Complements', 'You''re the best friend ever'),
(23, 'Complements', 'Your palace is awesome!!!'),
(24, 'Complements', 'Sending you a Best friend hug'),
(25, 'Complements', 'Giving you a high five'),
(26, 'Holidays', 'Happy Holidays'),
(27, 'Holidays', 'Merry Christmas'),
(28, 'Holidays', 'Happy Hanukah'),
(29, 'Holidays', 'Happy Mothers Day'),
(30, 'Holidays', 'Happy Fathers Day'),
(31, 'Holidays', 'Happy Summer'),
(32, 'Holidays', 'Happy Groundhogs Day'),
(33, 'Holidays', 'Happy Valentines Day'),
(34, 'Holidays', 'Congratulations'),
(35, 'Holidays', 'Happy Sunday'),
(36, 'Holidays', 'You are talented'),
(37, 'Holidays', 'Happy Thanksgiving'),
(38, 'Holidays', 'I wish you a very Happy Birthday'),
(39, 'Holidays', 'Happy birthday'),
(40, 'Holidays', 'It''s my Birthday Today'),
(41, 'Holidays', 'Happy 4th of July');
Is there anyway to see the mysql DB schema for the above example? Having trouble getting my head around what to replace and what not to replace to get this working properly with my dbase….
Cheers
Jim
Pretty much what I need to happen is that the number of selections wouldn’t matter; just that each child level selection would be populated with a query based on the parent(s) value(s).
It is currently six but may grow to seven. The tables are on an iSeries and the SQL queries are going to be quite complex so showing you the actual db isn’t possible. What I will have is Division>Market>Style>Color>Cup>Size.
Cup is optional depending on the style. What I need to happen is division selection shows only those (active markets) available and then market selection will show only those styles available and so on. I thought about creating a (getText.php) type page for each level.
7 drop downs is quite a bit, i have changed this drop down function to jquery and i need to update it. can you show me a sample of your database table?
I am wondering how Roger finally resolved his issue as I am having the same problem…I can’t seem to “feed” the second dropdown with data. I am try to get this to work for 7 dropdown levels.
Hi Rob,
Got the script working correctly, thanks a million…I’m wondering how to post the value of the second select to an Insert/Update form function? Is this possible?
Cheers
Samuel
Hi Rob,
So my next question is,
How do you send the result of the second as a search term to a search page..
Presuming this can be done
Rob
Many Tanks
I got it working
Hmm doesn’t seem to display it all
link: http://www.179a.com/Roger.txt
Hi Rob
here is my form
=====================================
Select a Category
<?php
$result = mysql_query(”SELECT DISTINCT artist FROM mp_id3_tags”)
or die(mysql_error());
while($cat = mysql_fetch_array( $result ))
{
echo ”.$cat['artist'].”;
}
?>
Hi Rob
here is my form
=====================================
Select a Category
<?php
$result = mysql_query(”SELECT DISTINCT artist FROM mp_id3_tags”)
or die(mysql_error());
while($cat = mysql_fetch_array( $result ))
{
echo ”.$cat['artist'].”;
}
?>
========================================
Here is my gettext.php
===========================================
post the code you have and i can help
hey bob i have a problem on database there’s something wrong regarding with the fields and tables
the Table requires me ‘updata.metro’ doesn’t exist
because my table here is “location” and my field is “Metro Manila” & “Province”
please help me ASAP
which part is not working, can you post what you have done.
Well I tried doing that , but I just can’t get it
Change everything called category to artist and everything called text to album.
in the form where you see this..
is where the php goes, under the form snippet. that populates the form.
Yes your right, I have them in the same table..
so how do it now
Roger
you should have the album and artists in the same table
WOW this is just what I was looking for….
I have a table with `artist` and one with `album` how would I change this to show Artist in first and Album in the second,
Really would appreciate any help that could be given.
Note: I spent 2 hrs trying to solve this myself, without any luck…
@Bryan
2 tables, one is just categories the other is categories and the text for the seconds dropdown. its just matching categories.
In regards to my last post, I’m having trouble populating the second select box once something was chosen from the first select box.
About the db, are there various tables? How are the select boxes linked to each other inside the db?
Regards,
Bryan
Rob,
How did you organize your db? I can’t seem to figure out tying my db with the code you have provided.
Very cool, Rob. After I posted the question, I found out it could be done with the visibility: hidden style.
Thanks
@Bryan
The sky is the limit on this one. What I have provided is a simple example. Ive added in the hide/show portion.
How many child-trees can there be off of the parent?
Is there a way to completely hide the select boxes that come after the first one? In the demo there are two select boxes but the second one has nothing in it. Would it be possible to make the select boxes appear as needed?
Regards,
Bryan
This came in really handy and after some tweaking works perfectly for what I needed to do. Thanks for making this available…it saved me loads of time!
Very cool, implemented this into a site I’m developing
Had to make a few changes as my categories and sub-categories are stored in seperate tables in the DB.
I’ve also set it to pre-select whatever category is currently being viewed, but having a little difficulty pre-selecting the sub-category. Gonna stick at it a while longer and will post up my code when working if anyone wants it.
Thanks for the initial code, has certainly helped.