Normalizing is the procedure of removing repeated values and storing them in a seperate table. For data result sets, you can't easily worry about this for one reason.

Your result set is a snapshot of your data (if it is constantly changing). Doing it in two queries/two result tables, getting one smaller table to and then again for the rest of your data would be logically nicer. You could generate a dictionary from your first query, and during the result phase, reassociate it to your second query. But you know why you don't usually ever do this?

It's faster most of the time. to do it once and let the database weed out what wouldn't match in the first place. It's O(f()) * 2 if your queries are equaly complex. That's bad. Dividing in two is always good, thus do it only once.

If you were joining on something like, a reference table, it may be faster to keep a constant structure in perl to re-associate back to, in dictionary/hash form. Other than that, it's nicer to get back one result set and proecss it in a neat algorithm, like you tried to do.

---

On to part 2. While your exact code may or may not work, it is the right way of doing things. If your first field changes, just create a new indentation with a new title. This is similar to how SAX works. When you see a close tag (or an open one) change the state of the process to behave a little differently.

The particular problem with your code, is that you create variables scoped for blocks and expect them to cross boundaries. There are tricks, such as creating the reference and then saving it somewhere else, but in this case, i wouldn't advise it.

My advice would be to create a funciton that at least takes the data in, processes it, and then returns back the structure you like.

In this case, in pseudo code, I would try something like this. 


my @track = ();
my %demo = ();
while(my @data = $sth->fetchrow_array())
{
   for( my $x = 0; $x < $#data ; $x++ )
   {
      if( $data[$x] ne $track[$x] )
      {
         print "\t" x $x;
         print "$data[$x]\n";
      }
      @track = @data;
   }
   push( @{$demo{ $data[0] }{ $data[1] }{ $data[2] } .... = $data[widt
+h];
}

[download]
The code won't compile and prolly has a logic error or two, but it should get you started. An exercise best left to the user if you don't like your code.

Play that funky music white boy..

In reply to Re: normalizing results from a db query by exussum0
in thread normalizing results from a db query by punkish

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.