Create a perl hash from a db select

Question

Having some trouble understanding how to create a Perl hash from a DB select statement.

$sth=$dbh->prepare(qq{select authorid,titleid,title,pubyear from books});
$sth->execute()  or die DBI->errstr;
while(@records=$sth->fetchrow_array()) {
    %Books = (%Books,AuthorID=> $records[0]);
    %Books = (%Books,TitleID=> $records[1]);
    %Books = (%Books,Title=> $records[2]);
    %Books = (%Books,PubYear=> $records[3]);
    print qq{$records[0]\n}
    print qq{\t$records[1]\n};
    print qq{\t$records[2]\n};
    print qq{\t$records[3]\n};
}
$sth->finish();
while(($key,$value) = each(%Books)) {
    print qq{$key --> $value\n};
}

The print statements work in the first while loop, but I only get the last result in the second key,value loop.

What am I doing wrong here. I'm sure it's something simple. Many thanks.

Answer 1

In the shown code an assignment of a record to a hash with the same keys overwrites the previous one, row after row, and the last one remains. Instead, they should be accumulated in a suitable data structure.

Since there are a fair number of rows (351 we are told) one option is a top-level array, with hashrefs for each book

my @all_books;

while (my @records = $sth->fetchrow_array()) {
    my %book;
    @book{qw(AuthorID TitleID Title PubYear)} = @records;
    push @all_books, \%book;
}

Now we have an array of books, each indexed by the four parameters. This uses a hash slice to assign multiple key-value pairs to a hash.

Another option is a top-level hash with keys for the four book-related parameters, each having for a value an arrayref with entries from all records

my %books;

while (my @records = $sth->fetchrow_array()) {
    push @{$books{AuthorID}}, $records[0];
    push @{$books{TitleID}}, $records[1];
    ...
}

Now one can go through authors/titles/etc, and readily recover the other parameters for each.

Adding some checks is always a good idea when reading from a database.

Answer 2

OP needs better specify the question and do some reading on DBI module.

DBI module has a call for fetchall_hashref perhaps OP could put it to some use.