Using HTML::Table::FromDatabase

By David Precious

A task I find myself doing reasonably often when programming is producing a HTML table based on the result of a database query.

This often ends up with the same kind of boring code being written again and again, which get tedious.

For example:

print <<TABLESTART;
<table border="1">
<tr><th>id</th><th>foo</th><th>bar</th></tr>
TABLESTART
 
my $sth = $dbh->prepare(
"select id, foo, bar from mytable where something = ... "
);
$sth->execute() or die "Failed to query";
 
while (my $row = $sth->fetchrow_hashref) {
print '<tr><td>';
print join '</td><td>', @$row{qw(id foo bar)};
print "</td></tr>n";
}
print "</table>n";
$sth->finish;

Not hard, but it does get tedious.

HTML::Table makes things better by taking out most of the HTML drudgery, but you still need to loop through adding rows to your table.

This is where my HTML::Table::FromDatabase comes in - it's a subclass of HTML::Table which accepts an executed DBI statement handle, and automatically produces the table for you.

For instance:

my $sth = $dbh->prepare(
"select id, foo, bar from mytable where something = ..."
);
$sth->execute() or die "Failed to query";
 
my $table = HTML::Table::FromDatabase->new( -sth => $sth );
$table->print;

Much easier, and HTML::Table::FromDatabase does all the tedious work.

Sometimes that won't be quite flexible enough though; you might have something you want to do to certain columns or values before outputting them.

That's where HTML::Table::FromDatabase's callbacks come in handy. For a basic example, let's say that one of the columns you're fetching contains URLs, and you want to wrap them in anchor tags to make them clickable links. Simply done with:

 my $table = HTML::Table::FromDatabase->new(
-sth => $sth,
-callbacks => [
{
column => 'url',
transform =>
sub { $_ = shift; qq[<a href="$_">$_</a>]; },
},
],
);

Another example - looking for all cells whose value is a number, and formatting them to two decimal places:

 my $table = HTML::Table::FromDatabase->new(
-sth => $sth,
-callbacks => [
{
value => qr/d+/,
transform => sub { return sprintf '%.2f', shift },
},
],
);

You can apply as many callbacks as you need.

As HTML::Table::FromDatabase is a subclass of HTML::Table, all of HTML::Table's options can still be used to control how the generated table appears, for example:

• -class => ‘classname' to give the table a specific class to help you apply CSS styling
• -border => 1 to apply borders, -padding => 3 to set cell padding
• -evenrowclass and -oddrowclass if you want to have different styling for even and odd rows (e.g. alternating row backgrounds).

The full list of options can be found in the HTML::Table documentation, I'm not going to duplicate it all here.

Currently, the row headings used in the generated table are taken from the column names in the query, but I plan to release a new version sometime soon which allows you to alias them, if you want to do so.

(The code samples in this post are intentionally kept relatively simple, omitting obvious things like connecting to the database first, error checking etc).