How can have one WHILE Loop inside the WHILE Loop in perl?

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • santhanalakshmi
    New Member
    • May 2009
    • 147
    #1

    How can have one WHILE Loop inside the WHILE Loop in perl?

    Hi,

    Please see my code:
    Code:
    !c:/perl/bin/perl
use CGI qw(:all);
$empid=param('empid');
  
  
 print "Content-type: text/html\n\n";
 print "<body bgcolor=\"#ffcccc\">";
  
 use DBI;
 my $dbh = DBI->connect("DBI:ODBC:aaaa","bbb","cccc") or die "Can not connect: $DBI::errstr\n"; 
  
 my $sth;
  
print "<center><table border=1></center>";
print "<tr><th>SlNo</th><th>name</th><th>designation</th><th></th><th>title1</th><th>title2</th></tr>";


$sth=$dbh->prepare("select empname, empdesignation from emp where empid=?");
$sth->execute($empid) or die "Cant execute SQL: $DBI::errstr\n" 
while ( @row = $sth->fetchrow_array())
     {
           $j=$j+1;
            [B]Here  again, i am calling 
            $sth=$dbh->....
            while()
            {
            } 
[/B]
           print "<tr><td>$j</td><td>$row[0]</td><td>$row[1]</td><td>$row[2]</td><td>$row[3]</td></tr>";
     }
    my problem is, i am not facing any error. But total record count is 20 means, i am getting only 1 record as output. I don't know. Why my outer WHILE LOOP is not iterating? Its getting stopped from one output. My SQL statement is correct. I checked it out.Thanks in advance.
    Tags: None
    • Rabbit
      Recognized Expert MVP
      • Jan 2007
      • 12517
      #2
      What's the rest of this line? "$sth=$dbh->...."
      You are reassigning $sth to something even though you're using it as your loop condition. Once you've reassigned this variable, your loop condition no longer evaluates correctly because it's lost the record set that it was reading from.

        Comment

        • santhanalakshmi
          New Member
          • May 2009
          • 147
          #3
          Hi,
          Yes, you are correct.In inner $sth -> i am trying to reassigning with other "select" statement.Then how can i solve this problem. If i use $sth1 in inner means, my outer $sth-> is not working. Thanks in advance

            Comment

            • RonB
              Recognized Expert Contributor
              • Jun 2009
              • 589
              #4
              Your first problem is that you're missing these 2 pragmas.
              Code:
              use strict;
use warnings;
              Add those and make sure you use the 'my' keyword and declare all of your vars.

              Your first select statement should only return 1 record, assuming your database is setup correctly and you don't have duplicate records. So, there is no need/reason to use a loop when retrieving that record.
              Code:
              $sth=$dbh->prepare("select empname, empdesignation from emp where empid=?");
$sth->execute($empid) or die "Cant execute SQL: $DBI::errstr\n";

my ($emp_name, $emp_designation) = $sth->fetchrow_array;
$sth->finish;

                Comment

                • santhanalakshmi
                  New Member
                  • May 2009
                  • 147
                  #5
                  Hi,

                  Thanks. My first Select statement is not returning only one record. Its returning more than one record. So i passed to while loop(outer most one) and then i passing this result to the inner most SELECT STATEMENT. This also containing more than one record. I don't know what problem in my coding.

                    Comment

                    • RonB
                      Recognized Expert Contributor
                      • Jun 2009
                      • 589
                      #6
                      First, as it has already been pointed out, you can't use the same var for the statement handle for both select statements.

                      Second, you haven't shown us your second while loop so we have no way to determine the problem, other than what has already been pointed out.

                      Please post a short but complete script that demonstrates your problem.

                        Comment

                        • numberwhun
                          Recognized Expert Moderator Specialist
                          • May 2007
                          • 3467
                          #7
                          After 107 posts, I don't think we should be asking for your code, but that's just me.

                            Comment

                            • santhanalakshmi
                              New Member
                              • May 2009
                              • 147
                              #8
                              hi,
                              Thanks for help. Please check my coding

                              Code:
                              #!c:/perl/bin/perl
use CGI qw(:all);
$pfaccode=param('faccode');
print "Content-type: text/html\n\n";
print "<body bgcolor=\"#ffcccc\">";
use DBI;
my $dbh = DBI->connect("DBI:ODBC:aaaa","dddd","fffff") or die "Can not connect: $DBI::errstr\n"; 


$j=0;

print "<br>";
print "<center><h2>Student Current Registration Details</h2></center>";
print "<br>";


print $pfaccode;

print "<center><table border=1 align=center></center>";
print "<tr><th>SlNo</th><th>Rollno</th><th>Name</th><th>Prog</th><th>Deptcode</th><th>Sem</th><th>Crseno1</th><th>Crseno2</th><th>Crseno3</th><th>Crseno4</th><th>Crseno5</th><th>Crseno6</th><th>Crseno7</th><th>Crseno8</th><th>Crseno9</th></tr>";

my $sth = $dbh->prepare("select cregp from clockmst") or die "Can not prepare SQL statement\n";
$sth->execute or die "Cant execute SQL: $DBI::errstr\n";
while ( @row = $sth->fetchrow_array())
{
	$mperiod = $row[0];
}
print $mperiod;


my @row;
my $stucount=0;
my $count=0;
my $j=0;


$sth=$dbh->prepare("select count(*) as att from stuacmst where faccode=? and studstat='C'")or die "Cant prepare: $DBI::errstr\n";
$sth->execute($pfaccode) or die "Cant execute: $DBI::errstr\n";
while(@row = $sth->fetchrow_array())
{
     $stucount=$row[0];
	print $stucount;
     
}

if($stucount>0)
{
   $sth=$dbh->prepare("select rollno,name,programme,deptcode,semester from stuacmst where faccode=? and studstat='C'")or die "Cant prepare: $DBI::errstr\n";
   $sth->execute($pfaccode) or die "Cant execute: $DBI::errstr\n";
   while(@row = $sth->fetchrow_array())
   {
     $mrollno=$row[0];
     $mname=$row[1];
     $mprogramme=$row[2];
     $mdeptcode=$row[3];
     $msemester=$row[4];
     $j=$j+1;
	#print $mrollno;
      #print $mname;
   
     $sth=$dbh->prepare("select count(*) from crnrgdet b,corsemst a where a.crseid=b.crseid and b.period=? and b.rollno=?")or die "Cant prepare: $DBI::errstr\n";
     $sth->execute($mperiod,$prollno) or die "Cant execute: $DBI::errstr\n";
     $count = $sth->fetchrow_arrayref()->[0];


     print "<tr><td>$j</td><td>$mrollno</td><td>$mname</td><td>$mprogramme</td><td>$mdeptcode</td><td>$msemester</td><td>$count</td></tr>";
  } 

     
    

}
print "</table>";
print "</body>";

                                Comment

                                • RonB
                                  Recognized Expert Contributor
                                  • Jun 2009
                                  • 589
                                  #9
                                  You have 3 select statements, all of which use the same variable for the statement handle. The 1st and 3rd will return only 1 result each. The 3rd one is where you have a problem. As has already been mentioned, you can't the reassign the statement handle inside the loop when that handle is used as the loop control.

                                  You need to use a different var for the 3rd handle, and I'd recommend using separate vars for each of the handles.

                                  The first while loop is not needed. You can reduce that to 1 line.
                                  Code:
                                  my ($stucount) = $sth->fetchrow_array;

                                    Comment

                                    Previous template Next
                                    Working...