Writing MySQL-Based Programs - MySQL - page 70

fetchrow_hashref() returns a reference to a hash structure, or undef when there are
no more rows:

while ( my $ref = $sth -> fetchrow_hashref ())
{
print "id: $ref->{id}, name: $ref->{name}, cats: $ref->{cats}\n" ;
}
To access the elements of the hash, use the names of the columns selected by the state‐
ment ( $ref->{id} , $ref->{name} , and so forth). fetchrow_hashref() is particularly
useful for SELECT * statements because you can access elements of rows without knowing
anything about the order in which columns are returned. You need know only their
names. On the other hand, it's more expensive to set up a hash than an array, so fet
chrow_hashref() is slower than fetchrow_array() or fetchrow_arrayref() . It's also
possible to “lose” row elements if they have the same name because column names must
be unique. Same-name columns are not uncommon for joins between tables. For sol‐
utions to this problem, see Recipe 14.10 .

In addition to the statement execution methods just described, DBI provides several
high-level retrieval methods that execute a statement and return the result set in a single
operation. All are database-handle methods that create and dispose of the statement
handle internally before returning the result set. The methods differ in the form in which
they return the result. Some return the entire result set, others return a single row or
column of the set, as summarized in the following table:

Method Return value
selectrow_array() First row of result set as an array
selectrow_arrayref() First row of result set as a reference to an array
selectrow_hashref() First row of result set as a reference to a hash
selectcol_arrayref() First column of result set as a reference to an array
selectall_arrayref() Entire result set as a reference to an array of array references
selectall_hashref()

Entire result set as a reference to a hash of hash references

Most of these methods return a reference. The exception is selectrow_array() , which
selects the first row of the result set and returns an array or a scalar, depending on how
you call it. In array context, selectrow_array() returns the entire row as an array (or
the empty list if no row was selected). This is useful for statements from which you
expect to obtain only a single row. The return value can be used to determine the result
set size. The column count is the number of elements in the array, and the row count is
1 or 0:

my @val = $dbh -> selectrow_array ( "SELECT name, birth, foods FROM profile
WHERE id = 3" );
my $ncols = @val ;
my $nrows = $ncols ? 1 : 0 ;