DBI and HTML::Template

[czarfred]

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>

(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!

[kwoff]

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

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

[lachoy]

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( {} );

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

[growlf]

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*

[Anonymous Monk]

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'} ,
];

and use it like this.

$template->param( EMPLOYEE_INFO => $myarrayref);

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

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

[chipmunk]

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

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

There's also fetchall_arrayref, as lachoy explained.