Jazz has asked for the wisdom of the Perl Monks concerning the following question:

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?


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; }
$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?