Database Reference
In-Depth Information
One way to produce such a list (in Perl) is to retrieve all the information using a single
query, marching through the result set, and beginning a new list item each time you
reach a new letter:
my
$sth
=
$dbh
->
prepare
(
"SELECT name FROM states ORDER BY name"
);
$sth
->
execute
();
my
@items
;
my
@names
;
my
$cur_ltr
=
""
;
while
(
my
(
$name
)
=
$sth
->
fetchrow_array
())
{
my
$ltr
=
uc
(
substr
(
$name
,
0
,
1
));
# initial letter of name
if
(
$cur_ltr
ne
$ltr
)
# beginning a new letter?
{
if
(
@names
)
# any stored-up names from previous letter?
{
# for each definition list item, the initial letter is
# the term, and the list of states is the definition
push
(
@items
,
dt
(
$cur_ltr
));
push
(
@items
,
dd
(
ul
(
li
(
\
@names
))));
}
@names
=
();
$cur_ltr
=
$ltr
;
}
push
(
@names
,
escapeHTML
(
$name
));
}
if
(
@names
)
# any remaining names from final letter?
{
push
(
@items
,
dt
(
$cur_ltr
));
push
(
@items
,
dd
(
ul
(
li
(
\
@names
))));
}
print
dl
(
@items
);
Another approach uses the same query but separates the data-collection and HTML-
generation phases:
# collect state names and associate each with the proper
# initial-letter list
my
$sth
=
$dbh
->
prepare
(
"SELECT name FROM states ORDER BY name"
);
$sth
->
execute
();
my
%ltr
;
while
(
my
(
$name
)
=
$sth
->
fetchrow_array
())
{
my
$ltr
=
uc
(
substr
(
$name
,
0
,
1
));
# initial letter of name
# initialize letter list to empty array if this is
# first state for it, then add state to array
$ltr
{
$ltr
}
=
[]
unless
exists
(
$ltr
{
$ltr
});
push
(
@
{
$ltr
{
$ltr
}},
$name
);
}
# generate the output lists
my
@items
;
