Update: The second I posted this, I noticed that the while statement is making the fetchrow go out of scope (d'uh!). So the updated question is what is the most efficient/elegant way to achieve the structure that I need without it going out of scope?

Hello,

I'm receiving the "DBD::mysql::st fetchrow_hashref failed: fetch() without execute() " error when invoking fetchrow_hashref within loops. First the code: 

      my $sth_spaces = $dbh->prepare( "SELECT 
            Spaces.*,
            SpaceTypes.SpaceTypeID,SpaceTypes.SpaceTypeName,
            COUNT(Spaces.SpaceID)
            from Spaces
            LEFT JOIN SpaceTypes ON SpaceTypes.SpaceTypeID = Spaces.Sp
+aceTypeID
            WHERE Spaces.RegioID = $regioID
            GROUP BY SpaceID
            ORDER BY SpaceName
      ");

      $sth_spaces->execute();

      my @regioloop = ();

      foreach my $regid ( sort { $a cmp $b } keys %$regia ){
         my @insulaloop = ();

         foreach my $insid ( sort { $a cmp $b } keys %$insulae ){
            my @spaceloop = ();

            while ( my $space = $sth_spaces->fetchrow_hashref() ){
               push @spaceloop, {
                  'SpaceID'       => $space->{'SpaceID'},
                  'Doorway'       => $space->{'Doorway'},
                  'SpaceName'     => $space->{'SpaceName'},
                  'SpaceTypeID'   => $space->{'SpaceTypeID'},
                  'SpaceTypeName' => $space->{'SpaceTypeName'}
               };
           }
           push @insulaloop, {
              'InsulaID'   => $insid,
              'InsulaName' => $insulae->{ $insid },
              'SPACELOOP'  => \@spaceloop    
           };
         }                
         push @regioloop, {
            'RegioID'   => $regid,
            'RegioName' => $regia->{ $regid },
            'INSULALOOP' => \@insulaloop    
         };            
        
         push @{$fields->{'REGIOLOOP'}}, @regioloop;
      }

[download]
$fields is going to get tossed to HTML::Template for some happy TMPL_LOOP nesting, which is why I need this data structure.

If I pull the while statement outside of the foreach loops, it works fine. What is it about being inside the loop that makes this error come up, and how can I fix it, oh wise ones?

Thanks!
Jasmine

In reply to fetchrow_hashref within a loop yields "fetch() without execute()" by Jazz

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.