Loading ...
Sorry, an error occurred while loading the content.
 

Problem with looping and references

Expand Messages
  • Jonathan Mangin
    I have a query that returns data like this: date time class type val1 val2 time_format(time, %H%i ) 2005-08-01 04:02:00 A O
    Message 1 of 5 , Dec 6, 2005
      I have a query that returns data like this:

      date time class type val1 val2 time_format(time,'%H%i')
      2005-08-01 04:02:00 A O 95 .006 0402
      2005-08-01 06:08:00 A S 92 0608
      2005-08-01 07:30:00 A S 124 0730
      2005-08-01 15:00:00 A S 72 1500
      2005-08-01 18:32:00 A S 144 1832
      2005-08-01 21:37:00 A S 114 2137
      2005-08-01 06:15:00 B I 18 10 0615
      2005-08-01 11:27:00 B I 11 1127
      2005-08-01 17:57:00 B I 22 11 1757
      2005-08-02 04:00:00 A O 94 .006 0400
      2005-08-02 06:09:00 A S 91 0609
      2005-08-02 07:31:00 A S 128 0731
      2005-08-02 08:00:00 A O 128 .008 0800
      2005-08-02 15:08:00 A S 70 1508
      2005-08-02 18:38:00 A S 143 1838
      2005-08-02 21:45:00 A S 113 2145
      2005-08-02 06:17:00 B I 18 9 0617
      2005-08-02 11:24:00 B I 11 1124
      2005-08-02 17:58:00 B I 22 10 1758

      As each row is fetched I want to compare $row[0] to $date1.
      If true, I want to add that row to %vals. When all 2005-08-01 rows
      have been fetched I want to populate @temp with ($row[0], \%vals),
      push [@temp] onto @data, increment $date1, and do it all again with
      2005-08-02...

      Here's some non-working code:

      my $date1 = Date::Simple->new($bdate);
      # my $date2 = Date::Simple->new($edate);

      my %vals = ();
      my @temp = ();
      my @data = ();
      while (my @row = $sth->fetchrow_array) {
      if ($row[0] eq $date1) {
      $vals{$row[6]} = [substr($row[1],0,5), $row[3], $row[4], $row[5]];
      next;
      }
      @temp = ($row[0], \%vals);
      push @data, [@temp];
      # (%vals, @temp) = ();
      $date1 = $date1->next;
      }

      I've been able to get one day's data, or all days under a single date.
      There must be several better ways to go about this. Any help would
      be appreciated.

      Thanks,
      Jon
    • merlyn@stonehenge.com
      ... Jonathan I have a query that returns data like this: Jonathan date time class type val1 val2 time_format(time, %H%i ) Jonathan
      Message 2 of 5 , Dec 6, 2005
        >>>>> "Jonathan" == Jonathan Mangin <jon.mangin@...> writes:

        Jonathan> I have a query that returns data like this:
        Jonathan> date time class type val1 val2 time_format(time,'%H%i')
        Jonathan> 2005-08-01 04:02:00 A O 95 .006 0402
        Jonathan> 2005-08-01 06:08:00 A S 92 0608
        Jonathan> 2005-08-01 07:30:00 A S 124 0730
        Jonathan> 2005-08-01 15:00:00 A S 72 1500
        Jonathan> 2005-08-01 18:32:00 A S 144 1832
        Jonathan> 2005-08-01 21:37:00 A S 114 2137
        Jonathan> 2005-08-01 06:15:00 B I 18 10 0615
        Jonathan> 2005-08-01 11:27:00 B I 11 1127
        Jonathan> 2005-08-01 17:57:00 B I 22 11 1757
        Jonathan> 2005-08-02 04:00:00 A O 94 .006 0400
        Jonathan> 2005-08-02 06:09:00 A S 91 0609
        Jonathan> 2005-08-02 07:31:00 A S 128 0731
        Jonathan> 2005-08-02 08:00:00 A O 128 .008 0800
        Jonathan> 2005-08-02 15:08:00 A S 70 1508
        Jonathan> 2005-08-02 18:38:00 A S 143 1838
        Jonathan> 2005-08-02 21:45:00 A S 113 2145
        Jonathan> 2005-08-02 06:17:00 B I 18 9 0617
        Jonathan> 2005-08-02 11:24:00 B I 11 1124
        Jonathan> 2005-08-02 17:58:00 B I 22 10 1758

        Jonathan> As each row is fetched I want to compare $row[0] to $date1.
        Jonathan> If true, I want to add that row to %vals.

        What does it mean to "add a row" to a hash? What key would you use?
        Do you mean an array instead?

        Jonathan> When all 2005-08-01 rows
        Jonathan> have been fetched I want to populate @temp with ($row[0], \%vals),
        Jonathan> push [@temp] onto @data, increment $date1, and do it all again with
        Jonathan> 2005-08-02...

        I *think* I understand, and it'd be faster to build a hash keyed
        on the first column, then flatten that hash to an list of arrays
        at the final step:

        my %temp;
        while (my @row = GET_A_ROW) {
        push @{$temp{$row[0]}}, [@row[2..6]];
        }
        my @result = map [$_, $temp{$_}], sort keys %temp;

        That will create:

        @result =
        ["2005-08-01", [
        ["A", "O", 95, 0.006, "0402"],
        ["A", "S", 92, undef, "0608"],
        ["A", "S", 92, undef, "0608"],
        ["A", "S", 124, undef, "0730"],
        ...,
        ],
        ["2005-08-02", [
        ["A", "O", 94, 0.006, "0400"],
        ["A", "S", 91, undef, "0609"],
        ...,
        ]
        ];

        Is that close to what you want? If not, please describe the problem
        again, and don't use terminology like "add a row to a hash", because
        that doesn't make sense unless you also talk about keys, because
        hashes take keys and values, not just rows.

        --
        Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
        <merlyn@...> <URL:http://www.stonehenge.com/merlyn/>
        Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
        See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!
      • Jonathan Mangin
        ... From: To: Jonathan Mangin Cc: Sent: Tuesday, December 06, 2005 10:03 AM
        Message 3 of 5 , Dec 6, 2005
          ----- Original Message -----
          From: <merlyn@...>
          To: "Jonathan Mangin" <jon.mangin@...>
          Cc: <perl-beginner@yahoogroups.com>
          Sent: Tuesday, December 06, 2005 10:03 AM
          Subject: Re: [PBML] Problem with looping and references


          >>>>>> "Jonathan" == Jonathan Mangin <jon.mangin@...> writes:
          >
          > Jonathan> I have a query that returns data like this:
          > Jonathan> date time class type val1 val2
          > time_format(time,'%H%i')
          > Jonathan> 2005-08-01 04:02:00 A O 95 .006
          > 0402
          > Jonathan> 2005-08-01 06:08:00 A S 92
          > 0608
          > Jonathan> 2005-08-01 07:30:00 A S 124
          > 0730
          > Jonathan> 2005-08-01 15:00:00 A S 72
          > 1500
          > Jonathan> 2005-08-02 04:00:00 A O 94 .006
          > 0400
          > Jonathan> 2005-08-02 06:09:00 A S 91
          > 0609
          > Jonathan> 2005-08-02 07:31:00 A S 128
          > 0731
          > Jonathan> 2005-08-02 08:00:00 A O 128 .008
          > 0800
          >
          > Jonathan> As each row is fetched I want to compare $row[0] to $date1.
          > Jonathan> If true, I want to add that row to %vals.
          >
          > What does it mean to "add a row" to a hash? What key would you use?
          > Do you mean an array instead?

          (Define a hash element?)
          $vals{$row[6]} = [substr($row[1],0,5), $row[3], $row[4], $row[5]];

          >
          > Jonathan> When all 2005-08-01 rows
          > Jonathan> have been fetched I want to populate @temp with ($row[0],
          > \%vals),
          > Jonathan> push [@temp] onto @data, increment $date1, and do it all again
          > with
          > Jonathan> 2005-08-02...
          >
          > I *think* I understand, and it'd be faster to build a hash keyed
          > on the first column, then flatten that hash to an list of arrays
          > at the final step:
          >
          > my %temp;
          > while (my @row = GET_A_ROW) {
          > push @{$temp{$row[0]}}, [@row[2..6]];
          > }
          > my @result = map [$_, $temp{$_}], sort keys %temp;
          >
          > That will create:
          >
          > @result =
          > ["2005-08-01", [
          > ["A", "O", 95, 0.006, "0402"],
          > ["A", "S", 92, undef, "0608"],
          > ["A", "S", 92, undef, "0608"],
          > ["A", "S", 124, undef, "0730"],
          > ...,
          > ],
          > ["2005-08-02", [
          > ["A", "O", 94, 0.006, "0400"],
          > ["A", "S", 91, undef, "0609"],
          > ...,
          > ]
          > ];
          >
          > Is that close to what you want?

          Close.
          Try to excuse the gross misuse of braces/brackets.
          Just trying to illustrate.

          @result =
          ["2005-08-01", [
          $vals{"0402"} = ["04:02", "O", 95, 0.006],
          $vals{"0608"} = ["06:08", "S", 92, undef],
          $vals{"0730"} = ["07:30", "S", 124, undef],
          ...,
          ],
          ["2005-08-02", [
          $vals{"0400"} = ["04:00", "O", 94, 0.006],
          $vals{"0609"} = ["06:09", "S", 91, undef],
          ...,
          ]
          ];

          Lots of things to study...I appreciate the help.

          Thanks,
          Jon
        • Charles K. Clarkson
          ... That cannot be done because $vals{ 0402 } cannot easily be expressed inside a perl data structure. We could do this, however. @result = ( [ 2005-08-01 , {
          Message 4 of 5 , Dec 6, 2005
            Jonathan Mangin <> wrote:

            : Try to excuse the gross misuse of braces/brackets.
            : Just trying to illustrate.
            :
            : @result =
            : ["2005-08-01", [
            : $vals{"0402"} = ["04:02", "O", 95, 0.006],
            : $vals{"0608"} = ["06:08", "S", 92, undef],
            : $vals{"0730"} = ["07:30", "S", 124, undef],
            : ...,
            : ],
            : ["2005-08-02", [
            : $vals{"0400"} = ["04:00", "O", 94, 0.006],
            : $vals{"0609"} = ["06:09", "S", 91, undef],
            : ...,
            : ]
            : ];

            That cannot be done because $vals{"0402"} cannot
            easily be expressed inside a perl data structure. We
            could do this, however.

            @result = (
            [
            '2005-08-01',
            {
            '0402' => ['04:02', 'O', 95, 0.006],
            '0608' => ['06:08', 'S', 92, undef],
            '0730' => ['07:30', 'S', 124, undef],
            ...,
            },
            ],

            [
            '2005-08-02',
            {
            '0400' => ['04:00', 'O', 94, 0.006],
            '0609' => ['06:09', 'S', 91, undef],
            ...,
            }
            ]

            );

            To get that value of 94, we would need something like this.

            $result[1]{'0400'}[2];


            HTH,

            Charles K. Clarkson
            --
            Mobile Homes Specialist
            254 968-8328
          • Jonathan Mangin
            ... From: Charles K. Clarkson To: Sent: Tuesday, December 06, 2005 1:28 PM Subject: RE: [PBML] Problem
            Message 5 of 5 , Dec 6, 2005
              ----- Original Message -----
              From: "Charles K. Clarkson" <cclarkson@...>
              To: <perl-beginner@yahoogroups.com>
              Sent: Tuesday, December 06, 2005 1:28 PM
              Subject: RE: [PBML] Problem with looping and references


              > Jonathan Mangin <> wrote:
              >
              > : Try to excuse the gross misuse of braces/brackets.
              > : Just trying to illustrate.
              > :
              > : @result =
              > : ["2005-08-01", [
              > : $vals{"0402"} = ["04:02", "O", 95, 0.006],
              > : $vals{"0608"} = ["06:08", "S", 92, undef],
              > : $vals{"0730"} = ["07:30", "S", 124, undef],
              > : ...,
              > : ],
              > : ["2005-08-02", [
              > : $vals{"0400"} = ["04:00", "O", 94, 0.006],
              > : $vals{"0609"} = ["06:09", "S", 91, undef],
              > : ...,
              > : ]
              > : ];
              >
              > That cannot be done because $vals{"0402"} cannot
              > easily be expressed inside a perl data structure. We
              > could do this, however.
              >
              > @result = (
              > [
              > '2005-08-01',
              > {
              > '0402' => ['04:02', 'O', 95, 0.006],
              > '0608' => ['06:08', 'S', 92, undef],
              > '0730' => ['07:30', 'S', 124, undef],
              > ...,
              > },
              > ],
              >
              > [
              > '2005-08-02',
              > {
              > '0400' => ['04:00', 'O', 94, 0.006],
              > '0609' => ['06:09', 'S', 91, undef],
              > ...,
              > }
              > ]
              >
              > );
              >
              > To get that value of 94, we would need something like this.
              >
              > $result[1]{'0400'}[2];
              >
              >
              > HTH,
              >
              > Charles K. Clarkson

              Well, I did say for illustration ;)

              Or, for processing in TT...

              [% FOREACH row IN result %]
              [% SET cdate = result.0 %]
              [% result.0 %]
              [% FOREACH item IN result.1 %]
              <td>[% item.value.0 %] -- [% item.value.1 %]</td>
              ...
              [% END %]
              [% END %]

              I am using this construct elsewhere for building tables with
              user-selectable columns but this particular _query_ has me
              stumped. I'll have to study Randal's answer.
            Your message has been successfully submitted and would be delivered to recipients shortly.