Database Record into a hash...

[Donavon]

I am trying to load a database into a HASH of HASH. For some reason
every instance of the HASH only contains the data from the last record
pulled?

So I have a 1000 copies of the same information with different HASH
keys.
Any suggestions??

....
....
....
# loads a record into a hash
while ($data = $sth->fetchrow_hashref) {

%record = %$data;
$key = $record{'ProjectNumber'};
$hash{$key} = \%record;

};

$sth -> finish;
$dbh -> disconnect
or warn "Disconnection failed: $DBI::errstr\n";

# prints out content of HASH
foreach $key1 (keys %hash) {
print 'Level 1: ' . $key1 . "<BR>";

foreach $key2 (keys %{$hash{$key1}}) {
print $key2 . '=>' . $hash{$key1}{$key2} . "<BR>";
};
};

 

[Tad McClellan]

I am trying to load a database into a HASH of HASH. For some reason
every instance of the HASH only contains the data from the last record
pulled?

Any suggestions??

use strict;

%record = %$data;

my %record = %$data;

 

[Chief Squawtendrawpet]

while ($data = $sth->fetchrow_hashref) {
%record = %$data;
$key = $record{'ProjectNumber'};
$hash{$key} = \%record;
};

Each time through the loop, the key-value pairs in %record may be
changing, but %record itself is still the same old hash to which all
of the refs in %hash keep pointing.

One way to solve your problem is to dispense with the intermediary
(%record) and work directly with the ref in $data.
$hash{ $data->{ProjectNumber} } = $data;

Another way to solve the problem is to take Tad's advice:
my %record = %$data;

This has the effect of defining a "fresh copy" of %hash each time
through the loop -- at least that's how I lamely understand it. Maybe
one of the experts around here could offer a better explanation,
becuase this behavior has always been a little bit mysterious to me.

Chief S.

 

[Thomas Kratz]

I am trying to load a database into a HASH of HASH. For some reason
every instance of the HASH only contains the data from the last record
pulled?

So I have a 1000 copies of the same information with different HASH
keys.
Any suggestions??

...
...
...
# loads a record into a hash
while ($data = $sth->fetchrow_hashref) {

while (my $data = $sth->fetchrow_hashref) {
# create a new hashref for each loop

%record = %$data;

delete the above line, you do not need a temporary hash.

$key = $record{'ProjectNumber'};
$hash{$key} = \%record;

$hash{$data->{'ProjectNumber'}} = $data;

};

$sth -> finish;

A more elegant alternative is:

my $hashref;
unless ( $hashref = $sth->fetchall_hashref('ProjectNumber') ) {
warn "couldn't fetch data, $DBI::errstr\n";
}
# let DBI do the loop and create the hash
# no need to call $sth->finish() now

$dbh -> disconnect
or warn "Disconnection failed: $DBI::errstr\n";

# prints out content of HASH
foreach $key1 (keys %hash) {

foreach $key1 (keys %$hashref) {

print 'Level 1: ' . $key1 . "<BR>";

foreach $key2 (keys %{$hash{$key1}}) {
print $key2 . '=>' . $hash{$key1}{$key2} . "<BR>";

foreach $key2 (keys %{$hashref->{$key1}}) {

};
};

(untested, more in 'perldoc DBI')

Thomas