Using Perl with MySQL - Learning MySQL - page 544

printf ("%-5s %-128s\n", $ref->[0], $ref->[1]);
$count++;
}
Both fetchrow_array( ) and fetchrow_arrayref( ) are faster than using
fetchrow_hashref( ) , but are more prone to problems in mixing up column index
numbers. They may also have problems if you alter the order of fields—for example,
with an ALTER TABLE statement. We recommend that you use fetchrow_hashref( ) for
all but the most time-sensitive applications.
Perl is case-sensitive, so accessing the fetched results using incorrect capitalization
won't work. In our example, we're interested in the artist_id and artist_name fields
of the music.artist table. If we try to access the results using, say, uppercase names:
printf ("%-5s %-128s\n", $ref->{ARTIST_ID}, $ref->{ARTIST_NAME});
blanks will be printed where we expect the field values to be, because ref-
>{ARTIST_ID} is not the same as ref->{artist_id} . If in doubt, try out a SHOW CREATE
TABLE table_name ; or DESCRIBE table_name statement from the MySQL monitor to see
exactly how to capitalize field names.
Alternatively, you can use all uppercase or all lowercase field names and ask the
fetchrow_hashref( ) function to force all the names to uppercase or lowercase when it
retrieves them:
while(my $ref=$sth->fetchrow_hashref("NAME_uc"))
{
printf ("%-5s %-128s\n", $ref->{ARTIST_ID}, $ref->{ARTIST_NAME});
$count++;
}
If you use "NAME_uc" , your Perl code should use all uppercase labels; you should use all
lowercase labels if you force everything to lowercase with "NAME_lc" .
Before we end our discussion on accessing result sets, let's look at a couple of high-
level ways of performing all the prepare and execute operations together. The
selectall_arrayref( ) function returns a two-dimensional array containing the query
results. For example, a query to list the contents of the music.artist table might return
the following array:
+---+---------------------------+
| 0 | 1 |
+---+---------------------------+
| 0 | New Order |
| 1 | Nick Cave & The Bad Seeds |
| 2 | Miles Davis |
| 3 | The Rolling Stones |
| 4 | The Stone Roses |
| 5 | Kylie Minogue |
+---+---------------------------+
Each row of the array is itself an array containing the result fields. The rows and columns
can be addressed with an index starting from 0 .