NAME

DBIx::XHTML_Table - Create XHTML tables from SQL queries

SYNOPSIS

  use DBIx::XHTML_Table;

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

  my $table = DBIx::XHTML_Table->new($dsrc,$usr,$pass) or die;

  $table->exec_query("
	select foo from bar
	where baz='qux'
	order by foo
  ");

  print $table->get_table;

  # and much more, read on . . .

DESCRIPTION

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.

I am firm believer in separating presentation from content, but breaking the rules can be pretty fun sometimes! The beauty of this module is that you are allowed to be as simple or as complex as you wish. Enough with the sales pitch, onward!

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.

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.

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(@credentials[,$attribs])

# note - all optional args are denoted inside brackets

Construct a new XHTML_Table object by supplying the database credentials:

# mysql example
my $table = DBIx::XHTML_Table->new(
  'DBI:mysql:database:host',   # datasource
  'user',                      # user name
  'password',                  # user password
) or die "couldn't connect!";

The constuctor will simply pass the credentials to the DBI::connect() method - see DBI as well as the one for your corresponding DBI driver module (DBD::Oracle, DBD::Sysbase, DBD::mysql, etc) for the proper format for 'datasource'.

The $attribs argument is optional - it should be a hash reference whose keys are the names of any XHTML tag (colgroup and col are very experimental right now), and the values are hash references containing the desired attrbutes for those tags:

# valid example for last argument
my $table_attribs = {
  table => {
    width       => '75%',
    cellspacing => '0',
    rules       => 'groups',
  },
  caption => 'Example',
  td => {
    align => 'right',
  },
};

my $table = DBIx::XHTML_Table->new(@credentials,$table_attribs);

The purpose of $attribs is to allow the bypassing of having to call modify_tag() (see below) multiple times. Right now, $attribs can only modify 'global' tags - i.e., you can't specify attributes by Areas (head, body, or foot) or Columns (specific to the query result) - see modify_tag() below for more on Areas and Columns.

style 2

$obj_ref = new DBIx::XHTML_Table($DBH[,$attribs])

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[,$attribs)

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. As a side effect, that first row will be removed from $ref upon instantiation. You can always bypass the printing of the heading via get_table().

OBJECT METHODS

exec_query

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

Pass the query off to the database with hopes that data will be returned. The first argument is scalar that contains the SQL code, the optional 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,$attribs[,$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 lowercase, 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',
});

# only <td> tags in column BAR will be set
$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(tag => $value, $attrib);

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 modify_tag() 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. Notice the use of the get_col_count() method in this example:

  $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 (scalar) or columns (reference to a list of scalars) 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(td => {
	  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');

If [$cols] is not specified, all columns are assumed. This method does not permantly change the data.

map_head

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

Just like map_col(), except it modifies only column headers, i.e. the <th> data located inside the <thead> section. The immediate application is to ucfirst() the column headers:

$table->map_head(sub { ucfirst shift });

If [$cols] is not specified, all columns are assumed. This method does not permantly change the data.

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

Again, $cols is optional, and can be either a single column (scalar) or multiple columns (reference to a list of scalars), and if not present, all columns are assumed.

Another way to alternate row colors is by using the set_group() method (explained further below) and modify_tag() on <tbody>:

  $table->set_group('<pick the most redundant column here!>');

  $table->modify_tag(tbody => {
	  bgcolor => [qw(green red)],
  });

set_null_value

$table->set_null_value($new_null_value)

Change the default null_value ( ) to something else. Any column that is undefined will have this value substituted instead.

set_group

$table->set_group($column[,$no_dups,$replace_with])

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  

# 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. See the Tutorial below (you know, past the chickens) for more on this method.

So, what's it good for? If you supply the following two attributes (and their associated values) to the <table> tag:

  # only usefull if you set a group, by the way
  $table->modify_tag(table => {
	  cellspacing => '0',
	  rules       => 'groups',
  });

then horizontal lines will only appear at the point where the 'grouped' column changes. This had to be implemented in the past with <table>'s inside of <table>'s. Much nicer! Add this for a nice coloring trick:

  # works with or without setting a group
  $table->modify_tag(tbody => {
	  bgcolor => [qw(insert rotating colors here)],
  });

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. If $cols is not specified, all columns will be totaled. 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 row per group 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:

# 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()

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

# at this point, only the necessary tags are present
print $table->get_table();

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',
});

# etc . . .
print $table->get_table();

Add a caption:

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

Make the column headers display the first letter on their name as upper-cased:

$table->map_head(sub { ucfirst shift} );

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

AUTHOR

Jeffrey Hayes Anderson <captvanhalen@yahoo.com>

COPYRIGHT

To install DBIx::XHTML_Table, copy and paste the appropriate command in to your terminal.

cpanm

cpanm DBIx::XHTML_Table

CPAN shell

perl -MCPAN -e shell
install DBIx::XHTML_Table

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)