jeffa has asked for the wisdom of the Perl Monks concerning the following question:

I am on a quest for a module that I do not believe exists. So, I am planning on writing my very first CPAN module.

My requirements:

  1. Accept a query to a database, as well the database connection information, or optionally an existing DBI::db object.
  2. Accept a SQL SELECT statement and execute it
  3. Return a string containing the results wrapped in HTML table tags: <TABLE> <TR> <TH> and <TD>
  4. Allow user to specify attributes for each of the table tags, such as WIDTH, CLASS, ALIGNMENT etc.
  5. Allow user to format each <TH> and <TD> elements to allow for FONT tags. This would definately be a 'global' thing - set this value and all elements are effected
  6. Print column names headers (that is, <TH> tags)
  7. Calculate totals (and possibly subtotals) on specified columns, the columns should be accessible by their names, maybe by their number in the list of columns - user's responsibility to make sure the rows would be numeric
  8. Specify colors for the headers, totals, and body rows. Additionally allow for alternating body row colors. Two is plenty, but a configurable frequency might be nice.

Now, I have been searching CPAN, and have come across some modules that meet _some_ of my requirements:
Data::Table

Good as long as you don't mind not being able to tweak the <TD> tags without extra coding on your behalf. Plus, it has a lot of extra baggage for accessing CSV files, pulling out sub tables, sorting, etc.
Data::ShowTable I have no idea how to use this thing, I get the feeling the author doesn't want anybody to know how to use it.
DBIx::XML_RDB

This is a great start, unfortunately, it only works for XML compliant browsers. Plus you have to create style sheets
HTML::Template (or any other templating system) I use this module on a few of my projects and I love it, but for these requirements it is too much overkill. The point is to not have any other necessary files, templates, or style sheets.
The biggest missing element is none of these modules handle subtotals (with the exception of the Tempating Modules of course). The best one is
DBIx::XML_RDB, which I plan to base mine from, but with the current limiations of XML, it's not the best wide-audience choice. I have looked at a number of other modules, mostly in the DBI and HTML categories, but all them carry too much extra baggage.

So, my questions:

  1. Has anybody seen a module that meets these requirements that I did not list?
  2. Would you personally find this useful?
  3. What is a good name for it? I was planning on DBIx::HTML_Table
  4. Any suggestions for a first time CPAN writer?
Thanks,
Jeff A

Replies are listed 'Best First'.
Re: SQL query to HTML table
by OeufMayo (Curate) on Apr 11, 2001 at 03:14 UTC

    jeffa, here are my 2 eurocents on this one:

    Return a string containing the results wrapped in HTML table tags: <TABLE> <TR> <TH> and <TD>

    Make also sure that you use the other table elements described in the W3C Table Model such as the COLGROUP, COL, CAPTION, THEAD, TBODY and TFOOT elements (and their proper required attributes). Your users will be thankful to you when they will fire up large queries and/or try to parse the table afterward. The other advantage is to be able to provide the table as a stream and see it displayed as the informations come.

    Allow user to specify attributes for each of the table tags, such as WIDTH, CLASS, ALIGNMENT etc. Allow user to format each <TH> and <TD> elements to allow for FONT tags. This would definately be a 'global' thing - set this value and all elements are effected

    The FONT tag is deprecated (but not deprecated) since three years now. The easiest and smartest way to handle the diplaying style of your table is to use stylesheets. Most decent browser deals fairly well with them as long as you don't go down into the intricacies of the DOM. Providing a default overidable stylesheet might be a good thing.

    Calculate totals (and possibly subtotals) on specified columns, the columns should be accessible by their names, maybe by their number in the list of columns - user's responsibility to make sure the rows would be numeric

    Just to be sure, you will leave the calculations to the database and just create the result row in your table, won't you?

    Now, to answer your questions, apart from DBIx::XML_RDB (which I discovered by your tutorial) I haven't yet found such module though I recall having coded several times not so long ago such functions (HTML tables from SQL queries, alternating colors, subtotal, etc...). I'm pretty sure that if this module would be available, I'll have more spare time to spend in the CB.

    As for the name, I'll defintely go for DBIx::XHTML_Table, since HTML is dead (or should be, really). Again, most browsers won't see any difference, but the people who will to work seriously with the tables generated will thank you all the more (and you can parse XHTML with either HTML::Parser or XML::Parser).

    Update: I haven't looked at it, but there's a module called DBIx::HTMLView which looks a lot like what you're trying to do.

    <kbd>--
    my $OeufMayo = new PerlMonger::Paris({http => 'paris.mongueurs.net'});</kbd>
[reply]

Back to Seekers of Perl Wisdom