Database Reference
In-Depth Information
));
}
print
table
({
-
border
=>
"1"
},
@rows
);
The preceding table-generation examples hardwire the column headings into the code,
as well as knowledge about the number of columns. With a little effort, you can write a
more general function that takes a database handle and an arbitrary statement, executes
the statement, and returns its result as an HTML table. The function gets the column
labels from the statement metadata. To produce labels that differ from the table column
names, specify column aliases in the statement:
my
$tbl_str
=
make_table_from_query
(
$dbh
,
"SELECT
year AS Year, artist AS Artist, title AS Title
FROM cd
ORDER BY artist, year"
);
print
$tbl_str
;
Any kind of statement that returns a result set can be passed to this function. You could,
for example, use it to construct an HTML table from the result of a
CHECK
TABLE
state‐
ment, which returns a result set that indicates the outcome of the check operation.
What does the
make_table_from_query()
function look like? Here's a Perl implemen‐
tation:
sub
make_table_from_query
{
# db handle, query string, parameters to be bound to placeholders (if any)
my
(
$dbh
,
$stmt
,
@param
)
=
@_
;
my
$sth
=
$dbh
->
prepare
(
$stmt
);
$sth
->
execute
(
@param
);
my
@rows
;
# use column names for cells in the header row
push
(
@rows
,
Tr
(
th
([
map
{
escapeHTML
(
$_
) }
@
{
$sth
->
{
NAME
}} ])));
# fetch each data row
while
(
my
$row_ref
=
$sth
->
fetchrow_arrayref
())
{
# encode cell values, avoiding warnings for undefined
# values and using for empty cells
my
@val
=
map
{
defined
(
$_
)
&&
$_
!~
/^\s*$/
?
escapeHTML
(
$_
) :
" "
}
@
{
$row_ref
};
my
$row_str
;
for
(
my
$i
=
0
;
$i
<
@val
;
$i
++
)
{
# right-justify numeric columns
if
(
$sth
->
{
mysql_is_num
}
->
[
$i
])
{
$row_str
.=
td
({
-
align
=>
"right"
},
$val
[
$i
]);
