Yes, there is a better way to do this, but it doesn't have to do with not knowing your perl, but rather your DBI. You want fetchrow_hashref.

Just a couple of other points.

Your statement handle would benefit from placeholders, and forming a closure for the prepared handle would allow the prepare call to only be done once:

# after $dbh is obtained
{
    my $sth = $dbh->prepare
        "select
            hall_name,
            upload_date,
            uploaded_by,
            photo_id
        from hall_details
        where
            hall_id=?
            limit 1";

    sub details_from_id {
        my $id = shift;
        $sth->execute $id;
        $sth->fetchrow_hashref;
    }   # mod some error checking
}
[download]

The other point is that your returned hash is a level too deep. The caller already knows $hall_id - it was passed as an argument - and will just need to extract the lone value from your return. With that, you can just call details_from_id directly.

my @hall_ids = 1..20;
my %details;
@details{@hall_ids} = map { details_from_id $_ } @hall_ids;
[download]

After Compline,
Zaxo

Or couse, the limit assuming postgres or mysql ( or other non-Oracle type stuff )...

Untested and broken (see Abigail-IIs post below), but I think your while loop could be replaced by

1 while @hall_details{$hall_id}{
           qw[ 
                hall_id 
                hall_name 
                upload_date 
                uploaded_by 
                photo_id 
           ] } = $sth->fetchrow_array();
[download]

Update: I should never post untested code. It always bites me. Again, I think this would work.

my @fields;
@( hall_details{$hall_id} }{
   qw[ 
        hall_id 
        hall_name 
        upload_date 
        uploaded_by 
        photo_id 
   ] 
} = @fields while @fields = $sth->fetchrow_array();
[download]

which would save a lot of typing and to the offense of the "it don't matter how long it takes brigade", it might even be slightly more efficient:) Feel free to reformat that however suits your view of beauty.

That said, I have a sneaky suspicion that you can ask DBI to return the data as a hash to begin with, which would undoubtably be your best option.

That doesn't look right, even after fixing the syntax error. Since you call $sth -> fetchrow_array () until it returns an empty list, the last assignment will be the equivalent of:

@{$hall_details {$hall_id}} {qw [ ... ]} = ();
[download]

Effectively wiping out the content the OP was interested in.

Abigail

sub getHallDetails() {
  my ($hall_id, $dbh) = @_;

  my $sth=$dbh->prepare("select hall_name, upload_date, uploaded_by, p
+hoto_id from hall_details
                         where hall_id='$hall_id' limit 1;");
  $sth->execute();
  my $res_arr = $sth->fetchall_arrayref();
  $sth->finish; 
  my $hall_details={ $hall_id => $res_arr };  
  return $hall_details;
}
[download]

      
--------------------------------
SV* sv_bless(SV* sv, HV* stash);

# presume dbh hunky dory
my @hall_ids = ("slumber1", "slumber2", "slumber3");
my %hall_details;

my @constrings = map { 
    "select hall_name, upload_date, uploaded_by, photo_id " .
    "from hall_details where hall_id='$_' limit 1;" 
    } @hall_ids;

map {
    my $sth = $dbh->prepare($constring[$_]);
    $sth->execute();
    $halldetails{$hallid[$_]} = $sth->fetchall_arrayref();
    $sth->finish();
} (0 .. $#hallids);
[download]