Building an array of hashes from database

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

Hi Monks,

I am trying to figure out why this code is not giving me the result I need.

I want to build an array of hashes from the result of a database query.

Here is my code. At the end of the fetch, it prints only the last record in the table, several times.

my $query = "SELECT name, ip, email from people";

my $sth = $dbh->prepare($query);

$sth->execute();
my @records =();
my %data =();

while (my @row = $sth->fetchrow_array()) {
    $data{name} = $row[0];
    $data{ip} = $row[1];
    $data{email} = $row[2];
    push @records, \%data;
}

for my $record (@records) {
    for (qw(name ip email)) {
        print "$_ => $record->{$_}\n"
    }
    print "\n"
}
[download]

Where is the error?

Thanks for any hint.

Comment on Building an array of hashes from database Download Code

Replies are listed 'Best First'.
Re: Building an array of hashes from database by dbwiz (Curate) on Sep 26, 2003 at 16:37 UTC
First, immediate solution: put `my %data =()` inside your loop and it should work. The reason is that you are pushing the same reference over and over. That's why you see always the same record. If you put the hash inside the block, instead, you make a fresh one at each loop. However, there are several better ways. The one I prefer is this: `my $sth = $dbh->prepare($query); $sth->execute(); my $records = $sth->fetchall_arrayref({}); # notice an empty hashref here ^` [download] For more ways of dealing with hashes, see DBI Recipes in our Tutorials.	[reply] [d/l] [select]
Re: Re: Building an array of hashes from database by iburrell (Chaplain) on Sep 26, 2003 at 20:16 UTC
DBI has a convienence function on the dbh that does the prepare, execute, and fetchall in one call. Getting an arrayref of hashrefs requires a special argument. `my $records = $dbh->selectall_arrayref($sql, { Slice => {} });` [download]	[reply] [d/l]
Re: Building an array of hashes from database by mpeppler (Vicar) on Sep 26, 2003 at 16:39 UTC
You are always pushing a reference to the same `%data` hash. The easiest way around this is to do: `push(@records, {%data});` [download] which creates a reference to a new hash each time around. Alternatively, put `my %data` inside the `while (fetchrow_array())` loop - this will also create a new hash each time through the loop. Michael	[reply] [d/l] [select]
Re: Building an array of hashes from database by snax (Hermit) on Sep 26, 2003 at 17:03 UTC
As noted above, the hash ref isn't quite right in your code. Try this: `while (my @row = $sth->fetchrow_array()) { push @records, {name => $row[0], ip => $row[1], email => $row[2]}; }` [download] You can drop the `my %data;` statement using this approach, too. This makes it clear (to my eye, of course) that a new (anonymous) hash ref is used each time around. Check out perlref for more stuff on anonymous data structures and references. Good stuff :)	[reply] [d/l]