Table of Contents for

MySQL in a Nutshell, 2nd Edition

This function captures the results of a statement and returns a reference to the data. The results are a complex data structure: an array of references, with each reference to an array for each row of data retrieved. You can finish the statement handle after executing this method, since the results are stored in memory. Here is an example:

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

foreach my $book (@$books) {
   my ($title, $author) = @$book;
   print "$title by $author \n";
}
$sth->finish();

Notice that after fetchall_arrayref() is called, finish() is used before the data is parsed. Using foreach, first the array reference is dereferenced (i.e., @$books) and the reference to each array containing a row from the results is stored in a variable ($book). Then that array reference is deferenced (@$book) to parse the fields into variables for use.