NAME

DBIx::XHTML_Table - Create XHTML tables from SQL queries

SYNOPSIS

  use DBIx::XHTML_Table;

  # database credentials - fill in the blanks
  my ($dsource,$user,$pass) = ();

  # create the object
  my $table = DBIx::XHTML_Table->new($dsource, $user, $pass) 
  			|| die "could not connect to database\n";

  # grab some data
  $table->exec_query("
	SELECT ARTIST,ALBUM,TITLE,YEAR,GENRE 
	FROM MP3.SONGS
	WHERE YEAR=? AND GENRE=? 
	ORDER BY ARTIST,YEAR,TITLE
  ",[$year,$genre]);    # bind vars for demonstration only

  # start tweaking the table
  $table->modify_tag('table',{
	  border      => 1,
	  cellspacing => 0,
  });

  # modify all <th> tags
  $table->modify_tag('th',{
	  bgcolor => 'black',
	  style   => 'Color: white;',
  });

  # modify only <td> tags for TITLE column
  $table->modify_tag('td',{
	  align   => 'right',     # although align is deprecated
	  bgcolor => '#ABACAB',
  }, 'title');

  # values that are array refs will be rotated horizontally
  $table->modify_tag('td',{
	  width   => 200,
	  align   => [qw(left right)],
	  bgcolor => [qw(blue red)],
  }, [qw(album year)]);

  # this rotates colors vertically down the columns
  $table->set_row_colors(
	["#D0D0D0", "#B0B0B0"],
	[qw(artist genre)],
  );

  # set the most general column as the group
  $table->set_group('artist');  # can also suppress duplicates

  # sum up the years column
  $table->calc_totals('year');

  # and if you have set a group . . .
  $table->calc_subtotals('year');

  # print out the complete table
  print $table->get_table;

DESCRIPTION

XHTML_Table will execute SQL queries and return the results (as a scalar 'string') wrapped in XHTML tags. Methods are provided for determining which tags to use and what their attributes will be. Tags such as <table>, <tr>, <th>, and <td> will be automatically generated, you just have to specify what attributes they will use.

This module was created to fill a need for a quick and easy way to create 'on the fly' XHTML tables from SQL queries for the purpose of 'quick and dirty' reporting. If you find yourself needing more power over the display of your report, you should look into templating methods such as HTML::Template or Template-Toolkit. Another viable substitution for this module is to use DBIx::XML_RDB and XSL stylesheets. However, some browsers are still not XML compliant, and XHTML_Table has the advantage of displaying at least something on browsers that are not XML or XHTML compliant. At the worst, only the XHTML tags will be ignored, and not the content of the report.

The user is highly recommended to become familiar with the rules and structure of the new XHTML tags used for tables. XHTML is pretty much the HTML4.0 specification, but with the rules of XML. A good, terse reference on HTML4.0 can be found at http://www.w3.org/TR/REC-html40/struct/tables.html

Included in this documention is a simple table that lists all supported tags, how they are created, and what method is used to set the attributes. See TAG REFERENCE (yes, i know that's spelled wrong).

Additionally, a simple TUTORIAL is included in this documentation toward the end, just before the third door, down the hall, past the chickens and through a small gutter (just keep scrolling down).

CONSTRUCTOR

style 1

$obj_ref = new DBIx::XHTML_Table($dsource,$usr,$passwd)

Construct a new XHTML_Table object by supplying the database credentials: datasource, user, password:

my $table = DBIx::XHTML_Table->new($dsource,$usr,$passwd) || die;

The constuctor will simply pass the arguments to the connect() method from DBI.pm - see DBI as well as the one for your corresponding DBI driver module - DBD::Oracle, DBD::Sysbase, DBD::mysql, etc. The explanation of $dsource lies therein.

style 2

$obj_ref = new DBIx::XHTML_Table($DBH)

The first style will result in the database handle being created and destroyed 'behind the scenes'. If you need to keep the database connection open, create one yourself and pass it to the constructor:

my $DBH   = DBI->connect($dsource,$usr,$passwd) || die;
my $table = DBIx::XHTML_Table->new($DBH);
  # do stuff
$DBH->disconnect;

style 3

$obj_ref = new DBIx::XHTML_Table($array_ref)

The final style allows you to bypass a database altogether if need be. Simply pass a similar structure as the one passed back from DBI's selectall_arrayref(), that is, a list of lists:

  my $ref = [
  	[ qw(Head1 Head2 Head3) ],
	[ qw(foo bar baz)       ],
	[ qw(one two three)     ],
	[ qw(un deux trois)     ]
  ];

  my $table = DBIx::XHTML_Table->new($ref);

The only catch is that the first row will be treated as the table heading - be sure and supply one, even you don't need it. You can always bypass the printing of the heading via get_table().

OBJECT METHODS

exec_query

$table->exec_query($sql,[$bind_vars])
=back

OBJECT METHODS

exec_query

$table->exec_query($sql,[$bind_vars])

Pass the query off to the database with hopes of data being returned. The first argument is scalar that contains the SQL code, the second argument can either be a scalar for one bind variable or an array reference for multiple bind vars:

  $table->exec_query("
      SELECT BAR,BAZ FROM FOO
	  WHERE BAR = ?
	  AND   BAZ = ?
  ",[$foo,$bar])    || die 'query failed';

Consult DBI for more details on bind vars.

After the query successfully exectutes, the results will be stored interally as a 2-D array. The XHTML table tags will not be generated until get_table() is invoked, and the results can be modified via map_column().

get_table

$scalar = $table->get_table($sans_title,$sans_whitespace)

Renders and returns the XHTML table. The first argument is a non-zero, defined value that suppresses the column titles. The column foots can be suppressed by not calculating totals, and the body can be suppressed by an appropriate SQL query. The caption and colgroup cols can be suppressed by not modifying them. The column titles are the only section that has to be specifically 'told' not to generate, and this is where you do that.

print $table->get_table;      # produces titles by default
print $table->get_table(1);   # does not produce titles

The second argument is another non-zero, defined value that will result in the output having no text aligning whitespace, that is no newline(\n) and tab(\t) charatcters.

modify_tag

$table->modify_tag($tag,$args,[$cols])

This method will store a 'memo' of what attributes you have assigned to various tags within the table. When the table is rendered, these memos will be used to create attributes. The first argument is the name of the tag you wish to modify the attributes of. You can supply any tag name you want without fear of halting the program, but the only tag names that are handled are <table> <caption> <thead> <tfoot> <tbody> <colgroup <col> <tr> <th> and <td>. The tag name will be converted to uppercase, so you can practice safe case insensitivity.

The next argument is a reference to a hash that contains the attributes you wish to apply to the tag. For example, this sets the attributes for the <table> tag:

$table->modify_tag('table',{
    border => 2,
    width  => '100%',
    foo    => 'bar',
});

# a more Perl-ish way
$table->modify_tag(table => {
    border => 2,
    width  => '100%',
    foo    => 'bar',
});

Each KEY in the hash will be lower-cased, and each value will be surrounded in quotes. The foo=>bar entry illustrates that typos in attribute names will not be caught by this module. Any valid XHTML attribute can be used. Yes. Even JavaScript.

You can even use an array reference as the key values:

$table->modify_tag('td',{
    bgcolor => [qw(red purple blue green yellow orange)],
}),

Each <td> tag will get a color from the list, one at time. When the last index is reached (orange), the next <td> tag will get the first index (red), continuing just like a circular queue until no more <td> tags are left.

This feature changes attributes in a horizontal fasion, each new element is popped from the array every time a <td> tag is created for output. Use set_row_color() when you need to change colors in a vertical fashion. Unfortunately, no method exists to allow other attributes besides BGCOLOR to permutate in a vertical fashion.

The last argument is optional and can either be a scalar representing a single column or area, or an array reference containing multilple columns or areas. The columns will be the corresponding names of the columns from the SQL query. The areas are one of three values: HEAD, BODY, or FOOT. The columns and areas you specify are case insensitive.

# just modify the titles
$table->modify_tag('th',{
    bgcolor => '#bacaba',
}, 'head');

You cannot currently mix areas and columns.

If the last argument is not supplied, then the attributes will be applied to the entire table via a global memo. However, entries in the global memo are only used if no memos for that column or area have been set:

# all <td> tags will be set
$table->modify_tag('td',{
    class => 'foo',
});

# except those for column BAR
$table->modify_tag('td',{
    class => 'bar',
}, 'bar');

The order of the execution of the previous two methods calls is commutative - it doesn't matter.

A final caveat is setting the <caption> tag. This one breaks the signature convention:

$table->modify_tag('caption', $value, $atr);

Since there is only one <caption> allowed in an XHTML table, there is no reason to bind it to a column or an area:

# with attributes
$table->modify_tag('caption','A Table Of Contents',{
    class => 'body',
});

# without attributes
$table->modify_tag('caption','A Table Of Contents');

The only tag that cannot be modified by this method is the <COL> tag. Use add_col_tag to add these tags instead.

add_col_tag

$table->add_col_tag($cols)

Add a new <col> tag and attributes. The only argument is reference to a hash that contains the attributes for this <col> tag. Multiple <col> tags require multiple calls to this method. The <colgroup> tag pair will be automatically generated if at least one <col> tag is added.

Advice: use <col> and <colgroup> tags wisely, don't do this:

# bad
for (0..39) {
  $table->add_col_tag({
      foo => 'bar',
  });
}

When this will suffice:

# good
$table->modify_tag('colgroup',{
    span => 40,
    foo  => 'bar',
});

You should also consider using <col> tags to set the attributes of <td> and <th> instead of the <td> and <th> tags themselves, especially if it is for the entire table:

  $table->add_col_tag({
      span  => $table->get_col_count(),
	  class => 'body',
  });

map_col

$table->map_col($subroutine,[$cols])

Map a supplied subroutine to all the <td> tag's cdata for the specified columns. The first argument is a reference to a subroutine. This subroutine should shift off a single scalar at the beginning, munge it in some fasion, and then return it. The second argument is the column or columns to apply this subroutine to. Example:

# uppercase the data in column DEPARTMENT
$table->map_col( sub { return uc shift }, 'department');

One temptation that needs to be addressed is using this method to color the cdata inside a <td> tag pair. For example:

  # don't be tempted to do this
  $table->map_col(sub {
    return qq|<font color="red">| . shift . qq|</font>|;
  }, 'first_name');

  # when this will work (and you dig CSS)
  $table->modify_tag({
	  style => 'Color: red;',
  }, 'first_name');

Another good candidate for this method is turning the cdata into an anchor:

$table->map_col(sub {
  my $raw = shift;
  return qq|<a href="/foo.cgi?process=$raw">$raw</font>|;
}, 'category');

This method permantly changes the data, so use it wisely and sparringly. This consequence will be removed in a future version.

set_row_colors

$table->set_row_colors([$colors],[$cols])

Assign a list of colors to the body cells for specified columns or the entire table if none specified for the purpose of alternating colored rows. This is not handled in the same way that modify_tag() rotates the BGCOLOR attribute. That method rotates on each column (think horizontally), this one rotates on each row (think vertically). However:

  # this:
  $table->modify_tag('td',{
	  bgcolor => [qw(green green red red)],
  }, [qw(first_name last_name)]);

  # produces the same output as:
  $table->set_row_colors(
      [qw(green red)],
	  [qw(first_name last_name)],
  );

This is a strong possibiliy that this method will be deprecated to make way for a method that handles any attribute, not just BGCOLOR. If so, this method will just hand the arguments to the new method, so as not to break any clients.

set_null_value

$table->set_null_value($new_null_value)

Change the default null_value (&nbsp;) to something else.

set_group

$table->set_group($column)

Assign one column as the main column. Every time a new row is encountered for this column, a <tbody> tag is written. An optional second argument that contains a defined, non-zero value will cause duplicates to be permanantly eliminated for this row. An optional third argument specifies what value to replace for duplicates, default is &nbsp;

# replace duplicates with the global 'null_value'
$table->set_group('Branch',1);

# replace duplicates with a new value
$table->set_group('Branch',1,'----');

# or in a more Perl-ish way
$table->set_group('Branch',nodups=>'----');

Don't assign a column that has a different value each row, choose one that is a super class to the rest of the data, for example, pick album over song, since an album consists of songs.

calc_totals

$table->calc_totals([$cols],$mask)

Computes totals for specified columns. The first argument is the column or columns to sum, again a scalar or array reference is the requirement. Non-numbers will be ignored, negatives and floating points are supported, but you have to supply an appropriate sprintf mask, which is the optional second argument, in order for the sum to be correctly formatted. See sprintf for further details.

calc_subtotals

$table->calc_subtotals([$cols],$mask)

Computes subtotals for specified columns. It is manditory that you first specify a group via set_group() before you call this method. Each subtotal is tallied from the rows that have the same value in the column that you specified to be the group. At this point, only one subtotal can be calculated and displayed.

get_col_count

$scalar = $table->get_col_count()

Returns the number of columns in the table.

get_row_count

$scalar = $table->get_row_count()

Returns the numbers of body rows in the table.

TAG REFERENCE

   TAG        CREATION    BELONGS TO AREA
+------------+----------+--------------------+
| <table>    |   auto   |       ----         |
| <caption>  |  manual  |       ----         |
| <colgroup> |  manual  |       ----         |
| <col>*     |  manual  |       ----         |
| <thead>    |   auto   |       head         |
| <tbody>    |   auto   |       body         |
| <td>       |   auto   |       body         |
| <tr>       |   auto   |  head,body,foot    |
| <th>       |   auto   |  head,body,foot    |
| <tfoot>    |   auto   |       foot         |
+------------+-------------------------------+

* All tags use B<modify_tag()> to set attributes
  except <col>, which uses B<add_col_tag()> instead

TUTORIAL

This section provides a quick tutorial for you to learn about the available methods and the somewhat proper way to use them. For simplicity's sake, the sample database table is nothing more than a glorified flat file, but it will suffice:

+----------------------+
|Child    Parent   Take|
+----------------------+
|bugs     Mo         5 |
|daffy    Larry      4 |
|donald   Larry      4 |
|porky    Curly      7 |
|mickey   Mo         8 |
|goofy    Curly      9 |
|cartman  Mo         2 |
+----------------------+

Call this table BAR, and let's assign it to database FOO. The important thing to note about this table is that one column is numbers, and the other two have a 1 to M relationship with each other (that is, one Parent can have many Children). You will probably never encounter a database table like this in production, but many of the result sets returned by a database do have a similar structure.

Step 1. Establish a connection to the database server.

Here is where the mileage varies. It is vital that you understand that different databases handle different arguments for connection. Read DBI as well the one for the DBD module you installed.

For this example, assume that we are using MySql on a server named deadbeef, and we can connect with the password for user 'sparky' (the database is 'FOO'):

my $table = DBIx::XHTML_Table->new(
  'DBI:mysql:FOO:deadbeef', 'sparky', '********'
) || die "could not connect to database\n";

Step 2. Execute a SQL query

Let's get all the rows, parent first, child second, the take third, all ordered by parent, then child:

$table->exec_query("
  SELECT PARENT,CHILD,TAKE
  FROM BAR
  ORDER BY PARENT,CHILD
");

Step 3. Mold an XHTML table

At this point, we have the means to retrieve a very basic XHTML table. Everything will be displayed nice and lined up, but folks want 'pretty bridges'. Start by modifying the <table> tag:

$table->modify_tag(table => {
  border      => 2,
  cellspacing => 0,
  width       => '40%',
  rules       => 'groups',
  summary     => 'weekly takes',
});

print $table->get_table();

Add a caption:

$table->modify_tag(caption => 'This Weeks Takes');

# to see this example progress from simple to complex,
# add each of the following snippets (one at a time)
# to your code, just before the call to get_table()

Let's sum up how much the kids took this week:

$table->calc_totals('take');

The totals appear as the last row, in the FOOT area. Color that as well as the HEAD area:

$table->modify_tag(th => {
  bgcolor  => '#98a898',
}, [qw(head foot)]);

The duplicate names in the Parent's column is annoying, you can pick one and only one column to suppress duplicates on, via set_group():

$table->set_group('parent',1);

Get a running subtotal of each of the kid's takes. You can only calculate one subtotal that is based off of the group you designated, Parent:

$table->calc_subtotals('take');

More rows added to the BODY area. Change their color:

$table->modify_tag(th => {
  bgcolor  => '#a9b9a9',
},'body');

Hmmm, now the take column looks off-balance, change the aligment:

$table->modify_tag(td => {
  align  => 'center',
},'take');

And finally, spice up the body rows with alternating colors:

$table->set_row_colors(['#bacaba','#cbdbcb']);

Experiment, have fun with it. Go to PerlMonks and download extremely's Web Color Spectrum Generator and use it supply a list of colors to set_row_colors. You can find it at http://www.perlmonks.org/index.pl?node_id=70521

BUGS

Problems with 'SELECT *'

Users are recommended to avoid 'select *' and instead specify the names of the columns. Problems have been reported using 'select *' with SQLServer7 will cause certain 'text' type columns not to display. I have not experienced this problem personally, and tests on Oracle and mySQL show that they are not affected by this. SQLServer7 users, please help me confirm this. :)

Not specifying <body> tag in CGI scripts

I anticipate this module to be used by CGI scripts, and when writing my own 'throw-away' scripts, I noticed that Netscape 4 will not display a table that contains XHTML tags IF a <body> tag is NOT found. Be sure and print one out.

CREDITS

Briac 'OeufMayo' Pilpré for the name

Mark 'extremely' Mills for patches and suggestions

Matt Sergeant for DBIx::XML_RDB

Perl Monks for the education

SEE ALSO

DBI

AUTHOR

Jeffrey Hayes Anderson <captvanhalen@yahoo.com>

COPYRIGHT

Copyright (c) 2001 Jeffrey Hayes Anderson. All rights reserved. DBIx::XHTML_Table is free software; it may be copied, modified, and/or redistributed under the same terms as Perl itself.

cpanm DBIx::XHTML_Table

perl -MCPAN -e shell
install DBIx::XHTML_Table