Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

Re: Pivoting parts of a database table into an HTML table

by Athanasius (Chancellor)
on Oct 22, 2012 at 13:20 UTC ( #1000337=note: print w/replies, xml ) Need Help??

in reply to Pivoting parts of a database table into an HTML table

Here is a script which collates the data and produces the desired compact view:

#! perl use strict; use warnings; use feature 'switch'; { my %h; populate (\%h); stringify (\%h); print_view(\%h); } sub populate { my ($h) = @_; while (<DATA>) { my ($date, $type, $word) = split /\s+\|\s+/; chomp $word; unless (exists $h->{$date}) { $h->{$date}{woody_words} = []; $h->{$date}{tinny_words} = []; } given ($type) { when ('woody') { push @{ $h->{$date}{woody_words} }, $word +; } when ('tinny') { push @{ $h->{$date}{tinny_words} }, $word +; } default { warn "Datum '$word' of unknown type '$typ +e'"; } } } } sub stringify { my ($h) = @_; for (keys %$h) { $h->{$_}{woody_str} = join(', ', sort @{ $h->{$_}{woody_words} + }); $h->{$_}{tinny_str} = join(', ', sort @{ $h->{$_}{tinny_words} + }); } } sub print_view { my ($h) = @_; my $max = 0; for (keys %$h) { my $woody_str = $h->{$_}{woody_str}; my $new_length = length $woody_str; $max = $new_length if defined $woody_str && $new_length > $max +; } printf " date | %-*s | tinny words\n", $max, 'woody words'; printf "-------------+-%s-+-------------\n", '-' x $max; printf "%s %-*s %s\n", $_, $max, $h->{$_}{woody_str}, $h->{$_}{ +tinny_str} for sort { cmp_dates() } keys %$h; } { my %months; BEGIN { %months = (Jan => 1, Feb => 2, Mar => 3, Apr => 4, May => 5, Jun => 6, Jul => 7, Aug => 8, Sep => 9, Oct => 10, Nov => 11, Dec => 12); } sub cmp_dates { my ($day_a, $mon_a) = $a =~ /^(\d{1,2}) (\w{3})/; my ($day_b, $mon_b) = $b =~ /^(\d{1,2}) (\w{3})/; return ($months{$mon_a} < $months{$mon_b}) ? -1 : ($months{$mon_a} > $months{$mon_b}) ? +1 : ($day_a < $day_b) ? -1 : ($day_a > $day_b) ? +1 : 0; } } __DATA__ 28 Sep (Fri) | woody | caribou 28 Sep (Fri) | tinny | litterbin 29 Sep (Sat) | woody | wasp 29 Sep (Sat) | woody | yowling 29 Sep (Sat) | woody | gorn 30 Sep (Sun) | woody | intercourse 30 Sep (Sun) | woody | bound 30 Sep (Sun) | woody | pert 30 Sep (Sun) | tinny | newspaper 01 Oct (Mon) | woody | ocelot 01 Oct (Mon) | woody | concubine 01 Oct (Mon) | tinny | antelope 02 Oct (Tue) | woody | vole 02 Oct (Tue) | woody | sausage 03 Oct (Wed) | tinny | recidivist 03 Oct (Wed) | tinny | tit


23:09 >perl date | woody words | tinny words -------------+--------------------------+------------- 28 Sep (Fri) caribou litterbin 29 Sep (Sat) gorn, wasp, yowling 30 Sep (Sun) bound, intercourse, pert newspaper 01 Oct (Mon) concubine, ocelot antelope 02 Oct (Tue) sausage, vole 03 Oct (Wed) recidivist, tit 23:10 >

Of course, the really interesting question is: How do you distinguish words which are ‘woody’ from those which are ‘tinny’?  ;-)

Hope this helps,

Updates: Minor code improvements.

Athanasius <°(((><contra mundum

Replies are listed 'Best First'.
Re^2: Pivoting parts of a database table into an HTML table
by Anonymous Monk on Oct 22, 2012 at 15:16 UTC

    Hmm... looks like I forgot to mention that using the iterator would be nice, rather than slurping it all into a hash. The iterator also guarantees the order (dates in order, woody words before tinny), being from a database and all. Which would have much reduced the sorting need I hope.

    Anyway, I've yet to study your code properly, but right now I'm thinking of wrapping my iterator into a second iterator that buffers a day's worth of words. (Dominus's book seems to have had an effect on me.) It's not the nicest answer but one of the easier ones I guess.

      Athanasius's solution is the traditional perl solution for the problem you presented. In other words put the data in a hash (to organize the pivot) and then post process the output.

      If you are trying to leverage some of the databases functionality as you indicated in your second post then you may want to consider $hash_ref = $sth->fetchall_hashref(); rather than the database sort.

      If the goal is to use chapter 4 of Dominus's book to minimize memory overhead then you really just have a recursive problem where you need to identify your base state. (a change of date) You might find the following useful in that case. The data must be pre-sorted by date for this to work. Width formatting is not possible in this output since the data will be printed prior to a test of all rows. That shouldn't be a problem for your suggested goal of web output.


      Update1: changed for to while to honor the iterator concept

      Update2: I just noticed I didn't pick up the last line (no base state test when the query ends. (fixed))

      Update3: fetchrow_hashref is a better fit and can be used with sort

        Thank you, that helped a lot. I guess my problem was trying to force two state change points (date and type), which makes quite a bit sense but apparently introduces too much complexity I can't deal with.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1000337]
[Eily]: nope, Nodelet Settings doesn't let you move the XP Nodelet, CSS might
[marinersk]: That would mitigate the distraction/jangle issue, but then the information wouldn't be easy to find when it is populated. Plus, I don't currently see a way to move it, but I'm not done poking around on that point yet.
[marinersk]: Ah, you beat me to it.

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (9)
As of 2017-05-29 14:11 GMT
Find Nodes?
    Voting Booth?