http://qs1969.pair.com?node_id=126919

czarfred has asked for the wisdom of the Perl Monks concerning the following question:

Ive been working on this for a while now, and I cant seem to figure it out.

I decided that I wanted to use HTML::Template for a online phone application that Im doing. I need to basically take rows of data from a database, and pass the values to HTML::Template's param() function, in a way that in my .tmpl file, I can go:

<TMPL_LOOP NAME=EMPLOYEE_INFO>
         Name: <TMPL_VAR NAME=NAME> <P>
         Job: <TMPL_VAR NAME=JOB> <P>
        <P>
</TMPL_LOOP>

[download]

(Stolen from the docs). My problem is that HTML::Template's param() function expects an arrayref, and, even with DBI's fetchrow_arrayref() function, I dont see how I can get *all* the rows into a single arrayref that I can pass to HTML::Template's param().

Thanks for any and all help!

Replies are listed 'Best First'.
Re: DBI and HTML::Template
by kwoff (Friar) on Nov 22, 2001 at 05:41 UTC
    Here's how I did it (assuming I pasted the relevant stuff correctly :). 
    $tmpl->param('loop' => db_get_loop());


sub db_get_loop {
        my ($dbh, $sql, $sth, @loop_info, $limit,
                $last_update, $city, $zipcode, $name,
                $low, $high, $date, $forecast,
                $forecast_description);

        $dbh = db_connect();

        $sql = qq{SELECT last_update,city,zipcode,name,low,high,
                                date,forecast,forecast_description
                          FROM $TABLE
                          ORDER BY last_update DESC, date
                          LIMIT $limit};
        $sth = $dbh->prepare($sql);
        $sth->execute();
        $sth->bind_columns(\($last_update, $city, $zipcode, $name,
                                           $low, $high, $date,
                                           $forecast, $forecast_descri
+ption));
        while ($sth->fetchrow_arrayref()) {
                my (%loop);

                # Set TMPL_VARs for each iteration
                # in the <TMPL_LOOP NAME="${region}_headlines_loop"> l
+oop
                $loop{'last_update'} = $last_update;
                $loop{'city'} = $city;
                $loop{'zipcode'} = $zipcode;
                $loop{'name'} = $name;
                $loop{'low'} = $low;
                $loop{'high'} = $high;
                $loop{'date'} = $date;
                $loop{'forecast'} = sprintf('%02d', $forecast);
                $loop{'forecast_description'} = $forecast_description;

                # Push this row onto loop
                push @loop_info, \%loop;
        }
        $sth->finish();
        $dbh->disconnect();

        return \@loop_info;
}

    [download]

    You could put all those variables in a hash, instead. That's just how I happened to do it.

[reply]
[d/l]
Re: DBI and HTML::Template
by lachoy (Parson) on Nov 22, 2001 at 09:30 UTC

    With more recent versions of DBI, you can do: 

     my $sth = $dbh->prepare( $sql );

 # Fetch rows as arrayrefs
 my $rows_as_arrayrefs = $sth->fetchall_arrayref;

 # OR fetch all rows as hashrefs
 my $rows_as_hashrefs  = $sth->fetchall_arrayref( {} );

    [download]

    Both invocations return an arrayref, but the first returns an arrayref of arrayrefs, the second an arrayref of hashrefs.

    Chris
    M-x auto-bs-mode

[reply]
[d/l]
Re: DBI and HTML::Template
by growlf (Pilgrim) on Nov 22, 2001 at 13:49 UTC
    Well, you may want to look at this very simple solution. I have gone through this before - looping is not necesary. There IS an easy/clean/simple way. Honest.

    *G*
[reply]
Re: DBI and HTML::Template
by Anonymous Monk on Nov 22, 2001 at 08:41 UTC
    HTML::Template->param expects an array reference of hash references. So you would need to create a structure like this 
    $myarrayref = [ 
   { NAME => 'Bob' , JOB => 'Programmer'} ,
   { NAME => 'Bill' , JOB => 'Manager'} ,
];

    [download]
    and use it like this. 
    $template->param( EMPLOYEE_INFO => $myarrayref);

    [download]
    To do this using DBI you would want to do something like the following. First fetch each row as a hash reference then push each one of these onto a array reference. Note: you will need to dereference the array reference hence the @$myarrayref. 
    # Create statement handle
$sth = $dbh->prepare($somesql);

# execute statement handle
$sth->execute();

# loop through rows
while ($hashref = $sth->fethrow_hashref()) {
   push @$myarrayref , $hashref;
}

# clean up statement handle
$sth->finish();

    [download]
    Once you have the array reference of hash references you can pass this to the param method of HTML::Template like so. 
    $template->param(EMPLOYEE_INFO => $myarrayref);

    [download]
[reply]
[d/l]
[select]
      # loop through rows
while ($hashref = $sth->fethrow_hashref()) {
   push @$myarrayref , $hashref;
}

      [download]
      Be aware that the documentation for DBI specifically forbids using fetchrow_hashref in that way:
      Currently, a new hash reference is returned for each row. This will change in the future to return the same hash ref each time, so don't rely on the current behaviour.
      Although your code will work with existing versions of DBI, in some future version it will break with every reference in the array pointing to the same hash, containing the last row fetched. Instead, you should make sure to create a new hash for each row: 
      # loop through rows
while ($hashref = $sth->fetchrow_hashref()) {
   push @$myarrayref , { %$hashref };
}

      [download]
      There's also fetchall_arrayref, as lachoy explained.
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom