Table of Contents for

MySQL in a Nutshell, 2nd Edition

This function returns a reference to an array, which is the results set of the SQL statement executed. For each row of the results, another reference to an array is returned for each row of data. An optional second argument can specify any of the attributes allowed for the statement handle. If placeholders are used in the SQL statement, their values may be given as an array for the final argument. This method combines prepare(), execute(), and fetchall_arrayref(). Here is an example showing how it might be dereferenced:

my $sql_stmnt = "SELECT title, author
                 FROM books WHERE book_id = ?";
my $books = $dbh->selectall_arrayref($sql_stmnt, undef, '1234');

foreach my $book (@$books) {
   my ($title, $author) = @$book;
   print "$title by $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 an array reference, it must be deferenced in order to extract the data (i.e., the @$books). Using the foreach Perl function, each element of the array is extracted (the array reference for each row), which is then deferenced within the code block (@$book). From this, the values for the individual fields can be parsed and saved to variables.