Using HTML::Table::FromDatabase

October 15, 2008 Uncategorized No comments

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:

<table border="1">
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";

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 );

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

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.

What’s up with SPF?

October 8, 2008 Uncategorized 4 comments

Writing about Pobox reminded me of Meng Wong’s SPF anti-spam system. I’ve heard nothing about it recently, and a check of openspf.org shows Latest News from June 2008 and April 2007.

What’s the health of the project? Are people using SPF? Is it effective? Should I bother updating (fixing, really) the SPF records I added years ago?

Flash in the pan, Internet scale

October 8, 2008 Uncategorized No comments

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.

Please enjoy the new design

October 4, 2008 Uncategorized 5 comments

I like the new design so much more. Less yellowy and blocky. Thanks to Kelli Forbes for doing the logo. If you need design stuff, email her at kjfwsd-hotmail-com. Thanks also to Chris Larkee here at Barcamp Milwaukee 3 for helping me override the Movable Type templates, and showing me slick stuff in Firebug that I didn’t know it could do.

Optimizing for the developer, not the user: PHP misses again

September 29, 2008 Uncategorized 15 comments

PHP refuses to let you report a bug in any version of PHP older than the absolute latest & greatest.

At work today, we discovered a bug with PDO, the PHP version of Perl’s DBI. Turns out if you pass in too many bind parameters, PDO segfaults. Here’s the simple program that Pete Krawczyk put together to exercise it.

$dbh = new PDO( 'pgsql:host=localhost;dbname=FOO', 'PASSWORD', '', Array(
) );
$array = Array();
for ($i = 1; $i < 10; $i++) {
$array[] = $i;
$sth = $dbh->prepare('SELECT 1 FROM USERS WHERE CUSTID = ? LIMIT 1');
while ( $sth->fetch( PDO::FETCH_NUM ) ) {
# do nothing
print "PDO lived OK with $i bind" . ($i == 1 ? '' : 's') . "n";

It’s repeatable for us on PHP 5.2.5. So after searching to see that nobody else had already reported it on bugs.php.net, I went to report it.

Alas, when I went to report the bug, I was not able to. My bug happened in 5.2.5, but according to the dialog, that wasn’t an option. No, I was left with “Earlier? Upgrade first!”

Latest and greatest only, please!

No, PHP, I am not going to upgrade my PHP installation in order to be blessed with the opportunity of telling you about a segfault in a version of software one minor revision older.

No, PHP, I am not going to spend an hour building and installing another monolithic PHP on some test server so that I might gain the privilege, the privilege I say!, of helping out your project.

What a backwards way to look at open source development! “You must be at least this tall in order to report bugs.” What a way to help scare away contributors.

Perhaps you should have a look at how Perl handles it, where we have a wide open ticketing system. There’s a tool called perlbug that ships with Perl to encourage responses. The perl5-porters might get some inappropriate bug reports, maybe in a module rather than core Perl, but those are easily closed. We don’t put up barriers to reporting. We know how to treat the outside world, because we welcome the feedback.

Get a clue, PHP people.

Announcing “The Working Geek”

September 17, 2008 Uncategorized 2 comments

I started a new blog a while ago called
The Working Geek, devoted to work
issues of interest to techies of all stripes who work for a living.
Topics have included
how to speak Manager,
personal networking and
closing the deal at a job interview.

I’m going to be posting much more as I approach completion of my book
Land the Tech Job You Love: Why Skill and Luck Are Not Enough to be published by
Pragmatic Bookshelf. Bits of the book will
probably make their way into blog posts, and I’ll be posting more about work and tech
articles that I see.

I hope to see you there. For convenience and so you don’t have to leave
the comfort of your newsreader, here’s the feed.

Great results for five Perl projects in Google Summer of Code 2008

August 30, 2008 Uncategorized 3 comments

By Eric Wilhelm, Perl coordinator for Google Summer of Code 2008

Google’s Summer of Code 2008 is wrapping up now and I’m very pleased with how well The Perl Foundation’s students and mentors have done. The five projects which survived the halfway point have all finished with great results.

Many thanks to all of the mentors and students as well as everyone in the community who helped or supported the process. Also, thanks to Google for putting on the program and to Richard Dice and Jim Brandt at TPF.

But the end is only the beginning. We should really get started on next year now. Perl needs to do a better job of attracting students, but I’ll have to address these issues in another post.

Most of the students did a great job of blogging their progress, which I think is an important part of Summer of Code for the rest of the community. If you have been following along with any of the student projects, please drop me a note or leave a comment. I would love to hear more opinions from outside of the active SoC participants. Also, please thank the mentors and students for their work. Of course, they “know” you appreciate their effort — but it really means something if you actually send them an e-mail or say thanks on irc.

For those just joining us, here is a run-down of the SoC projects and some links.

Flesh out the Perl 6 Test Suite
student: Adrian Kreher
mentor: Moritz Lenz
Blog | Code

student: Samuel Tyler
Herbert Breunung
Jos Boumans
Blog | Code | CPAN distribution

Native Call Interface Signatures and Stubs Generation for Parrot
student: Kevin Tew
mentor: Jerry Gay
Mail | Code | (older branch)

Incremental Tricolor Garbage Collector
student: Andrew Whitworth
mentor: chromatic
Blog | Code

student: Thierry Moisan
mentor: Jonathan Leto
Blog | Code | CPAN distribution

Eric Wilhelm is a software and systems consultant, leader of the
Portland Perl Mongers, and author of many CPAN modules.

The first year of Perlbuzz

August 4, 2008 Uncategorized 3 comments

Perlbuzz is a year old today.

I just paid my first renewal for perlbuzz.com. It took about three weeks from when I first registered the domain name one night while brainstorming with Skud about a new newsy sort of site. The first news story: Further reports from YAPC::Europe.

Now, Skud is off doing other things, most recently her wiki at geekfeminism.org. Perlbuzz’s output has slowed some as I try to finish up my book “Job Hunting For Geeks” (not that that’s the title we’ll use) for Pragmatic Bookshelf.

What hasn’t changed at all is my appreciation for every one of you who reads Perlbuzz, or subscribes to the RSS feeds. Keep reading, and I’ll keep writing.


Crazy ideas for book indexing

July 10, 2008 Uncategorized 3 comments

I’m looking through Apache access logs, trying to remember what HTTP code 301 is.
I have O’Reilly’s _HTTP: The Definitive Guide_. I look in the index, and I see that 301 is aggregated with
bq. 300-399 status codes, 61-64, 502
Why not spell out all the codes?
bq. 301 status code, 61, 502
302 status code, 61, 502
303 status code, 62, 502
For that matter, why not have the index include the answer I want?
bq. 301 status code: Moved Permanently, 61, 502
I could have the answer right there, without having to go any further.
Further, the book has more than 600 pages, so I could even be able to look on page 301 and find out. It could have a footnote on the bottom of page 301 saying “301: Moved Permanently. Turn to pages 61-64, 502 for more information.”
Alas, I think that Chris Shifflet’s purple HTTP book is half the pages, so that wouldn’t do there.

Perl Foundation funds five grants for summer 2008

June 2, 2008 Uncategorized No comments

Alberto Simões, grant coordinator for the Perl Foundation, announces:

It took a long time, but for this round we had a big number of excellent proposals. Unfortunately TPF is unable to fund all the proposed grants as they exceed the funds available for Grants. Thus, TPF GC ranked proposals accordingly with its relevance to the community, and the first few were funded.

These are the funded proposals:

As a side note, the last proposal was funded using Perl 6 funds, from Ian Hague donation, and thus, outside the GC budget.

Follow the proposals that, although accepted, will not be funded:

Unfortunately, there was a few proposals that did not convince the committee, and were rejected. Without any particular order: