Mixed Media and Web Development.

Chained Select Boxes using PHP / MySQL / AJAX

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.

DEMO

2 and 3 Dropdowns available in the download.

tier

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.

DEMO

DOWNLOAD

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”

  1. Rob says:

    might have to setup some kind of onload. most dropdowns dont work well with page refresh’s let alone chained dropdowns.

  2. Patrick says:

    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

  3. tsiku says:

    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

  4. Rob says:

    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.

  5. vafa says:

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

  6. mahdi says:

    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 ?

  7. Radar says:

    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.

  8. Rob says:

    the 3 dropdown code is in the download

  9. Radar says:

    How can I implement this with the third select box selected value known? Thanks.

  10. rifqi says:

    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?

  11. Alice says:

    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_two
    FROM three_drops AS t1, three_drops AS t2
    WHERE t1.tier_one = t2.tier_one
    AND t2.id = \"$drop_var.\"")

  12. Alice says:

    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.

  13. Rob says:

    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.

  14. Alice says:

    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…

  15. Rob says:

    you can submit the data like any other form. Just send the variables to retrieve the data with post or get.

  16. Rob says:

    New version is available for download.

  17. Rob says:

    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.

  18. Alice says:

    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
    =(

  19. inv1221 says:

    Thanks

    Great script clean and elegant

    I am going to write and implement

    and Thank you again

  20. Iwan says:

    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

  21. Bernard says:

    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?

  22. Jaspreet says:

    is it possible to have a submit button which could have different links to each selection made…

  23. Rob says:

    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');

  24. Jim says:

    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

  25. Dan says:

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

  26. Dan says:

    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.

  27. Rob says:

    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?

  28. Dan says:

    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.

  29. Samuel says:

    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

  30. Roger says:

    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

  31. Roger says:

    Rob

    Many Tanks

    I got it working

    ;)

  32. Roger says:

    Hmm doesn’t seem to display it all

    link: http://www.179a.com/Roger.txt

  33. Roger says:

    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'].”;
    }

    ?>

  34. Roger says:

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

  35. Rob says:

    post the code you have and i can help

  36. brizal08 says:

    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

  37. Rob says:

    which part is not working, can you post what you have done.

  38. Roger says:

    Well I tried doing that , but I just can’t get it

  39. Rob says:

    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.

  40. Roger says:

    Yes your right, I have them in the same table..

    so how do it now

    Roger

  41. Rob says:

    you should have the album and artists in the same table

  42. Roger says:

    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…

  43. Rob says:

    @Bryan

    2 tables, one is just categories the other is categories and the text for the seconds dropdown. its just matching categories.

  44. Bryan says:

    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

  45. Bryan says:

    Rob,

    How did you organize your db? I can’t seem to figure out tying my db with the code you have provided.

  46. Bryan says:

    Very cool, Rob. After I posted the question, I found out it could be done with the visibility: hidden style.

    Thanks

  47. Rob says:

    @Bryan

    The sky is the limit on this one. What I have provided is a simple example. Ive added in the hide/show portion.

  48. Bryan says:

    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

  49. Jay says:

    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!

  50. Ryan says:

    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.

Leave a Reply