Re: Building an array of hashes from database

[dbwiz]

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 ^

For more ways of dealing with hashes, see DBI Recipes in our Tutorials.

[iburrell]

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 => {} });