Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options

using CGI, DBI and HTML::Template (a mini tutorial with example code)

by gav^ (Curate)
on Feb 09, 2002 at 04:13 UTC ( #144315=perlmeditation: print w/replies, xml ) Need Help??

I find HTML::Template great and in my personal mission to encourage people not to embed HTML in perl scripts, here is a few examples to get you started. I've tried to cover things I didn't get when I first started and could have saved me some work.

Lazy form handling with associate

HTML::Template has a cunning feature which I didn't notice in my first few passes through the documentation. You can give it your CGI object and it will fill in template values with them.


<form action="" method="post"> <p>Your name: <input type="text" name="name" value="<TMPL_VAR NAME=NAME>"></p> <p>Your favorite color: <input type="text" name="color" value="<TMPL_VAR NAME=COLOR>"></p> <p><input type="submit"></p> </form>


<p>Hello, <b><TMPL_VAR NAME=NAME></b>. A little birdy told me your favorite color is <b><TMPL_VAR NAME=COLOR></b>.

use CGI; use HTML::Template; my $cgi = CGI->new; my $template; print $cgi->header; if ($cgi->param('name') && $cgi->param('color')) { $template = 'color2'; } else { $template = 'color1'; } my $t = HTML::Template->new(filename => "$template.tmpl", associate => + $cgi); print $t->output;

Cheap Database Output

You want to output a table but are too lazy to code something? HTML::Template to the rescue! param() needs hashrefs and arrayrefs and the DBI functions selectall_hashref and fetchrow_hashref come in very handy.

Perl code:

use DBI; use CGI; use HTML::Template; my $cgi = CGI->new; my $dbh = DBI->connect('dbi:mysql:xxxx:localhost', 'xxxx', 'xxxx'); my $t = HTML::Template->new(filename => 'db.tmpl'); $t->param(ROWS => $dbh->selectall_hashref('select id, name, price from + products')); $dbh->disconnect; print $cgi->header; print $t->output;

Template code:

<table border="1"> <tr><th>ID</th><th>NAME</th><th>PRICE</th></tr> <TMPL_LOOP ROWS> <tr> <td><TMPL_VAR NAME=ID></td> <td><TMPL_VAR NAME=NAME></td> <td><TMPL_VAR NAME=PRICE></td> </tr> </TMPL_LOOP> </table>

Doing a little bit more...

Perl code:

[snip] my $rows = $dbh->selectall_hashref('select id, name, price from produc +ts'); $dbh->disconnect; my $sum = 0; my $i = 0; foreach (@$rows) { $_->{odd} = $i++ % 2; $sum += $_->{price}; } $t->param(ROWS => $rows, TOTAL => $sum);


<table border="1"> <tr><th>ID</th><th>NAME</th><th>PRICE</th></tr> <TMPL_LOOP ROWS> <tr bgcolor="#<TMPL_IF ODD>666666<TMPL_ELSE>EEEEEE</TMPL_IF>"> <td><TMPL_VAR NAME=ID></td> <td><TMPL_VAR NAME=NAME></td> <td><TMPL_VAR NAME=PRICE></td> </tr> </TMPL_LOOP> </table> <p>The total is <TMPL_VAR NAME=TOTAL></p>

But I need to process my db output!

I often find I need to do a little work on what's in the database and handling it row by row is easier.

Note the array created so I can pass the same thing every time I create a template. I usually have cache on as I am running under mod_perl and this speeds things up a bit, I tend to turn off strict and die_on_bad_params as I'd rather things looked a bit funky than the script die (especially if it isn't me editing it).

Perl code:

use DBI; use CGI; use HTML::Template; my $cgi = CGI->new; my $dbh = DBI->connect('dbi:mysql:xxxx:localhost', 'xxxxx', 'xxxx'); my @tmpl_opts = (cache => 1, die_on_bad_params => 0, associate => $cgi +); my $sth = $dbh->prepare('select id, name, price, saleprice from produc +ts'); my ($id, $name, $price, $saleprice); my $i = 0; my $sum = 0; $sth->execute; $sth->bind_columns(\($id, $name, $price, $saleprice)); while ($sth->fetch) { my $real_price = $price < $saleprice ? $price : $saleprice; $name =~ s/\b(\w)(\w+)\b/\U$1\L$2/g; push @rows, { ID => lc($id), PRICE => $real_price, NAME => $name, +ODD => $i++ %2 }; $sum += $real_price; } $sth->finish; my $t = HTML::Template->new(filename => 'db2.tmpl', @tmpl_opts); $t->param(ROWS => \@rows, TOTAL => $sum); print $cgi->header; print $t->output; $dbh->disconnect;


Hope you liked it :)


Replies are listed 'Best First'.
Re: using CGI, DBI and HTML::Template (a mini tutorial with example code)
by markjugg (Curate) on Feb 12, 2002 at 15:34 UTC
    selectall_hashref() only works in a limited number of versions of DBI (maybe 1.15 through 1.19). For the newest versions, 1.20 and greater I believe, you can get the same functionality that you are looking for like this:

    selectall_arrayref($sql,{ Slice => {} });

    selectall_hashref does something different in the newest versions and your old code will break.

    Here's another way to process your DB output as well:

    my $rows = $DBH->selectall_arrayref($sql,{ Slice=>{} }); for (@$rows) { # add key/value for Pi to every row. $_->{pi} = 3.14; }

    For even lazier form re-filling, look into adding HTML::FillInForm into the mix. Then you don't even have to add the <tmpl> tags into your forms in many cases. I use this in conjuction with HTML::Template. There is as outdated tutorial on using these here. It's outdated because HTML::FormValidator has been superceded by Data::FormValidator.


      Thanks for the tip, I just upgraded from 1.15 to 1.201 and found this broken. Now I'm worried as I have to make sure nobody upgrades DBI and causes all my scripts to break.


        Good Tutorial, though l have to admit l did get caught out my the selectall_hashref and after searching this site, l found an easy solution to it:
        my $table_data = $dbh->prepare($companyquery); $table_data->execute; my $arrayref = $table_data->fetchall_arrayref({}); $template->param(ROWS => $arrayref);
        Hope this helps
Re: using CGI, DBI and HTML::Template (a mini tutorial with example code)
by impossiblerobot (Deacon) on Feb 09, 2002 at 14:44 UTC
    Short but sweet! Good job, gav^. You give a good overview of the power of HTML::Template without getting bogged down in complicated details. This should encourage others to take a look at this module.

    I like the fact that you used a simple example that should be accessible to anyone, but followed up with some DBI information that should be useful even to a more experienced Perl user. Thanks!

    Impossible Robot
by orkysoft (Friar) on Feb 09, 2002 at 21:21 UTC

    I had some trouble with the <TMPL_IF NAME="__ODD__">, but you can work around it as you showed :-)

    Lur: "But if this cape shrinks, consider your species extinct!"

Re: using CGI, DBI and HTML::Template (a mini tutorial with example code)
by badbehaviour (Initiate) on May 20, 2008 at 20:06 UTC
    Still awaiting some tutorials on HTML::Mason

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlmeditation [id://144315]
Approved by root
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (4)
As of 2017-10-19 04:13 GMT
Find Nodes?
    Voting Booth?
    My fridge is mostly full of:

    Results (251 votes). Check out past polls.