MySQL in a Nutshell, 2nd Edition

Name

fetchall_hashref()

Synopsis

$sth->fetchall_hashref(key_column)

This method captures the result of an SQL statement and returns a reference to the data. The result is a complex data structure: it returns a reference to a hash using the name of the key column given as its key and the value of the key column given as its value. Each key column value is then used as the key to another hash with a reference to yet another hash for each. This final hash has the column names from the SQL statement as its keys and the values of each row of data retrieved as their respective hash values. The unraveling of such a hash reference may become clearer if you study the following code excerpt:

...
my $sql_stmnt = "SELECT book_id, title, author 
                 FROM books"; 
my $sth = $dbh->prepare($sql_stmnt);
$sth->execute();

my $books = $sth->fetchall_hashref('book_id');

$sth->finish();

foreach my $book(keys %$books) {
   my $book_id = $books->{$book}->{'book_id'};
   my $title = $books->{$book}->{'title'};
   my $author = $books->{$book}->{'author'};
   print "$title ($book_id) by $author\n";
}

Notice for the SQL statement we are able to select more than two columns—that’s because this is not a simple hash, but rather a hash of hashes (a key/value pairing is created ultimately from the column names and their respective values). Notice also with the fetchall_hashref() that the primary key column of book_id is given within quotes, as a string. Since this is a hash, a column with unique values is given. Looking at the foreach ...