/* FILE ARCHIVED ON 7:30:05 Jul 9, 2007 AND RETRIEVED FROM THE INTERNET ARCHIVE ON 18:03:58 Sep 22, 2011. JAVASCRIPT APPENDED BY WAYBACK MACHINE, COPYRIGHT INTERNET ARCHIVE. ALL OTHER CONTENT MAY ALSO BE PROTECTED BY COPYRIGHT (17 U.S.C. SECTION 108(a)(3)). */ DBIx::XHTML_Table - Cookbook
1: Intro

1.1: Getting Started

Problem

You want to have DBIx::XHTML_Table select some rows from a database table that you specify and return a string containing the results wrapped in XHTML tags.

Solution

Instantiate a new DBIx::XHTML_Table object, send it a query via the exec_query() method, and finally call the output() method.

use DBIx::XHTML_Table;

my $table = DBIx::XHTML_Table->new('DBI:vendor:database:host', 'user, 'pass');

$table->exec_query(' select foo,bar,baz,qux,quf from table order by foo ');

my $output = $table->$output();

At this point, $output will contain the results. If you are in a hurry you can even stack method calls:
use DBIx::XHTML_Table;

print DBIx::XHTML_Table ->new('DBI:vendor:database:host', 'user, 'pass') ->exec_query('select foo,bar,baz,qux,quf from table order by foo') ->$output();

top

1.2: Create a plain table.

Problem

You just want to line-up the results of a SQL select statement without having to resort to a PRE tag.

Solution

DBIx::XHTML_Table will not specify any attributes by default, so all you need to do is call the output() method and either print it the returned string or save it in a variable for later.
print $table->output();

Foo Bar Baz Qux Quf
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

top

1.3: Create a table with borders.

Problem

Not only do you want your query results displayed in order, you would like them seperated by borders.

Solution

Use the modify() method on the TABLE tag and define the attribute 'border' with a numerical value, such as 5:
$table->modify(table => { border => 5 });

Foo Bar Baz Qux Quf
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

However, the border attribute is deprecated:
$table->modify(table => { 
   style => {
     'border-style' => 'outset',
     'border-width' => '5px',
   }
});

Foo Bar Baz Qux Quf
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

That only surrounded the table (it did what it said). To achieve the same effect as using the HTML 'border' attribute with a value of 1 (one), more work needs to be done: the TH and TD tags need to be modified as well as the TABLE tag:
$table->modify(table => { 
   style => {
     'border-style' => 'outset',
     'border-width' => 'thin',
   }
});

$table->modify($_ => { style => { 'border-style' => 'inset', 'border-width' => 'thin', } }) for qw(th td);

Foo Bar Baz Qux Quf
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

The rest of the examples will use the HTML 'border' attribute with a value of 1 (one) to border the tables. It may be deprecated, but people still use Netscape 3.0 to this day.

top


2: Altering the Head

2.1: Change the header's text alignment.

Problem

You want to display the column names as in some other justification than the default 'center'.

Solution

Use the modify() method on 'th':

# XHTML prefered way
$table->modify(th => { style => 'text-align: right' });

# deprecated HTML way $table->modify(th => { align => 'right' });

top

2.2: Add a caption.

Problem

You want to display a caption for the table.

Solution

Add caption tags by calling the modify() method on 'caption':
$table->modify(caption => 'Hello World');

Hello World
Foo Bar Baz Qux Quf
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

The modify() method works a little differently for caption then the rest of the tags it modifies. Here is how you can supply attributes:
$table->modify(caption => 'Hello World', {
   style => 'color: green; font-style: italic'
});

Hello World
Foo Bar Baz Qux Quf
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

Style can be specified as strings or as hash references:
$table->modify(
   caption => 'Hello Border', {
      style => {
         'font-size'    => 'x-large',
         'border-style' => 'double',
      }
});

Hello Border
Foo Bar Baz Qux Quf
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

This will place the caption at the bottom of the table instead of the top, does not work with Konqueror 2.1.1:
$table->modify(
   caption => 'Hello Bottom', {
      align => 'bottom',
      style => 'font-size: x-large',
   }
);

Hello Bottom
Foo Bar Baz Qux Quf
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

top

2.3: Rename the column headers.

Problem

You want to display the column names as headers, but you would rather use different names.

Solution

Use aliases.

$table->exec_query("
   select foo as larry, bar as curley, baz as moe
   from qux
");
Just remember that you must use the aliases when specifying certain columns in modify():
   
$table->modify(th => {
   style => 'text-align: right',
},'curley');
#  curley, not bar

top

2.4: Change the header's case.

Problem

DBIx::XHTML_Table displays the column names (or aliases) with a default of ucfirst, that is, the first letter upper cased and the rest lower cased. You want to override this default behavior.

Solution

Use the map_head() method. However, if you just want all lower case column names, specify so via the output() method's attribute hash reference instead:

print $table->output({ no_ucfirst => 1 });
Otherwise, use the map_head() method:
# upper case all column headers
$table->map_head(sub { uc shift });

FOO BAR BAZ QUX QUF
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

# lower case only certain columns
$table->map_head(
   sub { lc shift },
   [qw(bar baz qux)],
);

Foo bar baz qux Quf
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

top

2.5: Create a table with no header.

Problem

You only want the data of the query, not the names of the columns marked-up as headers.

Solution

Use the attribute hash reference in the output() method like so:

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

top

2.6: Add col and colgroup tags.

Problem

You want to utilize col and colgroup to control table formatting.

Solution

Use the modify() method on 'colgroup' to create a colgroup tag. The get_col_count() method is useful here if you want to span the entire table.
$table->modify(colgroup => {
   span  => $table->get_col_count(),
   style => 'text-align: right',
});

Foo Bar Baz Qux Quf
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

Use the add_col_tag() method to specify individual col tags:
# the first row
$table->add_col_tag({
   span  => 1,
   style => 'color: red',
});

# the remaining rows $table->add_col_tag({ span => $table->get_col_count() - 1, style => 'color: green', });

Foo Bar Baz Qux Quf
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

Note that Mozilla 0.9.7 and Konqueror 2.1.1 do not handle this correctly at the moment. If you are viewing this with those browsers then you will not see any colors. Seems that the only way Mozilla will even recognize a tag is if there are no tags and the only attribute recognized is 'width':
$table->modify(colgroup => {
   span  => 2,
   width => 80,
   style => 'color: green',
});

Foo Bar Baz Qux Quf
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

But this is not a problem with Internet Explorer.

top


3: Altering the Body

3.1: Setting attributes for specific columns

Problem

You want to specify attributes for the tbody td tags, but not all of them, just certain columns.

Solution

Use modify() on 'td' and specify which columns with modify()'s third argument. One column can be specified with a scalar:
# only effect one column
$table->modify(td => { 
   style => {
      color        => 'green',
      'text-align' => 'center',
   }
},'bar');

Foo Bar Baz Qux Quf
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

More than one column requires an array reference:
$table->modify(td => { 
   style => {
      'text-align' => 'right',
      color        => 'red',
  }
},['foo','baz','quf']);

Foo Bar Baz Qux Quf
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

Alternate styles:
$table->modify(td => { 
   style => [
      'text-align: right; color: red',
      'text-align: left; color: black',
   ]
},['foo','baz','quf']);

Foo Bar Baz Qux Quf
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

top

3.2: Exclude a column from displaying.

Problem

You have a query that contains a number of columns, but you really don't want to display one or more of those columns in the table.

Solution

Don't do that. If you don't want to display column 'bar', then don't SELECT column bar in the database query.

There is one exception to this rule: the primary key column. If you wish to have the primary key available for each row, but don't want to display it as a column then use the set_pk() method. The value of the primary key for each row can be obtained via get_current_row(), but only inside the map_cell() method's call-back.

In all fairness, however, should you really, really want to DELETE a column after you select it you can use the drop_cols() method:
$table->drop_cols([qw(qux quf)]);

Foo Bar Baz
a 1 5
a 2 6
b 3 7
b 4 8

top

3.3: Add a column to the display.

Problem

You want to add a column that does not exist to the existing columns.

Solution

Use the add_cols() method:
$table->add_cols({
   name   => 'New', 
   data   => [10,20,30,40],
   before => 3,
});

Foo Bar Baz New Qux Quf
a 1 5 10 nine foo
a 2 6 20 ten bar
b 3 7 30 11 baz
b 4 8 40 12 qux

top

3.4: Make a scolling body.

Problem

You like to add a vertical scroll bar to your table so that large results do not require the viewer to scroll the window.

Solution

Use modify() on 'tbody' and CSS:
$table->modify(tbody => {
   style => {
      overflow => 'auto',
      height   => '75px',
   }
});

Foo Bar Baz Qux Quf
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

This is only available for Mozilla. I know for sure that it does not render in Internet Explorer and Konqueror2.1.1, but I have not tested in other browsers.

top

3.5: Seperate by groups

Problem

You want to do that thingy ...

Solution

Use the modify() method on 'table' and the set_group() method to group the most repeated column in the table. This causes each distict item from that column to start a new tbody group, which can be exploited by XHTML:
$table->modify(table => {
   cellspacing => 0,
   rules       => 'groups',
});

$table->set_group('foo');

Foo Bar Baz Qux Quf
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

top


4: Altering the Foot

4.1: Generating totals.

Problem

You have at least one column that consists of digits that you would like to total and display.

Solution

Use the calc_totals() method on the columns you wish to total:
$table->calc_totals(['bar','baz']);

Foo Bar Baz Qux Quf
  10 26    
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

Don't specify any arguments to total all columns, notice what happens when not all cells are numbers:
$table->calc_totals();

Foo Bar Baz Qux Quf
  10 26 23  
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

top

4.2: Formatting the total cells.

Problem

You want to format the cells that hold the totals.

Solution

Use modify() on 'th' in the 'foot' area:

$table->modify(th => {
   style => 'text-align:left',
},'foot');
By specifying only the 'foot' area, you exclude the th tags in the head area from being affected.

top

4.3: Generating sub-totals.

Problem

You also want sub-totals.

Solution

Use the calc_subtotals() method with the set_group() method. Calling the calc_totals() is necessary to print totals as well, but not required.
$table->set_group('foo');
$table->calc_totals();
$table->calc_subtotals();

$table->modify(th => { style => 'background: #bbbbbb' },[qw(head foot)]);

$table->modify(th => { style => 'background: #dddddd' },'body');

Foo Bar Baz Qux Quf
  10 26 23  
a 1 5 nine foo
a 2 6 ten bar
  3 11    
b 3 7 11 baz
b 4 8 12 qux
  7 15 23  

top

4.4: Formatting the subtotal cells.

Problem

You want to format the cells that hold the subtotals.

Solution

Use modify() on 'th' in the 'body' area:

$table->modify(th => {
   style => 'text-align:left',
},'body');
Although technically subtotals should be considered as footer information, since totals already are occupied in the tfoot tag, subtotals are left with the tbody tags.

top

4.5: Add arbitrary data to the foot.

Problem

You want to customize the foot area, maybe insert HTML form elements or even repeat the column headers.

Solution

Hack! There are currently no methods to handle this request, pretty much because I feel that if you need this kind of formatting, then DBIx::XHTML_Table is probably the wrong tool for you.

However, there are ways to get around my stubbornness. The following will fill the footer with data that is not the totals.
$table->{totals} = [
   qw(these are not the droids)
];

Foo Bar Baz Qux Quf
these are not the droids
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

When the output() method is called, the existance of 'totals' will cause th tags in the foot area to be populated with the values from 'totals' - this is a hack to trick DBIx::XHTML_Table into allowing you to add your own data. This gets really interesting when you add HTML form elements. But be careful that you supply the same number of 'total' elements as there are columns in the displayed table. You can test with the get_col_count() method.

The following code will repeat the column headers at the foot. Since the headers are rendering when when output() is called, some additional trickery is required for formatting:
$table->{totals} = [ 
   map { ucfirst } @{$table->{fields_arry}} 
];

Foo Bar Baz Qux Quf
Foo Bar Baz Qux Quf
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

top


5: Coloring the table

5.1: Color the whole table

Problem

You want the entire table to have the same color.

Solution

Use modify() on 'table' and specify a style. If you want to include a caption as well, you will have to do so explicitly.
$table->modify(table => {
	style => 'background: #cccccc'
});

$table->modify( caption => 'Caption', { style => { 'font-size' => 'large', background => '#cccccc', } } );

Caption
Foo Bar Baz Qux Quf
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

(note that this whole section is not limited to just background colors - any attribute will work)

top

5.2: Color the head and body differently

Problem

You want the header to stand out from the rest of the table.

Solution

Use the modify() method twice, once for 'thead' and once for 'tbody':
$table->modify(thead => {
   style => 'background: #aaaaaa'
});

$table->modify(tbody => { style => 'background: #dddddd' });

Foo Bar Baz Qux Quf
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

There Is More Than One Way To Do It!
$table->modify($_->[0] => {
   style => {background => $_->[1]}
}) for (['thead','#aaaaaa'],['tbody','#dddddd']);

Foo Bar Baz Qux Quf
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

top

5.3: Alternate colors by rows

Problem

You want to alternate two or more similar colors to create horizontal stripes.

Solution

Use set_row_colors():
$table->set_row_colors(['#d0d0d0','#f0f0f0']);

Foo Bar Baz Qux Quf
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

If you want to include the column headers, then use the modify() method:
$table->modify(tr => {
   style => {
      background => ['#d0d0d0','#f0f0f0']
   }
});

Foo Bar Baz Qux Quf
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

set_row_colors() is really just an interface to modify(). The first table could be generated with modify() by specifying 'tr' just like in the second example, the catch is to specify the 'body' area:
$table->modify(tr => {
   style => {
      background => ['#d0d0d0','#f0f0f0']
   }
},'body');

Foo Bar Baz Qux Quf
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

top

5.4: Alternate colors by columns

Problem

You want to alternate two or more similar colors to create vertical stripes.

Solution

Use set_col_colors():
$table->set_col_colors(['#d0d0d0','#f0f0f0']);

Foo Bar Baz Qux Quf
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

Unlike it's counterpart, set_row_colors(), set_col_colors() is more than just an interface to modify(). To see what I mean, observe what happens when the replacement code from the last recipe is used on 'td' instead of 'tr': (specifying 'body' is ok, but not necessary)
$table->modify(td => {
   style => {
      background => ['#d0d0d0','#f0f0f0']
   }
},'body');

Foo Bar Baz Qux Quf
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

Whoops! The problem happens when the number of colors supplied OR the number of columns displayed are BOTH not even numbers - unless the two numbers are the same in the first place. In this example there are five columns and two colors, which will yield a checkerboard pattern instead of stripes. If you want stripes, use set_col_colors(). Observe how 3,4, and 5 colors create different patterns across 5 columns:

3 colors:
$table->modify(td => {
   style => {
      background => [
         '#b0b0b0',
         '#d0d0d0',
         '#f0f0f0',
      ]
   }
});

Foo Bar Baz Qux Quf
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

4 colors:
$table->modify(td => {
   style => {
      background => [
         '#909090',
         '#b0b0b0',
         '#d0d0d0',
         '#f0f0f0',
      ]
   }
});

Foo Bar Baz Qux Quf
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

5 or more colors
$table->modify(td => {
   style => {
      background => [
         '#707070',
         '#909090',
         '#b0b0b0',
         '#d0d0d0',
         '#f0f0f0',
      ]
   }
});

Foo Bar Baz Qux Quf
a 1 5 nine foo
a 2 6 ten bar
b 3 7 11 baz
b 4 8 12 qux

If you supply more colors than columns, then the extra colors will be discarded, so more than 5 colors in this example will always be the same results as if 5 colors were passed.

top


6: Interacting with the database

6.1: Create a sorted table.

Problem

You want to display the results of the SQL select query statement in some order.

Solution

Use the database, not DBIx::XHTML_Table. Specify the SQL 'order by' clause in exec_query():

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

top

6.2: Use placeholders.

Problem

Your database driver utilizes placeholders and you want to use them.

Solution

Use question marks (?) instead of interpolated variables inside the SQL statement and wrap the variables in an array reference:

$table->exec_query('
   select foo from bar
   where baz = ? and qux = ?
',[$baz,$qux]);

# compare to interpolation

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

Using placeholders can increase application speed, but in the case of DBIx::XHML_Table, the biggest benefit of using them is not having to worry about quoting. A drawback is that some vendors (e.g. msql) do not support them.

top

6.3: Reuse the database handle.

Problem

You need to have a database handle for other parts of your script.

Solution

Instantiate a DBI object and pass it to the DBIx::XHTML_Table constructor:

my $dbh   = DBI->connect('DBI:vendor:database:host', 'user, 'pass');
my $table = DBIx::XHTML_Table->new($dbh);
The following CGI script demonstrates reusing the database handle. The script first gets a list of artists from the database and generates a scolling list for the user to select from. If an artist is selected, then a DBIx::XHTML_Table is created for all songs by that artist. Note the use of placeholders in exec_query()
use strict;
use DBI;
use DBIx::XHTML_Table;
use CGI qw(:standard);

my $DBH = DBI->connect( qw(DBI:mysql:mp3:host user pass), { RaiseError => 1 }, );

print header(), start_html(), start_form(), scrolling_list(-name=>'artist',-values=>get_artists(), -size=>8), submit(), end_form(), hr();

if (my $artist = param('artist')) {

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

$table->exec_query(' select aLbum,year,title,genre,id from songs where artist = ? order by year,album,id ',[$artist]);

print $table->output(); }

print end_html();

$DBH->disconnect;

sub get_artists { return $DBH->selectcol_arrayref(" select distinct artist from songs order by artist "); }

top

6.4: Use another DBIx class.

Problem

You want to use a CPAN module such as DBIx::Password.

Solution

Re-bless the object to the DBI::db namespace.

my $dbh = DBIx::Password->connect('jsmith');
bless $dbh, 'DBI::db';

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

Instantiating a new DBIx::XHTML_Table with the second style allows you to use create your own database handle. However, a DBI::db "object" is expected. Perl's flexible OO allows you re-bless an object to another namespace. This should work for any DBIx object that ISA DBI::db. (Thanks to Richard Piacentini for recommending a way to use DBIx::XHTML_Table with DBIx::Password.)

top