Dropdown Forms - Changing Options Dynamically

[Westyfield2]

@Mods: The stuff below is an example, I'm not setting up a mega music website!

So on a site I've got some dropdown forms. You select the options, hit go, and the Get action sticks the choices as querystring. The next page then looks at your querystring, uses it to build a SQL SELECT WHERE statement, and returns you the choices.

<form action="songs.php">

	Artist: 
	<select name="artist">
		<option selected value="">All</option>
		<option value="Queen">Queen</option>
		<option value="Pink Floyd">Pink Floyd</option>
	</select>

	Album: 
	<select name="album">
		<option selected value="">All</option>
		<option value="A Kind of Magic">A Kind of Magic</option>
		<option value="The Miracle">The Miracle</option>
		<option value="The Dark Side of the Moon">The Dark Side of the Moon</option>
		<option value="Wish You Were Here</option>
	</select>

	<input type="submit" value="Submit">
	
</form>

That's cool.

But actually, the options of artists and albums to select from are not hardcoded - the page goes off to the database doing a SELECT DISTINCT to know what artists and albums there are to chose and then creates the HTML.

<select name="artist">
	<option value="">All</option>
	<?php
		$result = mysql_query('SELECT DISTINCT artist FROM files LIMIT 0, 30 ');
		while ($row = mysql_fetch_assoc($result))
		{
			echo '<option ';
			if (($_REQUEST['artist'])==$row["artist"])
			{
				echo 'selected ';
			}
			echo 'value = "'.$row["artist"].'">'.$row["artist"]."</option>";
		}
	?>
</select>

Series: 
<select name="album">
	<option value="">All</option>
	<?php
		$result = mysql_query('SELECT DISTINCT album FROM files LIMIT 0, 30 ');
		while ($row = mysql_fetch_assoc($result))
		{
			if($row["album"]!="")
			{
				echo '<option ';
				if (($_REQUEST['album'])==$row["album"])
				{
					echo 'selected ';
				}
				echo 'value = "'.$row["album"].'">'.$row["album"]."</option>";
			}
		}
	?>
</select>

That's all working fine.

But, you can select "Queen" in the artist dropdown and "The Dark Side of the Moon" as the album... and then the user will get no results. Now it's their own stupid fault for not knowing whose albums are whose, but it's not that nice as a user experience.

So, I want to change the available options in the second (album) dropdown, based on what the user has selected in the first (artist) dropdown. Now I can figure out in my head how I might use jQuery/AJAX to change the available select options based on the first, but that would be hardcoding in what albums to show when "Pink Floyd" has been chosen as artist.

What I can't work out how I'm going to do it using the database to find out what the allowed combinations are .

Any ideas?

 

[Westyfield2]

Ta guys for the ideas, I'm also on the google hunt .

The sql for the album dropdown needs a WHERE clause.

ie WHERE artist=".$_POST["artist"]

Then the recordset would only include albums of the right artist.

Would that work? The user selects from the artist dropdown first, but we haven't actually submitted it yet. And the SQL for populating the dropdowns is running when the page loads.