I think this should work. I don't have a database set up to test it, though, so I'll need you to test it for me. :-)
Code:
sub build_select_field_from_related_db {
# --------------------------------------------------------
# Builds a SELECT field from the database.
# This subroutiine hard-codes the data needed for to enter the key field
# for the "one" side of a relational database into a field in the "many"
# side. Since it is hard-coded, it might be best to place this in the
# html.pl file for the "many" side.
# This also assumes that the field the two databases have in common has the
# same name in both.
my $value = @_;
my (@fields, %selectfields, @lines, $line, $output);
&switch_to_user; # Change this line to reflect the name of the "one" side of your database
open (DB, "<$db_file_name") or &cgierr("unable to open $db_file_name. Reason: $!");
if ($db_use_flock) { flock(DB, 1); }
LINE: while (<DB>) {
next if /^#/;
next if /^\s*$/;
$line = $_;
chomp ($line);
@fields = &split_decode ($line);
# I used a hash here to make alphabetizing easier. In this case, field 2 is the last name of a person
# and field one is the first name. When we print them out, all we have to do is sort the hash keys and
# everything will be in alphabetical order.
$selectfields{$fields[2]$fields[1]}[0] = $fields[$db_key_pos]; # This is the key value from the "one" side
$selectfields{$fields[2]$fields[1]}[1] = "$fields[1] $fields[2]"; # This is what will be seen in the select field so
# you don't have to memorize the number
}
close DB;
$output = qq|<SELECT NAME="$db_cols[$db_key_pos]"><OPTION>---|;
foreach $key (sort keys %selectfields) {
($selectfields{$key}[0] eq $value) ?
($output .= qq|<OPTION value="$selectfields{$key}[0]" SELECTED>$selectfields{$key}[1]|) :
($output .= qq|<OPTION value="$selectfields{$key}[0]">$selectfields{$key}[1]|);
}
$output .= qq|</SELECT>|;
&switch_to_item;
return $output;
}
To use it, in your html.pl file for items, just use
print &build_select_field_from_related_db($rec{'UserID'});
JPD
----------------------------------------------------
JPDeni's DBMan-ual How to
ask questions the smart way.