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