DBI hashref to CGI optgroup?

[Greg G]

Here's what I'm trying to do:

my $sql1 = "select location, location_name from location_names order by
location";
$all_locs = $dbh->selectall_hashref($sql1, "LOCATION");

print "<tr><td>\n";
print "<select name='available_locations' multiple size=20
style='width:150'>\n";
print optgroup (-name=>"available_locations", -values => \%$all_locs );
print "</select></td>\n";


Should be pretty simple, right? Except that it doesn't work! The
option group gets generated with stuff like this:

<option value="LOC1">HASH(0xa381bbc)</option>

The LOC1 is, of course, correct. However, that HASH reference sure
isn't the location name. What am I doing wrong here? This seems like
it should be straightfoward, but I've tried a ton of variations, and
nothing seems to help.

Thanks.

-Greg G

 

[Paul Lalli]

Here's what I'm trying to do:

my $sql1 = "select location, location_name from location_names order by
location";
$all_locs = $dbh->selectall_hashref($sql1, "LOCATION");

print "<tr><td>\n";
print "<select name='available_locations' multiple size=20
style='width:150'>\n";
print optgroup (-name=>"available_locations", -values => \%$all_locs );
print "</select></td>\n";


Should be pretty simple, right? Except that it doesn't work! The
option group gets generated with stuff like this:

<option value="LOC1">HASH(0xa381bbc)</option>

The LOC1 is, of course, correct. However, that HASH reference sure
isn't the location name. What am I doing wrong here? This seems like
it should be straightfoward, but I've tried a ton of variations, and
nothing seems to help.

What do you think is inside of $all_locs? selectall_hashref returns a
hash of hashes. The key of the outer hash is each value of the
LOCATION column from your table. The value is a reference to a hash,
where the key is the column name and the value is the entry for that
column in that row.

The value of the -values parameter, however, is supposed to be a
single-level hash, of just string keys and string values.

selectall_hashref is not going to do what you want. You're going to
have to build your hash piece by piece, with each fetch from the
statement handle.
my %location_of;
my $sql1 = "select location, location_name from location_names order by
location";
my $sth = $dbh->prepare($sql);
$sth->execute();
while (my $row = $sth->fetchrow_hashref) {
$location_of{ $row->{location} } = $row->{location_name};
}

And then use \%location_of in your -values attribute.

Paul Lalli

 

[David Squire]

Here's what I'm trying to do:

my $sql1 = "select location, location_name from location_names order by
location";
$all_locs = $dbh->selectall_hashref($sql1, "LOCATION");

print "<tr><td>\n";
print "<select name='available_locations' multiple size=20
style='width:150'>\n";

I take it you're using CGI.pm and some DBI/DBD modules? You really need
to tell us the complete context of what you're doing.

print optgroup (-name=>"available_locations", -values => \%$all_locs );

$all_locs is already a hashref, so what is the \% for? You deference it
with % and then re-reference it with \ ???

print "</select></td>\n";


Should be pretty simple, right? Except that it doesn't work! The
option group gets generated with stuff like this:

<option value="LOC1">HASH(0xa381bbc)</option>

The LOC1 is, of course, correct. However, that HASH reference sure
isn't the location name. What am I doing wrong here? This seems like
it should be straightfoward, but I've tried a ton of variations, and
nothing seems to help.

According to the CGI.pm documentation, -values should be specified using
an arrayref, not a hashref. You need to ask DBI for an arrayref (or
array), not a hash.


DS

 

[David Squire]

According to the CGI.pm documentation, -values should be specified using
an arrayref, not a hashref. You need to ask DBI for an arrayref (or
array), not a hash.

Actually, further reading shows that you can in fact pass it a hashref,
in which case "the keys will be used for the menu values, and the values
will be used for the menu labels". Perhaps that is what you wanted.


DS

 

[Greg G]

What do you think is inside of $all_locs? selectall_hashref returns a
hash of hashes. The key of the outer hash is each value of the
LOCATION column from your table. The value is a reference to a hash,
where the key is the column name and the value is the entry for that
column in that row.

The value of the -values parameter, however, is supposed to be a
single-level hash, of just string keys and string values.

selectall_hashref is not going to do what you want. You're going to
have to build your hash piece by piece, with each fetch from the
statement handle.

Thanks. I think I may have also been distracted by CGI::FormBuilder...

In any case, I had to do a little more jiggery-pokerey, because the
resultant hash wasn't sorted (well, duh, right?), so, here's what I
finished up with (and does work for me):

my @loc_values = ();
my %loc_labels = ();
my $sql1 = "select location, location_name from location_names order by
location";
my $sth1 = $dbh->prepare($sql1);
$sth1->execute();
while (my $row = $sth1->fetchrow_hashref) {
push @loc_values, $row->{LOCATION};
$loc_labels{ $row->{LOCATION} } = "$row->{LOCATION}
($row->{LOCATION_NAME})";
}

print optgroup (-name=>"available_locations",
-values => \@loc_values,
-labels => \%loc_labels,
);


Thanks for the quick response.

-Greg G

 

[J. Gleixner]

Greg G wrote:
[...]

In any case, I had to do a little more jiggery-pokerey, because the
resultant hash wasn't sorted (well, duh, right?), so, here's what I
finished up with (and does work for me):

Could also use Tie::IxHash:

"...implements Perl hashes that preserve the order in which the hash
elements were added. ..."

my @loc_values = ();
my %loc_labels = ();

No need to initialize them.

my $sql1 = "select location, location_name from location_names order by
location";

Could use single quotes there.

my $sth1 = $dbh->prepare($sql1);
$sth1->execute();

while (my $row = $sth1->fetchrow_hashref) {
push @loc_values, $row->{LOCATION};
$loc_labels{ $row->{LOCATION} } = "$row->{LOCATION}
($row->{LOCATION_NAME})";

That's OK, provided your $row->{ LOCATION } is unique, of course.

}

Take a look at bind_columns(), which is faster and will make
that much easier to read, there won't be any need for "$row->".

print optgroup (-name=>"available_locations",

No need to use double quotes.

-values => \@loc_values,
-labels => \%loc_labels,
);

Also, you can do one call to print with multiple arguments:

print "<tr><td>\n",
"<select name='available_locations' ...\n",
optgroup (-name=>"available_locations", ...);

Could also use CGI's popup_menu(), instead of '<select.. '.