[htmltmpl] HTML::template with DBI (dynamic)

[Gerard t. H. <g.t...@pu...>]

Hello HTML::Template users,

I am working with DBI as a connection to a MySQL database and want
query-results to show with a template. However I would like to have one
general template file which can show all my query results. Maybe this is too
much a general Perl question about datastruct, therefore I apologize if this
is not the right place to ask.

So I have created a template (just a section) where I first have a have for
the columsheadings (columsnames) and then two loops nested for the data
(loop for columns and a loop for rows). See the TEMPLATE section below. So
far so good. This works fine. However my Perl code is quite bad (I think)
because I fetch everything from the database and then 're-order' it for the
template. Does anyone have any experience with this and knows how I can
improve my code? I' willing to learn so all suggestions to the code are
welcome !

Thanks in advance and kind regards!

Gerard ter Haar

--- Perl code ---
sub WSI_query_user {

	my $self = shift;
	my $q=$self->query();

	my $dbh=$self->param('mydbh');
 	my %varhash;

	my $query = "select WS, LastScan from recentdata
				where UCase(username) = UCase('$user')";

	my $sth = $dbh->prepare($query);
	$sth->execute(); 

	my $field;
	my @rowarray = ();
	foreach $field (@{$sth->{NAME}}) {
		my $fieldhash={};
		$fieldhash->{'rowval'}=$field;
		push(@rowarray, $fieldhash); 
	}
	$varhash{ 'head_loop' }=\@rowarray;

	my $array_ref;
	my @looparray;
	while ( $array_ref = $sth->fetchrow_arrayref ) {

		my %loophash;
		my @row2array = ();
		foreach $field (@$array_ref) {
			my $fieldhash={};
			$fieldhash->{'rowval'}=$field;
			push(@row2array, $fieldhash); 
		}

		$loophash{ 'viewrow_loop' }=\@row2array;
		push(@looparray, \%loophash);
	}
	$varhash{ 'view_loop' }=\@looparray;

	return &display_template( $self, 'qry_results_std.html', \%varhash
);
}
-- cut ---

--- TEMPLATE ---
<table width="100%" border=0 cellspacing=0 cellpadding=0>
  <tr class=menubody>
    <td width="5%"></td>
    <td width="90%"><br>
	  <p><center>
	    <table width=95% border=1>
		<tr>
		  <!-- TMPL_LOOP NAME="head_loop" -->
		  <th><!-- TMPL_VAR NAME="rowval" --></th>
		  <!-- /TMPL_LOOP -->
        		</tr>
		<!-- TMPL_LOOP NAME="view_loop" -->
		  <tr>
		    <!-- TMPL_LOOP NAME="viewrow_loop" -->
			<td><!-- TMPL_VAR NAME="rowval" --></td>
			<!-- /TMPL_LOOP -->
		  </tr>
		<!-- /TMPL_LOOP -->
		</table>
		<br>
		</center>
	  </p>
    </td>
	<td width="5%"></td>
  </tr>
</table>
--- cut ---