Database Rows into Perl Hash

vishi has asked for the wisdom of the Perl Monks concerning the following question:

Hi Gurus!

Here's what I'm trying to do : I have a database table that has rows for bugs against a few components in the system like so

COMP    BUGNO
------  -----
USRINT     1111
HWARE    1232
USRINT    1021
KERNEL    4384
DRIVER    4894
KERNEL    4398
USRINT    1233
[download]

Now, I want to convert this data into a Perl Hash where in the "key" is the faulty component and the "value" is a list of bugs against that faulty component.

Now I have used DBI to get the values and write them to a hash like so

our $stmt = qq/select component, bug_no from request where status like
+ 'OPEN'/;
our $sth = $dbh->prepare($stmt);
$sth->execute() or die DBI->errstr;
my %hash;
while( my( $comp, $bug_num ) = $sth->fetchrow_array() ) {
    $hash{ $comp } = $bug_num;
}
[download]

What's happening here is that data is being overwritten when a new bug comes against an existing component. I want this bug to be appended to the end of that list so that one print of this hash gives me a list of components and bugs that are open against that component like so:

USRINT     1111, 1021, 1233
HWARE    1232
KERNEL    4384, 4398
DRIVER    4894
[download]

I'm not able to get this done. Any help is appreciated!

Comment on Database Rows into Perl Hash Select or Download Code

Replies are listed 'Best First'.

Re: Database Rows into Perl Hash
by vishi (Beadle) on Mar 07, 2015 at 12:31 UTC

Never mind, I found how to do it. Just modified the while loop a bit to look like this:

while( my( $comp, $bug_num ) = $sth->fetchrow_array() ) 
{
    if (($hash {$comp} eq '') ||($hash {$comp} == '' ))
    {
        $hash{ $comp }.=$bug_num;
    }
    else
    {
        $hash{ $comp}.=",$bug_num";    
    }
}
[download]

Is there any other way to do it... specially as the saying goes >> TIMTOWTDI

[reply]
[d/l]

Re^2: Database Rows into Perl Hash

by Athanasius (Archbishop) on Mar 07, 2015 at 12:37 UTC

Hello vishi,

Is there any other way to do it...

Yes! The simplest way is to replace this line in the original:

$hash{ $comp } = $bug_num;
[download]

with this:

push @{ $hash{ $comp } }, $bug_num;
[download]

The value corresponding to each hash key is now a reference to an anonymous array containing the bug numbers as its elements.

Of course, this doesn’t eliminate duplicates. For that, you could use the uniq function (also called distinct) in List::MoreUtils.

Update: Added quote.

Hope that helps,

Athanasius <°(((>< contra mundum Iustus alius egestas vitae, eros Piratica,

[reply]
[d/l]
[select]

Re^2: Database Rows into Perl Hash

by choroba (Cardinal) on Mar 07, 2015 at 12:42 UTC

$hash {$comp} == ''
[download]

This seems weird. == compares numbers, so it's equivalent to

$hash{$comp} == 0
[download]

لսႽ† ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ

[reply]
[d/l]
[select]

Re^2: Database Rows into Perl Hash

by duelafn (Parson) on Mar 07, 2015 at 12:43 UTC

You could collect the bugs into actual arrays,

while( my( $comp, $bug_num ) = $sth->fetchrow_array() ) 
{
    push @{ $hash {$comp} }, $bug_num;
}
[download]

Whether this is better or wors than constructing a string, of course, depends on how you intend to use the data, though even for producing strings I would generally build the arrays (I hate writing code which builds strings / tests whether the string is non-empty) and join with commas at output time: print join ", ", @{ $hash {$comp} };

Good Day,
Dean

[reply]
[d/l]
[select]