Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages


home | help
DBIx::XHTML_Table(3)  User Contributed Perl Documentation DBIx::XHTML_Table(3)

       DBIx::XHTML_Table - SQL query result set	to XHTML table.

	 use DBIx::XHTML_Table;

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

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

	     select foo	from bar
	     where baz='qux'
	     order by foo

	 print $table->output();

	 # stackable method calls:
	 print DBIx::XHTML_Table
	   ->exec_query('select	foo,baz	from bar')

	 # and much more - read	on ...

       DBIx::XHTML_Table is a DBI extension that creates an HTML table from a
       database	query result set. It was created to fill the gap between
       fetching	data from a database and transforming that data	into a web
       browser renderable table. DBIx::XHTML_Table is intended for programmers
       who want	the responsibility of presenting (decorating) data, easily.
       This module is meant to be used in situations where the concern for
       presentation and	logic seperation is overkill.  Providing logic or
       editable	data is	beyond the scope of this module, but it	is capable of
       doing such.

       For the most part, no new functionality will be added to	this module.
       Only bug	fixes and documentation	corrections/additions. All new efforts
       will be directed	towards	the rewrite of this distribution, DBIx::HTML.

       This distribution features a more flexible interface with fewer methods
       and logically named argument parameters.	At the core is an HTML
       attribute generator:

       o   Tie::Hash::Attribute

       Which is	used by	an HTML	tag generator:

       o   HTML::AutoTag

       Which is	used by	an HTML	table generator:

       o   Spreadsheet::HTML

       Which is	finally	wrapped	by a DBI extension:

       o   DBIx::HTML

       More documentation (tutorial, cookbook, FAQ, etc.) can be found at


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

	   Note	- all optional arguments are denoted inside brackets.

	   The constructor will	simply pass the	credentials to the
	   DBI::connect	method - read the DBI documentation as well as the
	   docs	for your corresponding DBI driver module (DBD::Oracle,
	   DBD::Sybase,	DBD::mysql, etc).

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

	   The last argument, $attribs,	is an optional hash reference and
	   should not be confused with the DBI::connect	method's similar
	   'attributes'	hash reference.'

	     # valid example for last argument
	     my	$attribs = {
	       table =>	{
		 border	     =>	1,
		 cellspacing =>	0,
		 rules	     =>	'groups',
	       caption => 'Example',
	       td => {
		 style => 'text-align: right',

	     my	$table = DBIx::XHTML_Table->new(
	     ) or die "couldn't	connect	to database";

	   But it is still experimental	and unpleasantly limiting.  The
	   purpose of $table_attribs is	to bypass having to call modify()
	   multiple times. However, if you find	yourself calling modify() more
	   than	4 or 5 times, then DBIx::XHTML_Table might be the wrong	tool.
	   I recommend HTML::Template or Template-Toolkit, both	available at

	     $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(
	       {RaiseError => 1},

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

	   You can also	use any	class that isa() DBI::db object, such as
	   Apache::DBI or DBIx::Password objects:

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

	     $obj_ref =	new DBIx::XHTML_Table($rows[,$headers])

	   The final style allows you to bypass	a database altogether if need
	   be. Simply pass a LoL (list of lists) such as the one passed	back
	   from	the DBI	method "selectall_arrayref()". The first row will be
	   treated as the table	heading. You are responsible for supplying the
	   column names. Here is one way to create a table after modifying the
	   result set from a database query:

	     my	$dbh  =	DBI->connect($dsource,$usr,$passwd);
	     my	$sth = $dbh->prepare('select foo,baz from bar');

	     # order is	essential here
	     my	$headers = $sth->{'NAME'};
	     my	$rows	 = $sth->fetchall_arrayref();

	     # do something to $rows

	     my	$table = DBIx::XHTML_Table->new($rows,$headers);

	   If $headers is not supplied,	then the first row from	the first
	   argument will be shifted off	and used instead.  While obtaining the
	   data	from a database	is the entire point of this module, there is
	   nothing stopping you	from simply hard coding	it:

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

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

	   And that is why $headers is optional.


	   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:

		 select	bar,baz	from foo
		 where bar = ?
		 and   baz = ?

	   exec_query()	also accepts a prepared	DBI::st	handle:

	     my	$sth = $dbh->prepare('
		 select	bar,baz	from foo
		 where bar = ?
		 and   baz = ?


	   Consult the DBI documentation for more details on bind vars.

	   After the query successfully	executes, the results will be stored
	   interally as	a 2-D array. The XHTML table tags will not be
	   generated until the output()	method is invoked.

	     $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, 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_indent => 1 });

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

	   You can combine these attributes, but there is no reason to use
	   no_ucfirst in conjunction with no_head.

	   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.

	   HTML	encoding of table cells	is turned off by default, but can be
	   turned on via:

	     $table->{encode_cells} = 1;

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

	   Deprecated -	use output() instead.


	   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:

		border => '2',
		width  => '100%'

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

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

	     # there is	more than one way to do	it
	     $table->modify(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

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

	     $table->modify(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 preferred XHTML way	with CSS styles:

	     $table->modify(th => {
		style => {
		   background => ['#cccccc','#aaaaaa'],

	   See the set_row_color() and set_col_color() methods for more	info.

	   The last argument to	modify() 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, or their
	   anticipated index number, starting at zero.	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(th => {
		bgcolor	=> '#bacaba',
	     },	'head');

	     # only <td> tags in column	FOO will be set
	     $table->modify(td => {
		style => 'text-align: center'

	     # <td> tags for the second	and third columns (indexes 1 and 2)
	     $table->modify(td => {
		style => 'text-align: right'

	   You cannot currently	mix areas and columns in the same method call.
	   That	is, you	cannot set a specific column in	the 'head' area, but
	   not the 'body' area.	This _might_ change in the future, but such
	   specific needs are a	symptom	of needing a more powerful tool.

	   As of Version 1.10, multiple	calls to modfiy() are inheritable.
	   For example,	if you set an attribute	for all	<td> tags and set
	   another attribute for a specific column, that specific column will
	   inherit both	attributes:

	     $table->modify(td => {foo => 'bar'});
	     $table->modify(td => {baz => 'qux'},'Salary');

	   In the preceding code, all <td> tags	will have the attribute	'foo =
	   "bar"', and the <td>	tags for the 'Salary' column will have the
	   attributes 'foo = "bar"' and	'baz = "qux"'. Should you not this
	   behavior, you can 'erase' the unwanted attribute by setting the
	   value of an attribute to the	empty string:

	     $table->modify(td => {foo => 'bar'});
	     $table->modify(td => {foo =>'', baz => 'qux'},'Salary');

	   Note	the use	of the empty string and	not undef or 0.	Setting	the
	   value to undef will work, but will issue a warning if you have
	   warnings turned on. Setting the value to 0 will set the value of
	   the attribute to 0, not remove it.

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

	     $table->modify(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
		caption	=> 'A Table Of Contents',
		{ align	=> 'bottom' }

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

	   The only tag	that cannot be modified	by modify() is the <col> tag.
	   Use add_col_tag() instead.


	   Deprecated, use the easier to type modify() instead.


	   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) {
		  foo => 'bar',

	   When	this will suffice:

	     # good
	     $table->modify(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 to span the entire table:

		span  => $table->get_col_count(),
		style => 'text-align: center',


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

	     # uppercase the data in the fifth column
	     $table->map_cell( sub { return uc shift },	4);

	   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>|;
	     },	[qw(first_name last_name)]);

	     # when CSS	styles will work
	     $table->modify(td => {
	       style =>	'color:	red',
	     },	[qw(first_name last_name)]);

	   Note	that the get_current_row() and get_current_col() can be	used
	   inside the sub reference. See set_pk() below	for an example.

	   All columns are used	if none	are specified, and you can specify
	   index number(s) as well as name(s).	Also, exec_query() must	be
	   called and data must	be returned from the database prior to calling
	   this	method,	otherwise the call back	will be	ignored	and a warning
	   will	be generated.  This is true for	map_head() as well.


	   Deprecated -	use map_cell() instead.


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


	   This	method will produce horizontal stripes.	 This first argument
	   is an array reference that contains the colors to use. Each row
	   will	get a color from the list - when the last color	in the list is
	   reached, then the rotation will start over at the beginning.	 This
	   will	continue until all <tr>	tags have been generated. If you don't
	   supply an array reference with at least 2 colors then this method
	   will	return without telling you.

	   set_row_colors() by default will use	CSS styles to color the	rows.
	   The optional	second argument	is a single scalar that	can be used to
	   specify another attribute instead of	the CSS	style 'color'. For
	   example, you	could use 'class' or even deprecated HTML attributes
	   such	as 'bgcolor' or	'width'.

	   This	method is just a more convenient way to	do the same thing with
	   the modify()	modify.

	   See for more
	   information on coloring the table.


	   This	method will produce vertical stripes.  The first argument is
	   an array reference to arrays	just like set_row_colors().

	   Unlike set_row_colors()  however, this module is more than just a
	   convenient way to do	the same with the modify() method.  The
	   problem arises when you supply an odd number	of colors for an even
	   number of columns, vice versa, or both odd. The result will be a
	   checkerboard. Not very readable for anything	except board games. By
	   using set_col_colors() instead, the result will always be vertical

	   set_col_colors() by default will use	CSS styles to color the	rows.
	   The optional	second argument	is a single scalar that	can be used to
	   specify another attribute instead of	the CSS	style 'color'. For
	   example, you	could use 'class' or even deprecated HTML attributes
	   such	as 'bgcolor' or	'width'.

	   See for more
	   information on coloring the table.


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


	   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

	   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 the get_current_row()	method.	This is	useful as a a
	   callback via	the map_cell() method.	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.


	   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'

	     # replace duplicates with a new value

	     # or in a more Perl-ish way

	   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.

	   So, what's it good for? If you set a	group (via the set_group()
	   method) and supply the following:

	     # well, and you are viewing in IE...
	     $table->modify(table => {
	       cellspacing => 0,
	       rules	   => 'groups',

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

	     # this works with or without setting a group, by the way
	     $table->modify(tbody => {
	       bgcolor => [qw(insert rotating colors here)],


	   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 the	sprintf	docs for further details.


	   Computes subtotals for specified columns. It	is mandatory 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.

	     $scalar = $table->get_col_count()

	   Returns the number of columns in the	table.

	     $scalar = $table->get_row_count()

	   Returns the numbers of body rows in the table.

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

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

		 { header => '', data => [], before => '' }, { ... }, ...

	   Going against the philosophy	of only	select what you	need from the
	   database, this sub allows you to remove whole columns. 'header' is
	   the name of the new column, you will	have to	ucfirst	yourself. It
	   is up to you	to ensure that that the	size of	'data' is the same as
	   the number of rows in the original data set.	'before' can be	an
	   index or the	name of	the column. For	example, to add	a new column
	   to the beginning:

	      $table->add_cols({name=>'New', data=>\@rows, before => 0});

	   add a new column to the end:

	      $table->add_cols({name=>'New', data=>\@rows});

	   or somewhere	in the middle:

	      $table->add_cols({name=>'New', data=>\@rows}, before => 'age'});

	   or combine all three	into one call:

		 {name=>'Foo', data=>\@rows, before => 0},
		 {name=>'Bar', data=>\@rows},
		 {name=>'Baz', data=>\@rows}, before =>	'Bar'},

	      $table->drop_cols([qw(foo	bar 5)];

	   Like	add_cols, drop_cols goes against said 'philosophy', but	it is
	   here	for the	sake of	TIMTWOTDI. Simply pass it an array ref that
	   contains either the name or positions of the	columns	you want to

       new Things with the stuff.

	   Stuff with the things.

       | <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()	to set attributes
	  except <col>,	which uses add_col_tag() instead

       If you have found a bug,	typo, etc. please visit	Best Practical
       Solution's CPAN bug tracker at


       or send mail to <>

       (you got	this far ... you can figure out	how to make that a valid
       address ... and note that i won't respond to bugs sent to my personal
       address any longer)

       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.

       Briac [OeufMayo]	PilprA(C) for the name.

       Mark [extremely]	Mills for patches and suggestions.

       Jim Cromie for presenting the whole spreadsheet idea.

       Stephen Nelson for documentation/code corrections.

       Matt Sergeant for DBIx::XML_RDB.

       Aaron [trs80] Johnson for convincing me into writing add	and drop cols.

       Richard Piacentini and Tim Alexander for	recommending DBIx::Password
       and Apache::DBI compatability and Slaven	Rezic for recommending using

       Perl Monks for the education.


       Jeff Anderson

       Copyright 2017 Jeff Anderson.

       This program is free software; you can redistribute it and/or modify it
       under the terms of the the Artistic License (2.0). You may obtain a
       copy of the full	license	at:


       Any use,	modification, and distribution of the Standard or Modified
       Versions	is governed by this Artistic License. By using,	modifying or
       distributing the	Package, you accept this license. Do not use, modify,
       or distribute the Package, if you do not	accept this license.

       If your Modified	Version	has been derived from a	Modified Version made
       by someone other	than you, you are nevertheless required	to ensure that
       your Modified Version complies with the requirements of this license.

       This license does not grant you the right to use	any trademark, service
       mark, tradename,	or logo	of the Copyright Holder.

       This license includes the non-exclusive,	worldwide, free-of-charge
       patent license to make, have made, use, offer to	sell, sell, import and
       otherwise transfer the Package with respect to any patent claims
       licensable by the Copyright Holder that are necessarily infringed by
       the Package. If you institute patent litigation (including a cross-
       claim or	counterclaim) against any party	alleging that the Package
       constitutes direct or contributory patent infringement, then this
       Artistic	License	to you shall terminate on the date that	such
       litigation is filed.


perl v5.32.0			  2017-01-04		  DBIx::XHTML_Table(3)


Want to link to this manual page? Use this URL:

home | help