Table of Contents for

MySQL in a Nutshell, 2nd Edition

This function returns a reference to a hash of references to hashes, one for each row from the results of an SQL statement given. This method combines prepare(), execute(), and fetchall_hashref(). A unique key field must be given for the second argument. This will be used for the key of the main hash of rows from the results set. An optional third argument can specify any of the attributes allowed for a statement handle. If placeholders are used in the SQL statement, their values must be given as an array for the final argument.

Here is an example:

...
my $sql_stmnt = "SELECT rec_id, title, author
                 FROM books";
                 
my $books = $dbh->selectall_hashref($sql_stmnt, 'book_id');

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

Notice that the prepare() method isn’t called to prepare the SQL statement or to create a statement handle. This means that finish() doesn’t need to be called. However, instead of giving an SQL statement, you can give a statement handle. Since the result is a hash reference, it must be deferenced in order to extract the data (i.e., the %$books). Using the foreach and the keys Perl functions, each key of the hash is extracted (the hash reference for each row), which is then deferenced within the code block (%$book). From this, the values for the individual fields can be extracted by the object oriented method.