Limit Drop Down Menu Options Based on Option Selected in Other Dropdown

[lkinnyc]

I have what is probably a basic question, but am new to web development, so don't really know where to look for an answer. Any help is greatly appreciated as I have brick-walled on this one.

I have a database of events that are organized by grandparent-parent-event_name structure. So Grandparent might be "Baseball" Parent might be "New York Mets" and Event Name would be "Mets vs. Phillies".

If the following form, I let an admin choose one option from each level of categorization. I have two questions:

1. Is there a better way to do this than with three db queries? (By "better" I mean less taxing on db and with faster page loads)

2. Is there a way, without using Javascript, that when someone chooses "Baseball" from grandparent, the options for parent would be limited to baseball teams and exclude football teams, golf matches, etc.? Would I need a submit button by each drop down? Do I need a separate file?

<form action="create_any_page.php" method="post">				
	<!-- Grandparent Selector -->
	Grandparent Name: 				
	<?php $query_grandparent  = "SELECT DISTINCT grandparent FROM events ORDER BY grandparent asc";
		$result_grandparent = mysql_query($query_grandparent) or die(mysql_error());
		$options_grandparent = "";

	while ($row_grandparent = mysql_fetch_array($result_grandparent)) {
		$the_grandparent = $row_grandparent["grandparent"];
		$options_grandparent.="<OPTION VALUE=\"$the_grandparent\">".$the_grandparent."</option>";
		}
	?>
	<select name='grandparent'><option value=0>Choose<?=$options_grandparent ?></select><br />

	<!-- Parent Name Selector -->
	Parent Name: 						
		<?php $query_parent  = "SELECT DISTINCT parent FROM events ORDER BY parent asc";
		$result_parent = mysql_query($query_parent) or die(mysql_error());
		$options_parent = "";

		while ($row_parent = mysql_fetch_array($result_parent)) {
		$the_parent = $row_parent["parent"];
		$options_parent.="<OPTION VALUE=\"$the_parent\">".$the_parent."</option>";
		}
	?>	
	<select name='parent_name'><option value=0>Choose<?=$options_parent_name?></select><br />
				
	<!-- Event Name Selector -->
	Event Name: 	
	<?php $query_event_name  = "SELECT DISTINCT event_name FROM events ORDER BY event_name asc";
		$result_event_name = mysql_query($query_event_name) or die(mysql_error());
		$options_event_name = "";

		while ($row_event_name = mysql_fetch_array($result_event_name)) {
		$the_event_name = $row_event_name["event_name"];
		$options_event_name.="<OPTION VALUE=\"$the_event_name\">".$the_event_name."</option>";
		}
	?>
	<select name='event_name'><option value=0>Choose<?=$options_event_name?></select><br />
	<input type='submit' />
</form>

[Atli]

Hi.

#1. No, not really. In this case using three separate queries is most likely your best option, performance vise.
Any combination of them (that I see, anyways) would require you to duplicate data in the return set, which would undoubtedly slow the query down in comparison.

#2. The best way to achieve this sort "dependent-drop-down" behavior is to use JavaScript and AJAX. (It's simple, really)
(See this example. It uses AJAX to create a table, rather than a drop-down, but it's the same technique.)

Second best would be to use PHP; have each <select> submit the form to the current page (minor JavaScript needed there, tho nothing complex) and have PHP recreate the page based on the selection.

There is really no third option, unless you want to submit each option on it's own page, which I wouldn't really call an option.

Exclude these two (and a half?) options, you are left with simply filling all <select> boxes with all the options.

[lkinnyc]

Thanks Atli, I appreciate the response.

The AJAX is a bit over my head, but I am going to give it a shot because it actually might help solve another problem - hiding the parent dropdown when there isn't a parent.

I am having a bit of trouble translating the example you provided, but essentially it looks like it is trying to pass the option selected to itself through a url appendage, then insert that back into the next MySQL query, which sounds like what I am trying. to do. I'll give it a shot.

Thanks again.

[Atli]

Ok. That might have been a bad example. (How hard is it to indent properly?!)

Anyhow, here is a working example of how to fill a drop-down with AJAX, based on the selection of another drop-down.

The first <select> calls the AJAX function (getItems), which creates an AJAX object(XMLHttpObject), which we used to call a second PHP file, which returns data back to the AJAX object, which we then use to fill the second <select> box.

The index file with the boxes:

<?php
// Connect to mysql
$dbLink = new mysqli('localhost', 'usr', 'pwd', 'dbName');
if(mysqli_connect_errno()) {
    echo 'MySQL connection failed:', mysqli_connect_error();
}

// Fetch groups for the first drop-down.
$sql = "SELECT id, name FROM groups";
$result = $dbLink->query($sql) or die("Query failed: ". $dbLink->error);

$groups = array();
while($row = $result->fetch_assoc()) {
    $groups[$row['id']] = $row['name'];
}

$result->close();
$dbLink->close();
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
    <head>
        <title>AJAX DropDown Example</title>
        <script type="text/javascript" src="script.js"></script>
    </head>
    <form action="process.php" method="post">
        <!-- Group select -->
        <select id="Groups" id="Groups" onchange="getItems();">
            <option value="">-Select-</option>
            <?php
            foreach($groups as $_id => $_name) {
                echo "<option value=\"{$_id}\">{$_name}</option>";
            }
            ?>
        </select>

        <!-- Item select. Hidden, initially -->
        <select name="Items" id="Items" style="display: none;"></select>
    </form>
</html>

The JavaScript file loaded into the first file, containing our AJAX function.

/**
 * Creats an AJAX object.
 */
function createAjaxObject()
{
    var ajaxObject;
    if(XMLHttpRequest) {
        // Standard method
        ajaxObject = new XMLHttpRequest();
    }
    else {
        // IE proprietary craphola
        ajaxObject = new ActiveXObject("Microsoft.XMLHTTP");
    }
    return ajaxObject;
}

/**
 * Uses AJAX to fetch the data for, and create, the Items dropdown.
 */
function getItems()
{
    // Get the selected group
    var groupID = document.getElementById('Groups').value;

    // Get the Items select box
    var itemsSelect = document.getElementById('Items');

    // Validate the groupID
    if(isNaN(parseInt(groupID)))
    {
       // The value isn't a valid group ID
       // Hide the items select and stop the function
       itemsSelect.style.display = "none";
       return;
    }

    // Create a new AJAX object
    var ajax = createAjaxObject();

    // Set the code to be exected when the
    // AJAX call is complete.
    ajax.onreadystatechange = function()
    {
        // Make sure the AJAX call was successful
        if(ajax.readyState == 4) {
            // Clear the box of any old options
            itemsSelect.options.length = 0;

            // Add a -Select- option to the box
            var option = new Option("-Select-", "");
            itemsSelect.options.add(option);

            // Get the response text and split it into groups
            var response = ajax.responseText;
            var groups = response.split("\n");

            // Add each group to the items select box
            for(var i = 0; i < groups.length; i++)
            {
                // Split the group into the ID and Name, and validate them
                var parts = groups[i].split(",");
                if(parts.length == 2)
                {
                    // Create and add an option to the select
                    option = new Option(parts[1], parts[0]);
                    itemsSelect.options.add(option);
                }

            }

            // Make the select visible
            itemsSelect.style.display = "inline-block";
        }
        else {
            // Hide the select
            itemsSelect.style.display = "none";
        }
    }

    // Execute the AJAX call
    ajax.open("GET", "getItems.php?gid=" + groupID, true);
    ajax.send(null);
}

And the second PHP file, which gets the data for the second object.

<?php
// Fetch the ID, and validate it
($groupID = @$_GET['gid']) or $groupID = null;

if(!is_numeric($groupID) || $groupID <= 0) {
    // Validation failed. Exit without returning a valid result.
    die("No results");
}

// Connect to mysql
$dbLink = new mysqli('localhost', 'usr', 'pwd', 'test');
if(mysqli_connect_errno()) {
    echo 'MySQL connection failed:', mysqli_connect_error();
}

// Fetch items
$sql = "SELECT id, name FROM items WHERE groupID = $groupID";
$result = $dbLink->query($sql) or die("Query failed: ". $dbLink->error);

// Create a CSV-style output
while($row = $result->fetch_assoc()) {
    echo $row['id'], ",", $row['name'], "\n";
}

$result->close();
$dbLink->close();
?>

Hope that makes more sense :)

[Atli]

O, yea.
And I used these tables to test this out on:

CREATE TABLE groups (
    id Int Unsigned Not Null Auto_Increment Primary Key,
    name VarChar(255) Not Null
)ENGINE=InnoDB;
CREATE TABLE items (
    id Int Unsigned Not Null Auto_Increment Primary Key,
    name VarChar(255) Not Null,
    groupID Int Unsigned Not Null
        References groups(id)
)ENGINE=InnoDB;

INSERT INTO groups(name)
VALUES ('Cars'), ('Bikes'), ('Diet Sodas');

INSERT INTO items(groupID, name)
VALUES 
    (1, 'BMW'), (1, 'Porsha'), (1, 'Toyota'),
    (2, 'I don\'t know any bike types!'),
    (3, 'Coke Lite'), (3, 'Diet Pepsi'), (3, 'Raw sewage');