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?

 

[Phantom Shadow]

My first instinct was that javascript/ajax would help - or you could always have one page to select the artist and then go to a second page where you select the album (could make the second page look the same as the first so all the user sees is another box appear with album options.

Have a look here: http://www.w3schools.com/php/php_ajax_database.asp Looks like you could have a separate php file to retrieve albums from the chosen artist and return that and use jquery to then update the dropdown box.

 

[inogen]

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.

 

[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.

 

[md85]

Make a GET request to that same (or another) page, return the data before exiting and append in the ajax callback. Been porting legacy, procedural PHP recently, things like this dotted all around it - painful By no means the best code in the world but it gets the job done.

You'll need to listen for some ajax GET variable and exit before any data loads, so do that at the top of the page.

<?php

    $isAjaxRequest = isset($_GET['ajax']);
  
    $artist = $_GET['artist']; // CLEAN THIS!

    if ($isAjaxRequest && !empty($artist)) {
        $albumOutput = "";

        $result = mysql_query("SELECT Album FROM files WHERE artist = $artist"); 

        while ($row = mysql_fetch_assoc($result)) { 
             if(!empty($row["album"])) { 
                $albumOutput .= '<option>' . $row["album"] . '</option>'; 
            } 
        }

        echo $albumOutput;
        exit;
    }

JQuery change event for the artist select.

$('[name=artist]').on('change', function () {
  $.get('this_page.php?artist=' + $(this).val() + '&ajax', function (data) {
     if (data) {
       $('[name=album]').html(data);
     }
  });
});

Most basic of solutions, plenty could (should) be done to clean it up -sanitize query variables (or PDO prepared statements), mysql_fetch_object just because object notation is cleaner than array notation, !empty instead of empty string comparison etc.