Chained Select - third menu using values from 1st and 2nd menu

PHP
1

Hi there,
First time poster, long time lurker…

I’m working on a page that queries my mysql database and does the following:

Select a sports league from the first dropdown.

Use that league to give the stadium name options to select from the second dropdown

The problem is the third dropdown.

What it DOES: Select the team name from stadium names, ignoring league (e.g. NBA/NHL teams that share an arena but not a league are both listed, even though NBA was selected on the first dropdown)

What I want it to do: Populate the third select list with options that meet both the first and 2nd qualifications.

Here’s the main chunk of php that does the magic:

<?php
//**************************************
//     Page load dropdown results     //
//**************************************
function getTierOne()
{
$sql = "SELECT DISTINCT league FROM data";
	$result = mysql_query($sql)
	or die(mysql_error());

	  while($tier = mysql_fetch_array( $result ))

		{
		   echo '<option value="'.$tier['league'].'">'.$tier['league'].'</option>';
		}

}

//**************************************
//     First selection results     //
//**************************************
if($_GET['func'] == "drop_1" && isset($_GET['func'])) {
   drop_1($_GET['drop_var']);
}

function drop_1($drop_var)
{
    include_once('db/db.php');

	$sql = "SELECT stadiumname FROM data WHERE league='$drop_var'";
	$result = mysql_query($sql)
	or die(mysql_error());
	
	echo '<select name="drop_2" id="drop_2">
	      <option value=" " disabled="disabled" selected="selected">Choose one</option>';

		   while($drop_2 = mysql_fetch_array( $result ))
			{
			  echo '<option value="'.$drop_2['stadiumname'].'">'.$drop_2['stadiumname'].'</option>';
			}
	
	echo '</select>';
	echo "<script type=\\"text/javascript\\">
$('#wait_2').hide();
	$('#drop_2').change(function(){
	  $('#wait_2').show();
	  $('#result_2').hide();
      $.get(\\"func.php\\", {
		func: \\"drop_2\\",
		drop_var: $('#drop_2').val()
      }, function(response){
        $('#result_2').fadeOut();
        setTimeout(\\"finishAjax_tier_three('result_2', '\\"+escape(response)+\\"')\\", 400);
      });
    	return false;
	});
</script>";
}


//**************************************
//     Second selection results     //
//**************************************
if($_GET['func'] == "drop_2" && isset($_GET['func'])) {
   drop_2($_GET['drop_var']);
}

function drop_2($drop_var)
{
    include_once('db/db.php');

	$result = mysql_query("SELECT hometeam FROM data WHERE stadiumname='$drop_var'")
	or die(mysql_error());
	
	echo '<select name="drop_3" id="drop_3">
	      <option value=" " disabled="disabled" selected="selected">Choose one</option>';

		   while($drop_3 = mysql_fetch_array( $result ))
			{
			  echo '<option value="'.$drop_3['hometeam'].'">'.$drop_3['hometeam'].'</option>';
			}
	
	echo '</select> ';
    echo '<input type="submit" name="submit" value="Submit" />';
}
?>

The url for the functioning process is available at allmysportsteamssuck.com/beta/test.php if that will help.

Thanks!

2

Anyone have any ideas? :slight_smile:

3

Hope this link helps you.
http://roshanbh.com.np/2008/01/populate-triple-drop-down-list-change-options-value-from-database-using-ajax-and-php.html

4

So… you know how to construct SQL queries.

You know how to pull both variables off the URL with $_GET and pass them to your function…

What exactly are you having trouble with? (Hint: You need a WHERE clause that has an AND in it.)

5

Sorry to necropost, but I figured out a workaround then the workaround ended up being terrible.

I understand that ultimately I need a SQL query that says “Select * from whatever WHERE team = $get_team and league = $get_league” but the problem is, I’m not sure which $GET value to … get. SQL is definitely my strength in this and $_GET variables are a bit new to me…

The problem I think, is that $_GET[‘drop_var’] is used repeatedly, so it “flushes” out the first dropdown selection.

I’m probably being thick, but what variable can I use to get the first dropdown selection?

6

I’ve tried a few things, such as moving everything to different tables, but ultimately I get stuck at the same point.

Anyone have any suggestions?