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

Re: [PBML] Do I need an inner loop or ?

Expand Messages
  • Jonathan Mangin
    ... From: Randal L. Schwartz To: Jonathan Mangin Cc: Sent: Monday,
    Message 1 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?
    • 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 2 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 3 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 4 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.