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

Do I need an inner loop or ?

Expand Messages
  • Jonathan Mangin
    I hope this is mostly a perlish question. I have tables that look something like this: +----+------------+----------+------+------+------+ ...
    Message 1 of 7 , Dec 5, 2005
    • 0 Attachment
      I hope this is mostly a perlish question.
      I have tables that look something like this:

      +----+------------+----------+------+------+------+
      | id | date | time | type | val1 | val2 |
      +----+------------+----------+------+------+------+
      | 1 | 2005-08-01 | 04:02:00 | O | | 95 |
      | 2 | 2005-08-01 | 06:08:00 | S | .006 | 92 |
      | 3 | 2005-08-01 | 07:30:00 | S | .007 | 124 |
      | 4 | 2005-08-01 | 15:00:00 | S | .005 | 72 |
      | 5 | 2005-08-01 | 18:32:00 | S | .008 | 144 |
      | 6 | 2005-08-01 | 21:37:00 | S | .007 | 114 |
      | 7 | 2005-08-02 | 04:00:00 | O | | 94 |
      | 8 | 2005-08-02 | 06:09:00 | S | .006 | 91 |
      | 9 | 2005-08-02 | 07:31:00 | S | .008 | 128 |
      | 10 | 2005-08-02 | 08:00:00 | O | | 128 |
      | 11 | 2005-08-02 | 15:08:00 | S | .005 | 70 |
      | 12 | 2005-08-02 | 18:38:00 | S | .008 | 143 |
      | 13 | 2005-08-02 | 21:45:00 | S | .007 | 113 |


      I'm trying to put data from a single date into %val.
      This code puts all dates into %val. It's about as close
      as I've come.

      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]];
      @temp = ($row[0], \%vals);
      next;
      }
      push @data, [@temp];
      # (%vals, @temp) = ();
      $date1 = $date1->next;
      }

      @temp should consist of a single day's data before it's pushed
      onto @data. I think I need another loop, but I've failed with all
      kinds of garbage code. Is possible somehow?

      Thanks alot!
    • merlyn@stonehenge.com
      ... Jonathan I hope this is mostly a perlish question. Jonathan I have tables that look something like this: Jonathan
      Message 2 of 7 , Dec 5, 2005
      • 0 Attachment
        >>>>> "Jonathan" == Jonathan Mangin <jon.mangin@...> writes:

        Jonathan> I hope this is mostly a perlish question.
        Jonathan> I have tables that look something like this:

        Jonathan> +----+------------+----------+------+------+------+
        Jonathan> | id | date | time | type | val1 | val2 |
        Jonathan> +----+------------+----------+------+------+------+
        Jonathan> | 1 | 2005-08-01 | 04:02:00 | O | | 95 |
        Jonathan> | 2 | 2005-08-01 | 06:08:00 | S | .006 | 92 |
        Jonathan> | 3 | 2005-08-01 | 07:30:00 | S | .007 | 124 |
        Jonathan> | 4 | 2005-08-01 | 15:00:00 | S | .005 | 72 |
        Jonathan> | 5 | 2005-08-01 | 18:32:00 | S | .008 | 144 |
        Jonathan> | 6 | 2005-08-01 | 21:37:00 | S | .007 | 114 |
        Jonathan> | 7 | 2005-08-02 | 04:00:00 | O | | 94 |
        Jonathan> | 8 | 2005-08-02 | 06:09:00 | S | .006 | 91 |
        Jonathan> | 9 | 2005-08-02 | 07:31:00 | S | .008 | 128 |
        Jonathan> | 10 | 2005-08-02 | 08:00:00 | O | | 128 |
        Jonathan> | 11 | 2005-08-02 | 15:08:00 | S | .005 | 70 |
        Jonathan> | 12 | 2005-08-02 | 18:38:00 | S | .008 | 143 |
        Jonathan> | 13 | 2005-08-02 | 21:45:00 | S | .007 | 113 |


        Jonathan> I'm trying to put data from a single date into %val.
        Jonathan> This code puts all dates into %val. It's about as close
        Jonathan> as I've come.

        First, why are date and time two separate columns?

        Second, to do a sum, do it in the database:

        SELECT date, sum(val1), sum(val2)
        FROM your_table
        GROUP BY 1;

        --
        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: Randal L. Schwartz To: Jonathan Mangin Cc: Sent: Monday,
        Message 3 of 7 , Dec 5, 2005
        • 0 Attachment
          ----- Original Message -----
          From: "Randal L. Schwartz" <merlyn@...>
          To: "Jonathan Mangin" <jon.mangin@...>
          Cc: <perl-beginner@yahoogroups.com>
          Sent: Monday, December 05, 2005 10:45 AM
          Subject: Re: [PBML] Do I need an inner loop or ?


          >>>>>> "Jonathan" == Jonathan Mangin <jon.mangin@...> writes:
          >
          > Jonathan> I hope this is mostly a perlish question.
          > Jonathan> I have tables that look something like this:
          >
          > Jonathan> +----+------------+----------+------+------+------+
          > Jonathan> | id | date | time | type | val1 | val2 |
          > Jonathan> +----+------------+----------+------+------+------+
          > Jonathan> | 1 | 2005-08-01 | 04:02:00 | O | | 95 |
          > Jonathan> | 2 | 2005-08-01 | 06:08:00 | S | .006 | 92 |
          > Jonathan> | 3 | 2005-08-01 | 07:30:00 | S | .007 | 124 |
          > Jonathan> | 4 | 2005-08-01 | 15:00:00 | S | .005 | 72 |
          > Jonathan> | 5 | 2005-08-01 | 18:32:00 | S | .008 | 144 |
          > Jonathan> | 6 | 2005-08-01 | 21:37:00 | S | .007 | 114 |
          > Jonathan> | 7 | 2005-08-02 | 04:00:00 | O | | 94 |
          > Jonathan> | 8 | 2005-08-02 | 06:09:00 | S | .006 | 91 |
          > Jonathan> | 9 | 2005-08-02 | 07:31:00 | S | .008 | 128 |
          > Jonathan> | 10 | 2005-08-02 | 08:00:00 | O | | 128 |
          > Jonathan> | 11 | 2005-08-02 | 15:08:00 | S | .005 | 70 |
          > Jonathan> | 12 | 2005-08-02 | 18:38:00 | S | .008 | 143 |
          > Jonathan> | 13 | 2005-08-02 | 21:45:00 | S | .007 | 113 |
          >
          >
          > Jonathan> I'm trying to put data from a single date into %val.
          > Jonathan> This code puts all dates into %val. It's about as close
          > Jonathan> as I've come.
          >
          > First, why are date and time two separate columns?
          >
          > Second, to do a sum, do it in the database:
          >
          > SELECT date, sum(val1), sum(val2)
          > FROM your_table
          > GROUP BY 1;
          >

          I don't know where you got the idea I'm trying to sum anything.
          Did you look at my question?
        • merlyn@stonehenge.com
          ... Jonathan I don t know where you got the idea I m trying to sum anything. Jonathan Did you look at my question? Sorry, I ignored your code, because it had
          Message 4 of 7 , Dec 5, 2005
          • 0 Attachment
            >>>>> "Jonathan" == Jonathan Mangin <jon.mangin@...> writes:

            Jonathan> I don't know where you got the idea I'm trying to sum anything.
            Jonathan> Did you look at my question?

            Sorry, I ignored your code, because it had an SQL call but didn't show
            the prepare, and referred to $row[6] when you had only columns 0-5 in
            your sample table. If you don't show the actual code you're using,
            it's hard to make comments on it.

            So instead, I looked at your text, which talked about getting stuff
            for a given date into one place, and presumed you were trying to sum
            something.

            Perhaps you could show *actual* code, then ask a question that makes
            sense related to that code.

            --
            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!
          • Charles K. Clarkson
            ... What should the data in %val look like? I assume you have confused the array starting index as 0 and as 1 at various points of your code. I also assume
            Message 5 of 7 , Dec 5, 2005
            • 0 Attachment
              Jonathan Mangin <> wrote:

              : I'm trying to put data from a single date into %val.
              : This code puts all dates into %val. It's about as close as I've
              : come.

              What should the data in %val look like? I assume you have
              confused the array starting index as 0 and as 1 at various points
              of your code. I also assume that the query did not return the id
              field. And finally I assume you want a hash of array of arrays
              keyed by date.

              For my example, I will assume fetchrow_array() returns values
              like this. (The time column should be able to be adjusted by the
              db to return the HH:MM format and avoid the substr() call in
              perl.)

              +------------+----------+------+------+------+
              | date | time | type | val1 | val2 |
              +------------+----------+------+------+------+
              | 2005-08-01 | 04:02:00 | O | | 95 |
              | 2005-08-01 | 06:08:00 | S | .006 | 92 |

              my %data_by_date;
              while ( my( $date, $time, @record ) = $sth->fetchrow_array() ) {
              push @{ $data_by_date{ $date } }, [ substr($time, 0, 5), @record ];
              }

              my $date1 = Date::Simple->new($bdate);
              $data_by_date{ $date1 } will now return an array of arrays.


              : my $date1 = Date::Simple->new($bdate);
              : # my $date2 = Date::Simple->new($edate);
              :
              : my %vals = ();
              : my @temp = ();
              : my @data = ();

              Yuck!

              my( %vals, @temp, @data );


              : while (my @row = $sth->fetchrow_array) {
              : if ($row[0] eq $date1) {

              As described in your table, row[0] is not the date, row[1] is.


              : $vals{$row[6]} = [substr($row[1],0,5), $row[3], $row[4], $row[5]];

              As described in your table, row[6] does not exist.


              : @temp = ($row[0], \%vals);

              This resets @temp each time through. All previous values will
              be lost.


              : next;
              : }

              YMMV, but I prefer "else" to "next" in this situation. I think
              it gives a better hint to what is happening.


              if ( $row[0] eq $date1 ) {
              $vals{ $row[6] } = [ substr($row[1],0,5), $row[3], $row[4], $row[5]
              ];
              @temp = ( $row[0], \%vals );

              } else {
              push @data, [@temp];
              $date1 = $date1->next;
              }


              HTH,

              Charles K. Clarkson
              --
              Mobile Homes Specialist
              254 968-8328
            • Jonathan Mangin
              It s true enough, I have a hard time asking questions properly owing to wide and deep ignorance. I include a snippet of table only to show normalized
              Message 6 of 7 , Dec 5, 2005
              • 0 Attachment
                It's true enough, I have a hard time asking questions
                properly owing to wide and deep ignorance. I include
                a snippet of table only to show "normalized" nature.
                Many more columns from three similar tables are involved.
                Poor example data:

                | 4 | 2005-08-01 | 15:00:00 | S | .005 | 72 |
                | 5 | 2005-08-01 | 18:32:00 | S | .008 | 144 |
                | 6 | 2005-08-01 | 21:37:00 | S | .007 | 114 |
                | 7 | 2005-08-02 | 04:00:00 | O | | 94 |
                | 8 | 2005-08-02 | 06:09:00 | S | .006 | 91 |
                | 9 | 2005-08-02 | 07:31:00 | S | .008 | 128 |

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

                # Been moved many times. You'd be yucky too. (Helps prove reality :)
                my %vals = ();
                my @temp = ();
                my @data = ();
                while (my @row = $sth->fetchrow_array) {

                # Compare date
                if ($row[0] eq $date1) {

                # Phantom $row[6] is time column in HHMM format
                $vals{$row[6]} = [substr($row[1],0,5), $row[3], $row[4], $row[5]];

                # If true...
                # $vals{1500} = [15:00, S, .005, 72]

                next;

                # If still true...
                # $vals{1832} = [18:32, S, .008, 144]
                # $vals{2137} = [21:37, S, .007, 114]
                }


                # Moved for clarity(?)
                @temp = ($row[0], \%vals);
                # @temp = ('2005-08-01', \%vals);
                push @data, [@temp];

                # (%vals, @temp) = ();
                $date1 = $date1->next;
                }

                Now go back and do it all again for 2005-08-02 and subsequent days
                resulting in @data = [@temp, @temp, @temp...]

                Don't understand much of the following though you seem know
                approximately what I need. Does using the time as key change
                appreciably what you've done?

                > my %data_by_date;
                > while ( my( $date, $time, @record ) = $sth->fetchrow_array() ) {
                > push @{ $data_by_date{ $date } }, [ substr($time, 0, 5), @record ];
                > }
                >
                > my $date1 = Date::Simple->new($bdate);
                > $data_by_date{ $date1 } will now return an array of arrays.
                >
                > : @temp = ($row[0], \%vals);
                >
                > This resets @temp each time through. All previous values will
                > be lost.
                >
                >
                > : next;
                > : }
                >
                > YMMV, but I prefer "else" to "next" in this situation. I think
                > it gives a better hint to what is happening.
                >
                >
                > if ( $row[0] eq $date1 ) {
                > $vals{ $row[6] } = [ substr($row[1],0,5), $row[3], $row[4], $row[5]
                > ];
                > @temp = ( $row[0], \%vals );
                >
                > } else {
                > push @data, [@temp];
                > $date1 = $date1->next;
                > }
                >
                >
                > HTH,
                >
                > Charles K. Clarkson
                > --
                > Mobile Homes Specialist
                > 254 968-8328
                >
                Charles,
                I wonder if you can put it all together in one piece for me
                to study? Thanks for the time you've given me already.

                --Jon
              • Charles K. Clarkson
                ... You can become less ignorant by learning. Many of traits needed for asking good questions are also the traits needed to be a good programmer.
                Message 7 of 7 , Dec 5, 2005
                • 0 Attachment
                  Jonathan Mangin <> wrote:

                  : It's true enough, I have a hard time asking questions properly
                  : owing to wide and deep ignorance.

                  You can become less ignorant by learning. Many of traits
                  needed for asking good questions are also the traits needed to be
                  a good programmer.

                  http://www.catb.org/~esr/faqs/smart-questions.html


                  : I include a snippet of table only to show "normalized" nature.
                  : Many more columns from three similar tables are involved.

                  Then show us the columns actually returned or stick to just
                  the six columns in your example code. This was one problem Randal
                  had with your code. You referred to the seventh column ($row[6])
                  with only 6 columns (@row[0..5]) in the example code.

                  Tell us what you want in clear concise terms. Forget all the
                  variables, just show us what the data structure is suppose to look
                  like by using the data in a table of supplied data.


                  HTH,

                  Charles K. Clarkson
                  --
                  Mobile Homes Specialist
                  254 968-8328
                Your message has been successfully submitted and would be delivered to recipients shortly.