Making your dropdown list look intelligent

Maing your dropdown list look (at least a little bit) intelligent

The point of this page is to demonstrate that it is possible to fill a drop down menu with without including selections that have already been made. This adds the personnal touch to the list of choices / options for a user. Not only does it make it simpler to select new infos by removing what is not wanted, it also saves the cgi failure page.. "Sorry you have already added this flavour for Paul"

Application uses:

Personal use: Keeping a list of your friends favourite ice cream flavours
Content Managment: Choosing other pages could appear below the page in a link list.
Commercial Sales: When choosing which products to put in the next weekly email you send me which I may want to consider purchasing - just so I do not get a list of the things I've already brought!

Examples

Paul and Sarah are my friends. I keep a note of the ice-cream flavours that they like - just in case they come and visit - and I need to know which flavours to stock up on.

Paul likes strawberries, apples, peaches and grapes. Sarah likes bananna and melon.

If I was adding new flavours for Paul, I could use a cgi form and fill it with ALL the flavours to choose from - but NO - the correct way is - create a cgi form and fill it with ONLY the flavours that have not been noted. We could even sort them too.

Bad Example : Add Flavour for Paul - all the flavours are listed:

Good Example: Add Flavour for Paul (because Paul likes strawberries, apples, peaches and grapes):

Removing bits from a list that have already been selected and populating the dropdown with whats left over.

Covers mysql select, mysql count, perl hash, hash element sort and generating html drop down.

Steps

Select and populate a hash (%hashgot) with paul's flavours from "selections" table
Select and populate a hash (%hashfree) Make a hash with the "flavour" table but ignore the values contained in %hashgot. My SQL version does not support the query langauge required to do this in SQL. The semantic query would be :"Select values from flavour which paul has not chosen already".

Generate a dropdown with values in %hashfree. Display the value - pass the cgi vaule as the key.

Table: flavours

id	flavour
1	strawberries
2	apples
3	pears
4	peaches
5	pineapple
6	orange
7	mango
8	grapes
9	banana
10	melon

Table: friends

id	name
1	Sarah
2	Paul

Table: selections

friend_id	flavour_id
2	1
2	2
1	7
2	4
2	8
1	8

Populate %hashgot

 
my (%hashgot);
 
my $sql =  "select p1.id, p1.flavour from flavours as p1,selections as p2, 
 friends as p3 where p1.id = p2.flavour_id and p2.friend_id = p3.id and p3.name ='Paul'";
 
$data=$dbh->prepare($sql);
$data->execute();
 
if(not $data) {
	print "
table does not exist";		
	$dbh->disconnect;	
	exit(0);
} else {
	
	while ($pointer = $data->fetchrow_hashref) {
	
		my ($id, $flavour );
 
			$id = $pointer->{'id'};			
			$flavour = $pointer->{'flavour '}
	
			${hashgot}{$id}{flavour} = $flavour;
 
		}
 
	}
 
}

Populate %hashfree

 
my (%hashfree);
my ($gothis) = 0;
 
$data=$dbh->prepare("select id, flavour from flavours");
$data->execute();
 
if(not $data) {
	print "
table does not exist";		
	$dbh->disconnect;	
	exit(0);
} else {
	while ($pointer = $data->fetchrow_hashref) {
	
		my ($id, $flavour);
		$gotthis = 0;
			
		$id = $pointer->{'id'};
		$flavour = $pointer->{'flavour'};
 
 
			foreach my $gotfile ( keys %hashgot){
 
				if (${hashgot}{$gotfile}{flavour} eq $id){
 
					$gotthis = 1;		
			
				}
 
			}
 
			if ($gotthis == 0){
				$hashfree{$id} = "$flavor";
			} 
 
		}
 
	}
 
	$dbh->disconnect();

Show Paul's flavours - contained in %hashgot

 
foreach my $id ( sort {$hashgot{$a}->{'flavour'} cmp $hashgot{$b}->{'flavour'} } keys %hashgot){
 
	print p( ${hashgot}{$id}->{'flavour'}, " with id ", $id);
 
}

Populate the dropdown with the remaining flavours - from %hashfree

print start_form();
 
print popup_menu(-name=>"new_flav",
	-labels=>\%hashfree, 
	-values=>[sort {$hashfree{$a} cmp $hashfree{$b} } keys %hashfree]);
 
print submit("send", "add new flavour");
 
print end_form();
 
 
}