RE: [htmltmpl] Nested Loops

[Chris D. <Chr...@Ma...>]

On Mon, Jun 24, 2002 at 08:35:25AM -0700, Roy Rubin wrote:
>I am looking to
>use the refernces that are already part of the DBI, instead of hard coding
>the variables (as in $OfficeID, etc.) I hope this makes sense.

On Monday, June 24, 2002 4:47 PM:Roger Burton West
[mailto:ro...@fi...] replied:

>	my @l1;
>	my $sth=$dbh->prepare("SELECT blah blah ... ");
>	$sth->execute;
>	while (my $r=$sth->fetchrow_hashref) {
>	    # manipulate $r if desired, for example by adding an inner loop:
>	    $r->{something_not_in_database}=$something_else;
>	    # once all manipulation is done:
>	    push @l1,$r;
>	}

I don't believe that this example will work with new(er) versions of DBI, as
the documentation states:
	Currently, a new hash reference is returned for each
	row.  This will change in the future to return the
	same hash ref each time, so don't rely on the current
	behaviour.

Consequently you would need to generate a new hash:
	push @l1, { %$r };

Incidentally, you can make your database queries *much* more efficient by
moving the prepare statements outside your loops, and using placeholder
queries. To take your original example, this could be rewritten thus:

	my $sqlOffice = qq{SELECT OfficeID, OfficeLocation FROM Office};
	my $sthOffice = $dbh->prepare($sqlOffice) or bail_out($self);

	my $sqlEmp = qq{Select ID from Employees WHERE Office = ?};
	my $sthEmp = $dbh->prepare($sqlEmp) or bail_out($self);

	my $sthOffice->execute() or bail_out($self);

	#outer loop
	my @Offices;
	while (my ($OfficeID, $OfficeLocation) = $sthOffice->fetchrow_array()) {
	    $sthEmp->execute($OfficeID) or bail_out($self);
	    my @whatever;

	    #inner loop
	    while (my ($ID) = $sth1->fetchrow_array()) {
		push @whatever, {ID => $ID};
	    }
	    push @Offices, {OfficeLocation => $OfficeLocation, EmpNames =>
\@whatever};
	}

	$thtml->param(OFFICES => \@Offices);

Chris
--
Chris Davies, Manheim Online
Tel. 0113 393-2004  Fax. 0870 444-0482.  Mobile 07778 199069