Databases Reference
In-Depth Information
printf ("\nTotal:%d\n", $count);
$sth->finish();
$dbh->disconnect();
Save this script as
select.pl
and run it as:
$
./select.pl
You should get a display similar to this:
ID: Name:
--- ------------------------
1 New Order
2 Nick Cave & The Bad Seeds
3 Miles Davis
4 The Rolling Stones
5 The Stone Roses
6 Kylie Minogue
Total:6
With
fetchrow_hashref( )
, the example obtains a hash for each row. There are other
ways to access the query results. The
fetchrow_array( )
function returns an array,
where you access elements by index rather than by key. The elements in the array are
in the order returned by the
SELECT
statement. In our example, the
SELECT * FROM
artist
statement returns the
artist
table fields in the order that they appear in the
database; you can find this by running the query from the MySQL monitor or looking
at the table description (here it has been truncated to fit the page):
mysql>
DESCRIBE artist;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| artist_id | smallint(5) | NO | PRI | 0 | |
| artist_name | char(128) | YES | | | |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.22 sec)
If we wanted to print the artist name before the artist ID, we would access array element
1
first for the artist name and then array element
0
for the artist ID:
while(my @val=$sth->fetchrow_array())
{
printf ("%-128s %-5s\n", $val[1], $val[0]);
$count++;
}
We can also return a reference to the results array, rather than the array itself, using
the
fetchrow_arrayref( )
function. Here, we find another application of the arrow op-
erator; the elements in the array can be accessed through the array reference:
while(my $ref=$sth->fetchrow_arrayref())
{