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

147 100+

Hi,

Please see my code:

Expand|Select|Wrap|Line Numbers

 !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;

            Here  again, i am calling 

            $sth=$dbh->....

            while()

            {

            } 
 
           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.

Jan 12 '11 #1

Follow Post Reply

3745

Rabbit

12,516

Expert Mod 8TB

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.

Jan 12 '11 #2

santhanalakshmi

147

100+

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

Jan 17 '11 #3

RonB

589

Expert Mod 512MB

Your first problem is that you're missing these 2 pragmas.

Expand|Select|Wrap|Line Numbers

 
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.

Expand|Select|Wrap|Line Numbers

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

Jan 17 '11 #4

santhanalakshmi

147

100+

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.

Jan 17 '11 #5

RonB

589

Expert Mod 512MB

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.

Jan 17 '11 #6

numberwhun

3,503

Expert Mod 2GB

After 107 posts, I don't think we should be asking for your code, but that's just me.

Jan 17 '11 #7

santhanalakshmi

147

100+

hi,
Thanks for help. Please check my coding

Expand|Select|Wrap|Line Numbers

 #!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>";

Jan 20 '11 #8

RonB

589

Expert Mod 512MB

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.

Expand|Select|Wrap|Line Numbers

my ($stucount) = $sth->fetchrow_array;

Jan 23 '11 #9

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

Post your reply

Similar topics