• 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).

    David Precious is a professional Perl developer, currently working for a UK web hosting company. He has released several modules on CPAN, and contributed to a number of other Open Source projects. He's also a keen motorcyclist, and has a fondness for beer.
  • Pittsburgh Perl Workshop 2008 is underway

    John Cappiello writes:

    So far I am enjoying the hallway track as always. Buglabs was semi interesting, but the business / product seems impractical, Rx talk by Ricardo Signes was both entertaining as always, and hopefully an informative piece to some, as it's quite an interesting product.

    There's only one track today, so talks are limited. I'll send more info as it comes along, and I have more conference photos to be uploaded to Flickr already. They're all tagged ppw2008.

    Any other reports from the field? Link to them in the comments!

  • Flash in the pan, Internet scale

    The lovely & talented Ricardo SIGNES on his employer:

    I work for Pobox. We provide identity management. For the most part, it's about email. You register an email address with us and we handle the mail for you. We send it to an IMAP store, or your current ISP, or some flash in the pan webmail provider like Google.
  • What's the state of Perl web frameworks?

    Joshua Hoblitt pounced on me in AIM this morning as soon as I opened my laptop.

    Joshua Hoblitt: Here's something to put on Perlbuzz.

    JH: WTF MVC framework is working this week?

    Andy Lester: Sounds like an editorial in the making?

    JH: Maypole is dead, Catalyst is um, well, I've never managed to finish a project with it.

    JH: The documentation is SHIIITTT.

    JH: And the book is one of the most crapped-on books I've ever seen on Amazon.

    JH: So Catalyst is a no go for me.

    JH: So what's left? Roll your own with Mason?

    AL: CGI::Application?

    JH: Ya, I've used it for small stuff.

    JH: The kind of stuff you put in one monster .pm file so it's trivial to install.

    JH: Hmm, there's MasonX::MiniMVC.

    JH: And this egg thing.

    AL: Can I post this chat as an article?

    JH: Please do.

    I've gone through a similar thought process recently. I've started looking at CGI::Application, but the work project where I was starting to use it has been derailed for a weeks.

    I welcome your ideas on the state of frameworks, either in comments below, or as a guest editorial.

  • Is learning Perl the hard way the easy way?

    Bruce Momjian, guru of PostgreSQL, has discovered the joys of Perl.

    I have converted two of my most complex shell scripts to Perl; as shell scripts, they were slow and hard to maintain. The rewritten Perl scripts are 200-400 lines long (about the same length the original shell scripts) and 15-25 times faster because of the improved algorithms possible in Perl and reduced subprocess creation.

    What was surprising to me was how he'd learned, via a book I'd never heard of before, Learning Perl The Hard Way. Has anyone in the Perl Buzz readership read it? Comments?