Esteemed monks,

For some time I have been working with HTML::Template within CGI::Application. In particular I have been trying to figure out how to populate pop-up menu's and found a few references including aone or two here in the Monastery. One of my favourite references on HTML::Template is the tutorial offered here in the Monastery by jeffa at HTML::Template Tutorial.

There you will find this unusual little note:

Some of the older versions of DBI allowed you to utitize an undocumented feature. dkubb presented it here (dkubb) Re: (2) Outputing data from mySQL query into format for use with HTML::Template. The result was being able to call the DBI selectall_arrayref() method and be returned a data structure that was somehow magically suited for HTML::Template loops, but this feature did not survive subsequent revisions.

I kept looking at it, and referring to my DBI documentation but it made no sense. Well, the exact method that dkubb used and which is referred to by jeffa may no longer be available but the selectall_arrayref (and the fetchall_arrayref) work. Well at least they do in DBI v1.38 (which is pretty darned current!) Now, my fuzzy recollection is that not only is a remark of this nature made by jeffa but also by others which I cannot track down at the moment.

This code fragment (non-functional it has been stripped down!) uses a simple SQL query to populate a pop-up list in a form:

use DBI;
use HTML::Template;

my $dbh = DBI->connect(...);
my $sth = $dbh->prepare('select id,title from movie');
$sth->execute;

my $movies   = $sth->fetchall_arrayref({});
my $template = HTML::Template->new(filehandle => \*DATA);

$template->param(movies => $movies);
print $template->output;

__DATA__
<form>
<select name="movies">
<tmpl_loop movies>
   <option value="<tmpl_var id>"><tmpl_var title></option>
</tmpl_loop>
</select>
</form>
[download]

So maybe we can add the following to our HTML::Tenmplate bag of tricks:

By calling the fetchall_arrayref or selectall_arrayref methods of DBI, and passing it an anonymous hashref we will get our data back as a reference to an array of hash references. Just what we need to pass to HTML::Template.

My thanks to gmax for his fine DBI recipes which under the heading Getting a list of hashes prompted me to re-read all the various documents and try this approach.

jdtoronto

UPDATED replaced my clunky example code with streamlined version from jeffa, thanks jeffa!

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.