Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

Dynamic HTML::Template Database Template

by jeffa (Bishop)
on Oct 02, 2002 at 16:04 UTC ( #202304=sourcecode: print w/ replies, xml ) Need Help??

Category: CGI Programming
Author/Contact Info jeffa
Description: Have you ever wanted to write an HTML::Template script that would display any database query without having to change the template file? Well, this bit of code attempts to do just that. Just supply a query and optional place holders and the code will do the rest. Of course, changing the layout of the table is a manual process, but the number and names of the columns are dynamically supplied.

NULL characters and HTML encodings are taken care of (via HTML::Entities for the latter), but i have only tested this code with the MySQL relational database server. Comments and suggestions are always welcome.

use strict;
use warnings;
use DBI;
use HTML::Entities;
use HTML::Template;

my $dbh = DBI->connect(
   qw(DBI:vendor:database:host user pass),
   {RaiseError=>1}
);

# change these two vars to suite your needs:
my @arg = (42);
my $sql = 'select foo,bar from baz where qux = ?';

my $sth = $dbh->prepare($sql);
$sth->execute(@arg);

my $stmt  = $sth->{Statement};
my $field = $sth->{NAME};
my $row   = $sth->fetchall_arrayref();
my $tmpl  = HTML::Template->new(filehandle => \*DATA);

$tmpl->param(
   query  => $stmt,
   fields => [
      map {
         { field => ucfirst lc $_ }
      } @$field 
   ],
   rows => [
      map {{ cols => [ 
         map {
            { data => defined $_ ? encode_entities $_ : ' ' }
         } @$_ 
      ] }} @$row
   ],
);

print $tmpl->output;

$dbh->disconnect;

__DATA__
<html>

<head>
<title>Dynamic table template</title>
<style type="text/css">
<!--
   table { border-style: outset; border-width: thin; width: 70% }
   th    { border-style: ridge; }
   td    { border-style: inset; }
-->
</style>
</head>

<body>

<table>
  <thead>
  <caption><tmpl_var query></caption>
  <tr>
  <tmpl_loop fields>
     <th><tmpl_var field></th>
  </tmpl_loop>
  </tr>
  </thead>

  <tbody>
  <tmpl_loop rows>
  <tr>
     <tmpl_loop cols>
        <td><tmpl_var data></td>
     </tmpl_loop>
  </tr>
  </tmpl_loop>
  </tbody>
</table>

</body>
</html>

Comment on Dynamic HTML::Template Database Template
Download Code
Replies are listed 'Best First'.
Re: Dynamic HTML::Template Database Template
by diotalevi (Canon) on Oct 02, 2002 at 17:13 UTC

    Nice. The only thing I really want to change about that is that double map on $row. I'm thinking that I'd really like to alter the arrays in place instead of making a new copy to (hopefully) keep the memory requirements lower.

    # Jeffa rows => [ map {{ cols => [ map { { data => defined $_ ? encode_entities $_ : '&nbsp;' } } @$_ ] }} @$row ], # becomes -{diotalevi}-> my ($row, $value); for $row (@$rows) { for $value(@$row) { $value = { data => defined($value) ? encode_entities($value) : '&nbsp;' }; } $row = { cols => $row }; } # and later rows => \@rows

    Update 0: I initially forgot to create the hash and array refs inline. My bad
    Update 1: $row = { cols => $row } used to be $row = [ cols => $row ]
    Update 2: rows => $rows was rows => \@rows

    __SIG__
    printf "You are here %08x\n", unpack "L!", unpack "P4", pack "L!", B::svref_2object(sub{})->OUTSIDE

Back to Code Catacombs

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: sourcecode [id://202304]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (16)
As of 2015-07-30 12:29 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (271 votes), past polls