Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

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

by jandrew (Hermit)
on Oct 22, 2012 at 17:45 UTC ( #1000392=note: print w/ replies, xml ) Need Help??


in reply to Re^2: Pivoting parts of a database table into an HTML table
in thread Pivoting parts of a database table into an HTML table

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.

#! perl use Modern::Perl; my $data; #load the data my $last_date ; while (<DATA>){ my ($date, $type, $word) = split(/\s+\|\s+/, $_); chomp $word; # Check for a new date - base state if( $last_date and $date ne $last_date ){ print_out( $last_date, $data ); $data = {}; } if( exists $data->{$type} ){ $data->{$type} .= ', ' . $word; }else{ $data->{$type} = $word; } $last_date = $date; } # Pick up the last line print_out( $last_date, $data ); sub print_out{ my ( $date, $data ) = @_; print $date . ' | ' . ($data->{woody} // '') . ' | ' . ($data->{tinny} // '') . "\n"; } __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

Produces this output

28 Sep (Fri) | caribou | litterbin 29 Sep (Sat) | wasp, yowling, gorn | 30 Sep (Sun) | intercourse, bound, pert | newspaper 01 Oct (Mon) | ocelot, concubine | antelope 02 Oct (Tue) | vole, sausage | 03 Oct (Wed) | | recidivist, tit

TIMTOWTDI!

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


Comment on Re^3: Pivoting parts of a database table into an HTML table
Select or Download Code
Re^4: Pivoting parts of a database table into an HTML table
by Anonymous Monk on Oct 23, 2012 at 07:20 UTC

    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.

      You could use type as a state change point but your column allocation would fail for 03 Oct (Wed) since the first type was not available that day. If you reversed the type sort order then you would fail on 29 Sep (Sat) and 02 Oct (Tue) for the same reason. To allocate the correct content to the correct column then you need to pre-define type names applied to the column and still store both sets of type content until the date changed. Doing that you loose any (minor) memory advantage gained from recursively parsing the type. You would also have problems if three types were defined later and you only wanted to display two.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1000392]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (7)
As of 2014-07-14 05:42 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    When choosing user names for websites, I prefer to use:








    Results (255 votes), past polls