Table of Contents for

MySQL in a Nutshell, 2nd Edition

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, we use keys to extract just the keys to the dereferenced hash reference. We don’t need the values (which are the hashes for each row of data from the results set) at this point: we’ll get to that value within the code block of the foreach statement. Each key is then stored in the variable $book. Using that key, we can extract the hashes that are referenced by an object oriented method: $hash_ref->{$key_col}->{'col_name'}. It might help a bit if I show you the preceding code but with the keys and values of the first hash and with more verbose results. We’ll have to use a while statement with the each function:

...
while( my ($book_key,$book_values) = each(%$books)) {
   my $book_id = $books->{$book_key}->{'book_id'};
   my $title = $books->{$book_key}->{'title'};
   my $author = $books->{$book_key}->{'author'};
   print "$books\->$book_key\->$book_values\->\n
          {book_id->'$book_id',title->'$title',author->'$author'}\n\n";
}

Here are two lines of the results of the program. You can see the two hashes mentioned earlier. After the first hash, notice the value of book_id is the key to the hash for the row of data. The book_id is also included in the final hash:

HASH(0x81e09e4)->1000->HASH(0x81e0b10)->
{book_id->'1000', title->'Mrs. Dalloway', author->'Virginia Woolf'}

HASH(0x81e09e4)->1001->HASH(0x81e0a20)->
{book_id->'1001', title->'The End of the Affair', author->'Graham Greene'}