Re: Creating list of hashes within a list of hashes (DBI and HTML::Template)

I usually accumulate info in a fetchrow while loop, it is easier for me conceptually to work with the data row by row:

...  
$sth->execute;
my (%rows,%cols,$rtime,$dev,$value);
while (($rtime,$dev,$value)=$sth->fetchrow) {
   $rows{$rtime}{$dev}=$value;
   $cols{$dev}++;
}
$sth->finish if $sth->{Active};  # really don't need finish if you got
+ all the data
# now column headers can be computed
my @cols=sort keys %cols;
map { $cols{$cols[$_]}=$_+1 } 0..$#cols;  # get indexes
my @headers=('RTIME',sort keys %cols);
my @row;
print join("\t",@headers),"\n";
# and rows can be extracted (empty cells where a dev wasn't seen for a
+ particular rtime)
for $rtime (sort keys %rows) {
   $row[0]=$rtime;
   map { $row[$cols{$_}]=$rows{$rtime}{$_} } keys %{$rows{$rtime}}; 
   print join("\t",@row),"\n";
}
[download]

You would need to plug the data into HTML::Template, that I'm not as familiar with but it doesn't look hard.

HTH,
SSF

Comment on Re: Creating list of hashes within a list of hashes (DBI and HTML::Template) Download Code

Replies are listed 'Best First'.
Re^2: Creating list of hashes within a list of hashes (DBI and HTML::Template) by 1Nf3 (Pilgrim) on Dec 29, 2008 at 14:34 UTC
The concept looks great, thank you very much. Right now, I'm trying to adapt it to my actual data, and combine it with HTML::Template. I'll post the code when the combination works. Again, thanks. Luke	[reply]
Re^3: Creating list of hashes within a list of hashes (DBI and HTML::Template) by jeffa (Bishop) on Dec 29, 2008 at 14:55 UTC
I don't recommend that concept, actually. `HTML::Template` works great with `DBI`'s `selectall_arrayref`. For example, something like this untested alteration of your code: `my $sth = $self->dbh->selectall_arrayref(" SELECT rtime, dev, value FROM db_1361_asc WHERE device_id = ? AND rtime > ? AND rtime < ? ORDER BY rtime,dev ", {Slice =>{}} $device_id, $TIME_START, $TIME_STOP ); my $template = $self->load_tmpl( 'show_details.tmpl' ); $template -> param( TABLE_HEADERS => [ map {{ VALUE => uc($_) }} @{$sth->{'NAME'}} ], RAW_DATA => $sth, ); return $template -> output();` [download] 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)	[reply] [d/l] [select]