Database Reference
In-Depth Information
that references column values by name, nonunique column names cause values to be‐
come inaccessible. Suppose that you fetch rows in a Perl DBI script like this:
while
(
my
$ref
=
$sth
->
fetchrow_hashref
())
{
...
process
row
hash
here
...
}
Fetching rows into the hash yields three hash elements (
name
,
title
,
price
); one of the
name
elements is lost. To solve this problem, supply aliases that make the column names
unique:
SELECT
artist
.
name
AS
painter
,
painting
.
title
,
states
.
name
AS
state
,
painting
.
price
FROM
artist
INNER
JOIN
painting
INNER
JOIN
states
ON
artist
.
a_id
=
painting
.
a_id
AND
painting
.
state
=
states
.
abbrev
;
Now fetching rows into a hash yields four hash elements (
painter
,
title
,
state
,
price
).
To address the problem without column renaming, fetch the row into something other
than a hash. For example, fetch the row into an array and refer to the columns by ordinal
position within the array:
while
(
my
@val
=
$sth
->
fetchrow_array
())
{
print
"painter: $val[0], title: $val[1], "
.
"state: $val[2], price: $val[3]\n"
;
}
