Was there a question? Regardless, i just wanted to point out that i did not know about the fetchall_arryref trick until gmax informed me at (jeffa) Re: A quesion about referencing lists. I have since contemplated updating HTML::Template Tutorial, but i haven't because i have instead have been contemplating writing Part 2 (and defecting over to Template). For the record, though, i do spread the gospel when i can. ;)

For me, the brilliance in this technique is only having to specify the column names in two places: in the SQL query and in the template. (Compare this with PostNuke's standard of having to list out the column names like 5 or 6 times in different areas of code ... bleh :/)

UPDATE:
commented out code snippet, it is now in root node

UPDATE:
So that's how you coerce selectall_arrayref! dkubb++ ... i wish i could transfer all of the XP from this node over to yours for that! ;)

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)

jeffa

Was there a question?

um, no! Maybe this should have been a meditation or a snippet. I hope you don't mind that I pinched your very much simplified code example. I was writing this in the heat of a busy morning and just wanted to get it all down before I got interupted again.

Pressure of time has kept me from looking at TT/TT2 so I look forward to your tutorial on that as well, it may influence me for the future. I have got all the TT2 stuff installed but every time I look at the doc's I head straight back to HTML::Template.

John jdtoronto

The one thing that really "won me over" was the WRAPPER directive. I so want this feature in HTML::Template, but i fear that some heavy lifting will be required to add it ... anyways, WRAPPER allows you to contain a "header" and a "footer" in one file. This is incredibly useful. Imaging that you have a skeleton ... oh wait a second! I've already explained this once before to you at Re: HTML::Template question. :D

Go back and look at that example again. You may ask yourself, "well, there is a complete script for the H::T example, but where is the code for TT the example?" First, my apologies for not explaining ... second, try this:

1. save the first template as skeleton.html
2. save the two pages as page1.html and page2.html, respectively
3. type tpage page1.html in your shell
If you have all the TT2 stuff installed correctly that should work just fine. Neat, eh?

If you want a skeleton Perl script to work with, try this one on for size:

Read more... (2 kB)

HTML::Template is simpler and faster, but TT is more fun and extremely powerful. ;)

jeffa

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)

  my $dbh =DBI->connect(
        "dbi:$dbidriver(RaiseError=>1,Taint=>1):dbname=$dbname;host=$d
+bhost", "$dbusername", "$dbpassword", );
   

    my $statement= "select name from names";
    my $sth = $dbh->prepare($statement);
    $sth->execute();

##grab all rows as array ref

    my $site = $sth->fetchall_arrayref;

    $sth->finish;

    $dbh->disconnect;

    my %stuff;

##push everything into a hash

    foreach my $site_in ( @{$site} ) {

        push @{ $stuff{view} },
          {       
            name => $site_in->[0], 
          };

    }
#load file
  my $template = HTML::Template->new(
        filename          => catfile( foo.tmpl ),
        die_on_bad_params => 0
    );
#load in hash
    $template->param( {%stuff} );
#print out
    print $template->output;
[download]

<table>
<tr><td>name</td></tr>
<tmpl_loop name=view>

<tr>           
<td><tmpl_var name=name></td>
</tr>

</tmpl_loop>
[download]

Hi drfrog,

The point is that their is no need to build the list of hashes! You can get it straight from the database. By changing:

# This method returns a reference to an array of array-refs
my $site = $sth->fetchall_arrayref;
[download]

to:

# This method returns a reference to an array of hash-refs
my $site = $sth->fetchall_arrayref( {} );
[download]

Produces a structure which can be directly passed to the TMPL_LOOP, like this:

$template->param( view => $site );
[download]

assuming of course that the one field returned, using your example code, was name.

My reference to dkubb was not to in any way impune what he did, but merely to try and cite the appropriate references as I knew them. The method he used originally was removed and this one obviously added in its place!

jdtoronto

doh!
next time i should try and read the whole thread, and then reply! answering 10 minutes before leaving work didnt help either!

thanks for asking this question, i had a bit of disconnect on this loading hashes straight from the database too!

btw:
i didnt take your references to dkubb as anything negative :)

I believe the technique I was using was removed from DBI about 3 months after my post. It was replaced with a more elegant (IMHO) system that can replicate the same results with newer versions of DBI.

In the earlier example I was trying get DBI to return an array reference of hash references with a single DBI method call. I don't see the need to break something up into multiple steps (prepare/execute/fetchall_arrayref) if DBI provides an all-in-one "utility method" that does these things underneath. Plus, as jeffa mentioned, it cuts down the number of places where you need to specify the names of the columns -- which is convenient if you ever need to make changes.

Here's how I would do it using a more recent version of DBI:

my $statement = q{
  SELECT id
       , title
    FROM movie
};

# $template is an HTML::Template object
$template->param(
  movies => $dbh->selectall_arrayref($statement, { Slice => {} }),
);
[download]