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

Greetings, Venerable Monks.

I'm writing a simple application, using CGI::App, DBI, HTML::Template and Config::IniFiles, a combination which I cannot praise enough. Right now, however, I'm facing a problem, and I can't find a decent solution.

I get the data from the table, and I print it out, as it is. What I have is a list of hashes, I pass it to HTML::Template and it produces a table that looks like this (actually the timestamp is '2008-12-28 01:15:00+01', I've simplified things a bit):

 
+----------+--------+-------+
|  RTIME   |    DEV | VALUE |
+----------+--------+-------+
| 08-12-28 |     25 |    13 |
+----------+--------+-------+
| 08-12-28 |     27 |    10 |
+----------+--------+-------+
| 08-12-28 |     32 |    43 |
+----------+--------+-------+
| 08-12-29 |     25 |     2 |
+----------+--------+-------+
| 08-12-29 |     27 |     5 |
+----------+--------+-------+
| 08-12-29 |     32 |    17 |
+----------+--------+-------+

[download]

But it's not the output I'm after. I would like to print out a table that shows in one row all the data from a given timestamp, so when (as in the above example) there are three devices, the table should look like this:

+----------+--------+--------+--------+
|  RTIME   |     25 |     27 |     32 |
+----------+--------+--------+--------+
| 08-12-28 |     13 |     10 |     43 |
+----------+--------+--------+--------+
| 08-12-29 |      2 |      5 |     17 |
+----------+--------+--------+--------+

[download]

The number of devices is not always three. But in every case, it can be read from the config file (it is predetermined). However, there may occur a situation when a device crashes and it doesn't transmit the value (so, for example, on a given time, 4 out of 5 devices have their rows in a table).

My code (the runmode subroutine that gives me the first table) looks like this:

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

    my $DEVICE     = $q -> param("device");
    my $TIME_START = "2008-12-28";
    my $TIME_STOP  = "2008-12-29";

    my $device_id       = hex($self -> config -> val($DEVICE, 'divis_i
+d'));
    my $sth = $self -> dbh -> prepare (
                                       "SELECT rtime, dev, value FROM 
+db_1361_asc
                                       WHERE device_id='$device_id' AN
+D rtime>'$TIME_START' AND rtime<'$TIME_STOP'
                                       ORDER BY rtime,dev"
                                       );
    $sth -> execute();
    my $raw_loh = $sth -> fetchall_arrayref({});
    $sth -> finish();

    my $template = $self -> load_tmpl('show_details.tmpl');
    $template -> param( TABLE_HEADERS => [
                                          { VALUE => 'RTIME' },
                                          { VALUE => 'DEV' },
                                          { VALUE => 'VALUE' }
                                          ]
                        );

    $template -> param( RAW_DATA => $raw_loh );
    return $template -> output();
}

[download]

I think I should try something that reads the data from the database row by row, and pushes the value into a specific field in a structure that is a list of hashes within a list of hashes (the template will contain a TMPL_LOOP for column data within a TMPL_LOOP for the row data, because of the variable device number). But the structure itself is a bit too complex for me to figure it out (it's my first attempt at something beyond a list of lists). I'd be very thankful if someone could explain to me how it should be done.

Regards,
Luke

Replies are listed 'Best First'.
Re: Creating list of hashes within a list of hashes (DBI and HTML::Template)
by sflitman (Hermit) on Dec 29, 2008 at 13:34 UTC
    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

[reply]
[d/l]

      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]

        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]

Back to Seekers of Perl Wisdom