Multiple Tiered Pulldown menu

ministryman

I am trying to figure out how to "cascade" or multi-tier a pulldown menu in php to access a sql database.

Ideally, after the user selects the first option, the next menu bar displays the options for the first choice, and after selected, the third menu bar displays the options for the second choice.

The resulting selection becomes the basis for the query accessing the SQL database for the results.

$sql = " SELECT * FROM users WHERE (age > $agemin) AND (age < $agemax) AND localID = $SELECTION;

There are three tables at work here:

local: localid, HQ, regionalID

regional: regionalID, HQ, globalID

global: globalid, HQ

There are 20 global IDs (1-20)

Each global ID has X number of regionalIDs associated with it (and they are alpha)

Each RegionalID has 3-6 localIDs associated with it (and they are alpha)

So, the user would select the GlobalID, which would display the Regional IDs for that Global ID, and after selecting the Regional ID, the Local IDs for that Regional ID would be displayed.

I started by making the global table values an array (it's static)

<?php
$global = array("1st","2nd", "3rd", "4th", "5th", "6th", "7th", "8th", "9th", "10th", "11th", "12th", "13th", "14th", "15th", "16th", "17th", "18th", "19th", "20th");
$5th = array ("KanNeb","SouthCal","California","NWMiss","Missou","Colorado","PacNW");
$SouthCal = array("LA","LAPas","LASDLV");

echo "<FORM METHOD=POST ACTION = results.php>";
echo "<SELECT NAME=column_name >";
echo"<OPTION VALUE=\"/\">Pick a District</OPTION>";
foreach($global as $district)
{
echo "<OPTION VALUE=\"$global\">$global</OPTION>";
}

// * BRAIN OVERLOAD BRAIN OVERLOAD **

echo "</SELECT></FORM>";
?>

**I was thinking about just making this a two tier model, but for Global ID 5, there would be 42 choices (kinda unwieldy on a screen IMHO).

If this is a newbie question forgive me; My brain hurts from searching fruitlessly on the web trying to figure it out.

Thank you.

rincewind456

To do it without having a page refresh then you need to look at AJAX. Google for millions of links.

ministryman

rincewind456 wrote:
To do it without having a page refresh then you need to look at AJAX. Google for millions of links.

So...let's say for sake of speed (I dont have the time to learn AJAx, even though it looks like what I am going to need down the line), I don't care about page refreshes (GASP)....

Can it be done with PHP only, and if so, how?

rincewind456

Yes it can be done just with php, but it could be better done with php and JavaScript.

I've knocked this together to give you some idea of how to do it

<?php

$global = array("1st","2nd", "3rd", "4th", "5th", "6th", "7th", "8th", "9th", "10th", "11th", "12th", "13th", "14th", "15th", "16th", "17th", "18th", "19th", "20th");
$_5th = array ("KanNeb","SouthCal","California","NWMiss","Missou","Colorado","PacNW");
$SouthCal = array("LA","LAPas","LASDLV");
?>


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title></title>

<script language='javascript' type='text/javascript'>
function setOptions(chosen) {
	var selbox = document.myform.opttwo;

selbox.options.length = 0;
if (chosen == " ") {
	selbox.options[selbox.options.length] = new Option('Select from list','');
	selbox.options[selbox.options.length] = new Option('Please select one of the options above first',' ');

}
if (chosen == "1") {
	selbox.options[selbox.options.length] = new Option('Select from list','');
	selbox.options[selbox.options.length] = new Option('first choice - option one','1');
	selbox.options[selbox.options.length] = new Option('first choice - option two','2');
}
if (chosen == "2") {
	selbox.options[selbox.options.length] = new Option('Select from list','');
	selbox.options[selbox.options.length] = new Option('second choice - option one','3');
	selbox.options[selbox.options.length] = new Option('second choice - option two','4');
}
if (chosen == "3") {
	selbox.options[selbox.options.length] = new Option('Select from list','');
	selbox.options[selbox.options.length] = new Option('third choice - option one','5');
	selbox.options[selbox.options.length] = new Option('third choice - option two','6');
}
if (chosen == "4") {
	selbox.options[selbox.options.length] = new Option('Select from list','');
	selbox.options[selbox.options.length] = new Option('third choice - option one','7');
	selbox.options[selbox.options.length] = new Option('third choice - option two','8');
}
if (chosen == "5") {
	<?php
	print "selbox.options[selbox.options.length] = new Option('Select from list','');";
	foreach ($_5th as $value){
		print "selbox.options[selbox.options.length] = new Option('".$value."','".$value."');\n";

	}

	?>
}
}
	function setOptions1(chosen) {
		var selbox = document.myform.optthree;

	selbox.options.length = 0;
	if (chosen == " ") {
		selbox.options[selbox.options.length] = new Option('Please select one of the options above first',' ');

	}
	if (chosen == "1") {
		selbox.options[selbox.options.length] = new Option('Select from list','');
		selbox.options[selbox.options.length] = new Option('first choice - option one','1');
		selbox.options[selbox.options.length] = new Option('first choice - option two','2');
	}
	if (chosen == "<?php echo $_5th[1]?>") {
		selbox.options[selbox.options.length] = new Option('Select from list','');
		<?php 
		foreach ($SouthCal as $value){
		print "selbox.options[selbox.options.length] = new Option('".$value."','".$value."');\n";
		}?>
	}
	if (chosen == "3") {
		selbox.options[selbox.options.length] = new Option('Select from list','');
		selbox.options[selbox.options.length] = new Option('third choice - option one','5');
		selbox.options[selbox.options.length] = new Option('third choice - option two','6');
	}
	if (chosen == "4") {
		selbox.options[selbox.options.length] = new Option('Select from list','');
		selbox.options[selbox.options.length] = new Option('third choice - option one','7');
		selbox.options[selbox.options.length] = new Option('third choice - option two','8');
	}
	if (chosen == "5") {
		selbox.options[selbox.options.length] = new Option('Select from list','');
		selbox.options[selbox.options.length] = new Option('third choice - option one','9');
		selbox.options[selbox.options.length] = new Option('third choice - option two','10');
	}
	if (chosen == "6") {
		selbox.options[selbox.options.length] = new Option('Select from list','');
		selbox.options[selbox.options.length] = new Option('third choice - option one','11');
		selbox.options[selbox.options.length] = new Option('third choice - option two','12');
	}
}

</script>

</head>

<body>
<form name="myform" action="" method="post">
<div align="center">
<select name="optone" size="1"
onchange="setOptions(document.myform.optone.options[document.myform.optone.selectedIndex].value);">
<option value=" " selected="selected"> </option>
<option value="1">1st</option>
<option value="2">2nd</option>
<option value="3">3rd</option>
<option value="4">4th</option>
<option value="5">5th</option>
</select>
<br />
<br />
<select name="opttwo" size="1"onchange="setOptions1(document.myform.opttwo.options[document.myform.opttwo.selectedIndex].value);">
<option value=" " selected="selected">Please select one of the options above first</option>
</select>
<br />
<br />
<select name="optthree" size="1">
<option value=" " selected="selected">Please select one of the options above first</option>
</select>
<br />
<br />
<input type="button" name="go" value="Value Selected"
onclick="alert(document.myform.optthree.options[document.myform.opttwo.selectedIndex].value);">
</div>
</form>


</body>
</html>

It's fairly easy to dynamically build your variables from a sql query and putting all your global, regional and locals into variables will make building the drop downs easier.

Hope this helps.

ministryman

I think I see where your logic is headed.

I will plug in all of the array elements and give it a spin.

Thank you in advance!

ministryman

I have a few problems.

One, the "screen pops" don't always work, or provide the wrong item chosen.

2nd -> WA -> WACAP No Screen Pop
5th -> KanNeb -> OmWi -> Screen Pop OmWi
5th -> KanNeb -> KCD -> Screen Pop OmWi

Two, moving forward, I will learn how to pull the array data from the table(s). Keying in the data on 6 regions was a bear...

The file is 30K, so I have zipped it for uploading