Dynamically populate two dropdown menus

Question

I'm not (as I am certain you'll notice below) a programmer or coder. I write network documentation manuals.

That said, I do have some familiarity with HTML, php, css, and so forth. But what follows has stumped me utterly and completely:

I am attempting to build a site for a friend of mine who is in a bind, and though everything else is going fine, I do have to redirect the end-user to a webpage built specifically for their community.

What I need is a way for the end-user to click on a dropdown menu which lists (through a MySQL query) all of the states where records are available, which, upon their selection of an option within the first menu, a second menu is populated by (again by way of MySQL) the communities within that state.

Though I've scoured the internet for things that might be useful, I find that I am simply not well-equipped for the logic of coding. I did find, however, one site that shows a basic version of what I am trying to do (see here), but I can't seem to get it to work with MySQL. This is where I am so far:

script --

<script>
 function swapOptions(ArrayName) {
       var ExSelect = document.theForm.sites;
       var theArray = eval(ArrayName);
       setOptionText(ExSelect, theArray);
    }

 function setOptionText(theSelect, theArray) {
       for (loop = 0; loop < theSelect.options.length; loop++) {
           theSelect.options[loop].text = theArray[loop];
       }
    }
 </script>

and HTML --

 <form name="theForm">
   <select name="chooseCat" onchange="swapOptions(this.options[selectedIndex].text);">
        <option value="">Please select your state ...</option>'
                 <?php 
                    $query = mysql_query("SELECT DISTINCT state FROM sites ORDER BY state") 
                           or die(mysql_error());  
                    while ($result = mysql_fetch_assoc($query)) {   
                           $stateChoice = $result['state'];                                      
                             echo '<option value="';
                             echo "$stateChoice"; 
                             echo '">';
                             echo "$stateChoice";  
                             echo '</option>';
                             echo '<br />';                  
                     }
                  ?>
    </select> 
    <select name="sites" style="min-width: 100px;" onchange="location.href='reports.php?page=' + this.options[this.selectedIndex].value;">
                  <?php 
                     $query = mysql_query("SELECT * FROM sites") 
                            or die(mysql_error());  
                     while ($result = mysql_fetch_assoc($query)) {                                      
                            echo '<option value="';
                            echo '">';
                            echo $result['longname'];  
                            echo '</option>';
                            echo '<br />';                  
                      }
                   ?>
     </select>
 </form>

This returns two dropdowns. The first with the results of my query for the states available, listed alphabetically in the dropdown. The second has the long-names of all of the sites listed, but for all of the sites in the table, not just the ones within a certain state (the state just chosen).

In the end, dropdown 1 should be populated with all of the available states. Once the user selects their state, this should trigger the population of dropdown 2 with all of the communities within their chosen state. Once the choice of community is clicked in dropdown 2, it should redirect the user's browser to, for example, http://www.webpage.com/reports.php?page=communityNameGoesHere ...

Thanks so much for any advice you might have here :)

Why use 11 times echo when 2 times is enough? You may use concatenation. — Jocelyn
– Jocelyn, Commented Sep 10, 2012 at 20:09
It looks like you'll have to either make an ajax call when an option on dropdown 1 is selected, or preload separately all the possible community lists for all the states and show/hide them when an option on the first dropdown is selected. Or, well, reload the entire page each time an option is selected but you probably don't want to do that :) — Mahn
– Mahn, Commented Sep 10, 2012 at 20:16
Hi Jocelyn, thanks so much for the comment. As for the repetitive use of echo, you are absolutely right. I only have it like this here because it's a work in progress, once done I will certainly clean and tighten it all up. Thanks again :) — spyrule
– spyrule, Commented Sep 10, 2012 at 20:17
Hi Mahn, hmmmm... Ajax?! I'm looking into it, but that's a little over my head, I think. I think you're probably right about it being the best option though. I'm just not sure that I could do it. Thanks so much, I'll definitely be looking more into Ajax! — spyrule
– spyrule, Commented Sep 10, 2012 at 20:22
Replace all of the echos with echo "<option value=\"$stateChoice\">$stateChoice</option><br />";. — uınbɐɥs
– uınbɐɥs, Commented Sep 19, 2012 at 19:54

Mike Brant · Accepted Answer · 2012-09-11 14:05:06Z

1

You have a couple of routes you can go. You can use AJAX to grab the values for the sites list upon selection of the state. Or, you could simply use javascript to filter the values in the second list based on the selection of the state.

I might think the second would be a better option for you since it is more straight forward. So I will focus on that, but if you want an AJAX solution that can be discussed as well.

I would revise your script to make a single DB call. Since you are querying the information from one table, there is no reason to call the DB twice.

Simply use SELECT * FROM sites ORDER BY states ASC

You can then loop through the result sets and do something like this

$array = array();
while ($row = mysql_fetch_assoc($query)) {
    $array[$row['state']][] = $row;
}

Then build your first select and populate the options with:

foreach($array as $state => $not_used) {
    echo sprintf('<option value="%1$s">%1$s</option><br />', $state);
}

Now build the second select and populate the options with:

foreach($array as $state => $state_array) {
    foreach($state_array as $site) {
        echo sprintf('<option class="%1$s" value="%2$s">%3$s</option><br />', $state, $site['shortname'], $site['longname']);
    }
}

Note, I have added a HTML class property here. This makes a convenient handle to show only those items you want to show.

You give the sites select element an ID of sites, and the states selected element an id of states. Set following rules in your CSS:

#sites { display: none; }
#sites option { display: none; }

This will initially hide the sites menu and all the options within it.

I will now show some simple jQuery for hiding/showing the proper elements, and for making the redirection upon site selection. I mention jQuery as this makes what you are trying to do a snap.

$(document).ready(function () { // calls this function on document ready
    $('#states').change(function() { // binds onchange function to #states select
        var selected_state = $(this).val(); // gets current selected value of #states select
        $('#sites option').hide(); // hides all options in sites select
        $('.'+selected_state).show(); // show site options with class = selected_state
        $('#sites').show(); // show entire #sites select (only useful for display from initial hidden state)
    });
    $('#sites').change(function() { // binds onchange function to #sites select
        window.location = 'reports.php?page=' + $(this).val(); // perform redirect
    });
});

edited Sep 11, 2012 at 14:05

answered Sep 10, 2012 at 20:34

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

spyrule Over a year ago

So, I've got this working thanks to you, Mike. For whatever reason though, the sprintf() doesn't print anything; no worries though because I just changed them out for printf() and everything's gravy. The problem I can't seem to get past, however, is that the second dropdown select box is only giving me one of the communities in the list (always the last one) and not the whole list for the selected state. Any ideas?

Mike Brant Over a year ago

@user1661045 Glad to help. I simply forgot to put echo before the sprintf function calls. I have changed my answer for future use. You should accept this answer to help build your reputation and make others more likely to help you out with future questions.

spyrule Over a year ago

Mike, do you have any idea why I might only be getting one of the communities out of a list for the second dropdown? The echo sprintf() works great, by the way; thanks again!

Mike Brant Over a year ago

@user1661045 I am not sure I know what you mean. Are you saying that the "sites" dropdown only hase one item in it, or that only one item is displayed at a time using javascript?

spyrule Over a year ago

The sites dropdown only displays one option and not the full list.

|

Collectives™ on Stack Overflow

Dynamically populate two dropdown menus

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related