Fetchall_Answer Returns

[growlf]

In the spirit of using HTML::Template with CGI and DBI, I am wanting to output a table that is of unknown dimensions at runtime, while still keeping the CGI portion and HTML portion as separate as possible so as to allow the 2 developer sets to work independently (and to ease maintenance as well, since we seem to have a plethora of  *gack* FP-Happy html types in our company and far too few Perl writers). 

I once wrote an article on using the HTML::Template with DBI that was a good answer for the time - but now I think I must either create my own solution, or find a better one that already exists. 

An example of my needs:

given a dataset that can be any number of rows by any number of columns, output the appropriate HTML table in the HTML template and have it render it without resorting to embedding any static HTML into my CGI Script.

The template handles a static sized dataset thusly (and quite nicely too):

my $response = HTML::Template->new(
  filename => $filename,
  path => "/$config{'template_dir'}/$theme/",
  die_on_bad_params => 0,
  global_vars => 1,
  associate => $request_cgi,
  loop_context_vars => 1,
  filter => {sub=> \&compress,format=> 'scalar'},
);
my $sqlquery = 'select t.field_one as DATA1, t.field_two as DATA2 from
+ table_one t order by t.field_one ASC';
my $sth = exec_query($sqlquery) or die("Wacka!!");
my $arrayref = $sth->fetchall_arrayref({});
$response->param(LOOPTEST => $arrayref);

along with this HTML:

<!--BEGIN Dynamic data table-->
<TMPL_UNLESS NAME=LOOPTEST>
  <font color=red>No records found.</font>
</TMPL_UNLESS>
<TMPL_LOOP NAME=LOOPTEST>
  <TMPL_IF NAME="__FIRST__">
  <table>
    <tr>
      <th bgcolor="#80D0FF"><font color="navy">Data 1</font></th>
      <th bgcolor="#80D0FF"><font color="navy">Data 2</font></th>
    </tr>
  </TMPL_IF>
  <TMPL_IF NAME="__ODD__">
    <tr>
      <td><a href="<TMPL_VAR NAME=SYSTEM_URL>"><TMPL_VAR NAME=DATA1></
+a></td>
      <td><TMPL_VAR NAME=DATA2></td>
    </tr>
  <TMPL_ELSE>
    <tr bgcolor="silver">
      <td bgcolor="silver"><a href="<TMPL_VAR NAME=SYSTEM_URL>/<TMPL_V
+AR NAME=DATA1>"><TMPL_VAR NAME=DATA1></a></td>
      <td bgcolor="silver"><TMPL_VAR NAME=DATA2></td>
    </tr>
  </TMPL_IF>
  <TMPL_IF NAME="__LAST__">
  </table>
  </TMPL_IF>
</TMPL_LOOP>
<!--END Dynamic data table-->

Is there a way to do this with a dynamic dataset, with changing numbers of both rows and columns?

[rob_au]

Short answer - This is fairly straight-forward, but you need to shift from using the fetchall_* methods to build the nested <tmpl_loop></tmpl_loop> data structure in a named fashion.

Long answer - The HTML::Template module allows for nesting of <tmpl_loop></tmpl_loop> loop structures within the template file which in turn allows you to "dynamically" resize your output table accordingly. For example, consider the template file below ...

<table>
  <tmpl_loop name="row">
  <tr>
    <tmpl_loop name="cell">
    <td align="left"><tmpl_var name="cell"></td>
    </tmpl_loop>
  </tr>
  </tmpl_loop>
</table>

... And the following now tested and working Perl code (updated 2002/03/04) ...

my $sth = $dbh->prepare(qq/ SELECT * FROM table /);
$sth->execute;

my @row;
while (my $row = $sth->fetchrow_arrayref) {
    my @cell;
    foreach my $cell ( @{$row} ) {
        push @cell, { 'cell' => $cell };
    }
    push @row, { 'cell' =>  \@cell};
}
$html->param(
    'row'  =>  \@row
);

$sth->finish;

In this example, an array-of-array-of-hashes has been built representing the nested loops within the HTML template file - When writing code which builds these data structures, I find it exceptionally useful to call upon Data::Dumper to visualise the data structure during development.

 

perl -e 's&&[e-mail]&&&split/[@.]/&&s&.com.&_&&&print'

[mattr]

This is probably not what you are asking for, but I have to say that I have some large dynamic tables running fine with HTML::Table which lets you build tables programatically.

I think this might make for a faster fetch since you could use fetchall* methods with it. I have not timed this against Template.

[jeffa]

Personally, I think rob_au's suggestion is the best way to go, but if are you still using HTML::Table then please check out DBIx::XHTML_Table. I think you will be pleased with it. ;)

jeffa

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)