Re: [htmltmpl] HTML::Template: Howto make a inner/outer loop?
Brought to you by:
samtregar
From: Puneet K. <pk...@ei...> - 2004-04-05 23:41:21
|
On Apr 5, 2004, at 2:18 PM, Eivind Hestnes wrote: > Hi, > > Short and to the point. > > 1. I'm using this script to dump information from a database (and it > works, > yes :). > > dump.pl > ----------------------------------------------------------------- > #!/usr/bin/perl > > .. > > sub db_connect { > > my ($result); > > $result = DBI->connect( > "DBI:$DBType:database=$DBName;host=$DBHost", > "$DBUser", "$DBPass", { 'RaiseError' => 0 } ) || &error("Unable to > connect to database"); > > return $result; > > } > > sub db_query { > > my ($result, $query); > > $query = $_[0]; > > $result = &db_connect->prepare($query) || &error("Unable to query > the > database"); > $result->execute(); > > return $result; > > } > > # outer loop > my $query1 = &db_query("SELECT id, title FROM sections"); > while (my $rows1 = $query1->fetchrow_hashref()) { > print "Section: " . $rows1->{title}."\n"; > > my $sectionid = $rows1->{id}; > > # inner loop > my $query2 = &db_query("SELECT title FROM pages WHERE sectionid = > $sectionid"); > while (my $rows2 = $query2->fetchrow_hashref()) { > print "-> Page: " . $rows2->{title}."\n"; > } > > } > ----------------------------------------------------------------- > > 2. Since I have started using HTML::Template on my webpage, I have to > port > this script to get use of a template. > The template looks something like this: > > dump.html > ----------------------------------------------------------------- > <TMPL_LOOP NAME="SECTIONS"> > Section: <TMPL_VAR NAME="TITLE"><br> > <TMPL_LOOP NAME="PAGES"> > -> Page: <TMPL_VAR NAME="TITLE"><br> > </TMPL_LOOP> > </TMPL_LOOP> > > 3. I have tried to make a new script for HTML::Template, but I can't > get the > script working with the template because of the outer/inner loop. > > you are likely going to get a lot of replies. I will just point out a few things -- a TMPL_LOOP is a ref to an array of hashes, where each hash is a key,value pair of fieldname and fieldvalue. The fetchall_arrayref({}) function conveniently does exactly that for you. So, call fetchall_arrayref({}) and pass the result directly to TMPL_LOOP like so -- sub db_query { my ($sql) = @_; my $sth = $dbh->prepare(qq{$sql}); $sth->execute; return $sth->fetchall_arrayref({}); } my $t->param(SECTIONS => &db_query("SELECT id, title FROM sections")); Now, what you are trying to do is build an internal loop of pages for every section. You can do it several different ways, but I prefer to do the db query in one shot using a JOIN as in SELECT s.id, s.title, p.title FROM sections s INNER JOIN pages p ON s.id = p.sectionid WHERE s.id = $sid This way I get all the results in one shot and then I group them in Perl using any one of the several methods discussed earlier in these archives. Search for "normalize" and you might find a complete, working code fragment posted by me a few months ago. The advantage of this approach is that you have to run the db query only once instead of once for every sectionid. Once you have the results, working with arrays and hashes in Perl is pretty much at the speed of the machine, aka, very fast. __END__ A few stylistic comments, fwiw -- In db_connect you don't need to declare $result separately and return it separately. You can do the entire thing on one line without decreasing readability -- sub db_connect { my $dbh = DBI->connect( "DBI:$DBType:database=$DBName;host=$DBHost", "$DBUser", "$DBPass", { 'RaiseError' => 0 } ) || &error("Unable to connect to database"); } When using DBI, I tend to use the handle names that Tim Bunce uses. That makes the intent very clear to me, to other readers of my code, and when comparing it with examples in DBI.pod. So, $dbh, $sth, etc. And, finally, I would probably alias the id and title fields in the database so their purpose it clear as -- SELECT s.id AS sectionid, s.title as sectiontitle, p.title as pagetitle FROM sections s INNER JOIN pages p ON s.id = p.sectionid WHERE s.id = $sid |