Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

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.

color1.tmpl:

<form action="color.pl" 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>

color2.tmpl:

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

color.pl:

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

Template:

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

Fin

Hope you liked it :)

gav^

Comment on using CGI, DBI and HTML::Template (a mini tutorial with example code)
Select or Download Code
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
Thanks!
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 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.

    -mark

      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.

      gav^

        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 badbehaviour (Initiate) on May 20, 2008 at 20:06 UTC
    Still awaiting some tutorials on HTML::Mason

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (8)
As of 2015-07-05 21:33 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 (68 votes), past polls