NAME

DBIx::XHTML_Table - Create XHTML tables from SQL queries

VERSION

0.98

SYNOPSIS

  use DBIx::XHTML_Table;

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

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

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

  print $table->output();

  # and much more, read on . . .

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. It is not intended for serious production use, although it use is viable for prototyping and just plain fun.

HOME PAGE

The DBIx::XHTML_Table homepage is now available, but still under construction. A partially complete FAQ and CookBook are available there, as well as the Tutorial, download and support info:

http://jeffa.perlmonk.org/XHTML_Table/

CONSTRUCTOR

style 1

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

Note - all optional arguments are denoted inside brackets.

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'.

# MySQL example
my $table = DBIx::XHTML_Table->new(
  'DBI:mysql:database:host',   # datasource
  'user',                      # user name
  'password',                  # user password
);

The $table_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',
  },
};

The purpose of $table_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 after the XHTML_Table object is destroyed, then 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 output().

Note that I only added this feature because it was too easy and simple not to. The intention of this module is that it be used with DBI, but who says you have to follow the rules.

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 output() is invoked, and the results can be modified via modify_tag().

output

$scalar = $table->output([$attribs])

Renders and returns the XHTML table. The only argument is an optional hash reference that can contain any combination of the following keys are set to a true value. Most of the time you will not want to use this argument, but there are three times when you will:

# 1 - do not display a thead section
print $table->output({ no_head => 1 });

This will cause the thead section to be suppressed, but not the caption if you set one. 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.

# 2 - do not format the headers with ucfirst
print $table->output({ no_ucfirst => 1 });

This allows you to bypass the automatic upper casing of the first word in each of the column names in the table header. If you just wish to have them displayed as all lower case, then use this option, if you wish to use some other case, use map_head()

# 3 - 'squash' the output HTML table
print $table->output({ no_whitespace => 1 });

This will result in the output having no text aligning whitespace, that is no newline(\n) and tab(\t) charatcters. Usefull for squashing the total number of bytes resulting from large return sets.

There is no reason to use no_ucfirst with no_head, since no_head will bybass the code that no_ucfirst will bypass.

Note: versions prior to 0.98 used a two argument form:

$scalar = $table->output([$sans_title,$sans_whitespace])

You can still use this form to suppress titles and whitespace, but warnings will be generated.

get_table

$scalar = $table->get_table([ {attribs} ])

Deprecated - use output() instead.

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

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

# you can even specify CSS styles
$table->modify_tag(td => {
   style => 'color: blue; text-align: center',
});

# there is more than one way to do it
$table->modify_tag(td => {
   style => {
      color        => 'blue',
      'text-align' => 'center',
   }
});

Each key in the hash ref will be lower-cased, and each value will be surrounded in quotes. Note that typos in attribute names will not be caught by this module. Any attribute can be used, valid XHTML attributes tend be more effective. And yes, JavaScript works too.

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

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

As the table is rendered row by row, column by column, the elements of the array reference will be 'rotated' across the <td> tags, causing different effects depending upon the number of elements supplied and the number of columns and rows in the table. The following is the prefered XHTML way with CSS styles:

  $table->modify_tag(th => {
     style => {
        background => ['#cccccc','#aaaaaa'],
	 }
  });

The last argument to modify_tag() 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'. These 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 - the results are the same, so don't worry about too much about it.

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',
	 { align => 'bottom' }
  );

  # 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(),
	 style => 'text-align: center',
  });

map_cell

$table->map_cell($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_cell( 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_cell(sub {
    return qq|<font color="red">| . shift . qq|</font>|;
  }, 'first_name');

  # when CSS styles will work
  $table->modify_tag(td => {
	 style => 'Color: red;',
  }, 'first_name');

Note that the get_current_row() and get_current_col() are can be used inside a callback. See set_pk() below for an example.

If [$cols] is not specified, all columns are assumed. This method does not permantly change the data. Also, exec_query() _must_ be called and data must be returned from the database before this method is be called, otherwise the call back will not be used and a warning will be generated.

map_col

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

Deprecated - use map_cell() instead.

map_head

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

Just like map_cell(), except it modifies only column headers, i.e. the <th> data located inside the <thead> section. The immediate application is to change capitalization of the column headers, which are defaulted to ucfirst:

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

Instead of using map_head() to lower case the column headers, just specify that you don't want default capitalization with output():

$table->output({ no_ucfirst => 1 });

Again, if [$cols] is not specified, all columns are assumed. This method does not permantly change the data and exec_query() _must_ be called and data must be returned from the database before this method can be called, otherwise the call back will not be used and a warning will be generated.

set_row_colors

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

This first argument is either a scalar for one color or an array reference for more than one color. The second argument is simliar.

This method assigns 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 attribute. That method rotates on each column (think horizontally), this one rotates on each row (think vertically).

The rows are colored via CSS styles. If you really, really want to use the deprecated bgcolor attribute then you really, really can by by passing a true 3rd argument:

$table->set_row_colors([qw(red green blue)],undef,1);

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_pk

$table->set_pk([$primary_key]);

This method must be called before exec_query() in order to work!

Note that the single argument to this method, $primary_key, is optional. If you do not specify a primary key, then 'id' will be used.

This is highly specialized method - the need is when you want to select the primary key along with the columns you want to display, but you don't want to display it as well. The value will be accessible via get_current_row(). This is useful as a a callback via map_cell(). Consider the following:

$table->map_cell(sub { 
  my $datum = shift;
  my $row   = $table->get_current_row();
  my $col   = $table->get_current_col();
  return qq|<input type="text" name="$row:$col" value="$datum">|;
});

This will render a "poor man's" spreadsheet, provided that set_pk() was called with the proper primary key before exec_query() was called. Now each input has a name that can be split to reveal which row and column the value belongs to.

Big thanks to Jim Cromie for the idea.

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.

get_current_row

$scalar = $table->get_current_row()

Returns the value of the primary key for the current row being processed. This method is only meaningful inside a map_cell() callback; if you access it otherwise, you will either receive undef or the value of the primary key of the last row of data.

get_current_col

$scalar = $table->get_current_col()

Returns the name of the column being processed. This method is only meaningful inside a map_cell() callback; if you access it otherwise, you will either receive undef or the the name of the last column specified in your SQL statement.

TAG REFERENCE

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

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

BUGS

If you have found a bug, please visit Best Practical Solution's CPAN bug tracker at http://rt.cpan.org.

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 with 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

Jim Cromie for presenting the whole spreadsheet idea.

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)