Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

Dynamic HTML::Template Database Template

by jeffa (Bishop)
on Oct 02, 2002 at 16:04 UTC ( [id://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>
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

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others avoiding work at the Monastery: (4)
As of 2025-06-24 16:22 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?
    erzuuliAnonymous Monks are no longer allowed to use Super Search, due to an excessive use of this resource by robots.