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.

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

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

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

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

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

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

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

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

You want to display a caption for the table.

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

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

Use aliases.

$table->exec_query("
   select foo as larry, bar as curley, baz as moe
   from qux
");

   
$table->modify(th => {
   style => 'text-align: right',
},'curley');
#  curley, not bar

top

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.

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

# upper case all column headers
$table->map_head(sub { uc shift });

# 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

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

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

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

top

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

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

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

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

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.

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

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

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

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

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

You want to do that thingy ...

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

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

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

You want to format the cells that hold the totals.

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

$table->modify(th => {
   style => 'text-align:left',
},'foot');

top

You also want sub-totals.

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

You want to format the cells that hold the subtotals.

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

$table->modify(th => {
   style => 'text-align:left',
},'body');

top

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

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

You want the entire table to have the same color.

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

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

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

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

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

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

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

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

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

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

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'
");

top

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

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

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

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

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

top