Perl nested TMPL_LOOP in HTML::Template

Question

I have a database from which I need to get some values. My table looks like this

column: examroll, studentname, reporttitle, marks1, marks2, marks3, total
values:        1,         ABC,         xyz,      5,      4,      6,    15
values:        1,         ABC,         pqr,      6,      4,      6,    16
values:        2,         DEF,         stu,      3,      7,      4,    14
values:        2,         DEF,         vwx,      2,      8,      5,    15
values:        2,         DEF,         efg,      7,      3,      7,    17

I want to get report card for each student like following:

Name:ABC 
Exam Roll:1
xyz  5  4  6  15
pqr  6  4  6  16

NAME:DEF 
Exam Roll:2
stu  3  7  4  14
vwx  2  8  5  15
efg  7  3  7  17

I get studentname and examroll correctly, but in the innerloop, I get all the values together.

Exam Roll:1
xyz  5  4  6  15
pqr  6  4  6  16
stu  3  7  4  14
vwx  2  8  5  15
efg  7  3  7  17

NAME:DEF 
Exam Roll:2
xyz  5  4  6  15
pqr  6  4  6  16
stu  3  7  4  14
vwx  2  8  5  15
efg  7  3  7  17

Where should I change in order to get correct answer?

I have done following in perl code

my @outerloop=();
my @innerloop=();
my @blank=();

my $query1="select distinct examroll,studentname from table";
my $sth1 = $dbh->prepare($query1);
$sth1->execute();

my$query2="select reporttitle,mark1,mark2,mark3 where examroll=?";
my $sth2   = $dbh->prepare($query2);

while ($data1 = $sth1->fetchrow_hashref) { 
    $sth2->execute($data1->{'examroll'});
    while($data2 = $sth2->fetchrow_hashref){
        push(@innerloop,$data2);
    }
    $sth2->finish;
    $data1->{'innerloop'}=\@innerloop;

    push(@outerloop,$data1);
    #$data1->{'innerloop'}=\@blank; #This does not work either, all data is blank
}
$sth1->finish;

Now in html part

<TMPL_LOOP NAME=resultsloop>
  Name: <TMPL_VAR NAME=studentname>
  Exam Roll: <TMPL_VAR NAME=examroll>
  <table>
        <TMPL_LOOP NAME=innerloop>
            <tr>
                <td><TMPL_VAR NAME=reporttitle></td>
                <td><TMPL_VAR NAME=marks1></td>
                <td><TMPL_VAR NAME=marks2></td>
                <td><TMPL_VAR NAME=marks3></td>
                <td><TMPL_VAR NAME=total></td>
            </tr>
        </TMPL_LOOP>
    </table>
</TMPL_LOOP>

Answer 1

You have a single variable named @innerloop for both rolls reference. You need to create more than one variable.

my $query1 = "select distinct examroll,studentname from table";
my $sth1 = $dbh->prepare($query1);

my $query2 = "select reporttitle,mark1,mark2,mark3 where examroll=?";
my $sth2 = $dbh->prepare($query2);

my @outerloop;
$sth1->execute();
while(my $data1 = $sth1->fetchrow_hashref) { 
    $sth2->execute($data1->{'examroll'});

    my @innerloop;                                <-- New var created each
    while(my $data2 = $sth2->fetchrow_hashref){       pass of the loop.
        push(@innerloop,$data2);
    }

    $data1->{'innerloop'}=\@innerloop;

    push(@outerloop,$data1);
}

If you had limited the scope of your variables to where you needed them, you wouldn't have had this problem. Declaring variables before you need them defies a reason for having to declare them in the first place.