469,271 Members | 1,110 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

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

147 100+
Hi,

Please see my code:
Expand|Select|Wrap|Line Numbers
  1. !c:/perl/bin/perl
  2. use CGI qw(:all);
  3. $empid=param('empid');
  4.  
  5.  
  6.  print "Content-type: text/html\n\n";
  7.  print "<body bgcolor=\"#ffcccc\">";
  8.  
  9.  use DBI;
  10.  my $dbh = DBI->connect("DBI:ODBC:aaaa","bbb","cccc") or die "Can not connect: $DBI::errstr\n"; 
  11.  
  12.  my $sth;
  13.  
  14. print "<center><table border=1></center>";
  15. print "<tr><th>SlNo</th><th>name</th><th>designation</th><th></th><th>title1</th><th>title2</th></tr>";
  16.  
  17.  
  18. $sth=$dbh->prepare("select empname, empdesignation from emp where empid=?");
  19. $sth->execute($empid) or die "Cant execute SQL: $DBI::errstr\n" 
  20. while ( @row = $sth->fetchrow_array())
  21.      {
  22.            $j=$j+1;
  23.             Here  again, i am calling 
  24.             $sth=$dbh->....
  25.             while()
  26.             {
  27.             } 
  28.            print "<tr><td>$j</td><td>$row[0]</td><td>$row[1]</td><td>$row[2]</td><td>$row[3]</td></tr>";
  29.      }
  30.  
  31.  
  32.  
  33.  
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
8 3660
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
  1. use strict;
  2. 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
  1. $sth=$dbh->prepare("select empname, empdesignation from emp where empid=?");
  2. $sth->execute($empid) or die "Cant execute SQL: $DBI::errstr\n";
  3.  
  4. my ($emp_name, $emp_designation) = $sth->fetchrow_array;
  5. $sth->finish;
  6.  
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
  1. #!c:/perl/bin/perl
  2. use CGI qw(:all);
  3. $pfaccode=param('faccode');
  4. print "Content-type: text/html\n\n";
  5. print "<body bgcolor=\"#ffcccc\">";
  6. use DBI;
  7. my $dbh = DBI->connect("DBI:ODBC:aaaa","dddd","fffff") or die "Can not connect: $DBI::errstr\n"; 
  8.  
  9.  
  10. $j=0;
  11.  
  12. print "<br>";
  13. print "<center><h2>Student Current Registration Details</h2></center>";
  14. print "<br>";
  15.  
  16.  
  17. print $pfaccode;
  18.  
  19. print "<center><table border=1 align=center></center>";
  20. 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>";
  21.  
  22. my $sth = $dbh->prepare("select cregp from clockmst") or die "Can not prepare SQL statement\n";
  23. $sth->execute or die "Cant execute SQL: $DBI::errstr\n";
  24. while ( @row = $sth->fetchrow_array())
  25. {
  26.     $mperiod = $row[0];
  27. }
  28. print $mperiod;
  29.  
  30.  
  31. my @row;
  32. my $stucount=0;
  33. my $count=0;
  34. my $j=0;
  35.  
  36.  
  37. $sth=$dbh->prepare("select count(*) as att from stuacmst where faccode=? and studstat='C'")or die "Cant prepare: $DBI::errstr\n";
  38. $sth->execute($pfaccode) or die "Cant execute: $DBI::errstr\n";
  39. while(@row = $sth->fetchrow_array())
  40. {
  41.      $stucount=$row[0];
  42.     print $stucount;
  43.  
  44. }
  45.  
  46. if($stucount>0)
  47. {
  48.    $sth=$dbh->prepare("select rollno,name,programme,deptcode,semester from stuacmst where faccode=? and studstat='C'")or die "Cant prepare: $DBI::errstr\n";
  49.    $sth->execute($pfaccode) or die "Cant execute: $DBI::errstr\n";
  50.    while(@row = $sth->fetchrow_array())
  51.    {
  52.      $mrollno=$row[0];
  53.      $mname=$row[1];
  54.      $mprogramme=$row[2];
  55.      $mdeptcode=$row[3];
  56.      $msemester=$row[4];
  57.      $j=$j+1;
  58.     #print $mrollno;
  59.       #print $mname;
  60.  
  61.      $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";
  62.      $sth->execute($mperiod,$prollno) or die "Cant execute: $DBI::errstr\n";
  63.      $count = $sth->fetchrow_arrayref()->[0];
  64.  
  65.  
  66.      print "<tr><td>$j</td><td>$mrollno</td><td>$mname</td><td>$mprogramme</td><td>$mdeptcode</td><td>$msemester</td><td>$count</td></tr>";
  67.   } 
  68.  
  69.  
  70.  
  71.  
  72. }
  73. print "</table>";
  74. print "</body>";
  75.  
  76.  
  77.  
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
  1. my ($stucount) = $sth->fetchrow_array;
Jan 23 '11 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Roy Smith | last post: by
9 posts views Thread by Cybex | last post: by
13 posts views Thread by israphelr | last post: by
6 posts views Thread by mgcclx | last post: by
9 posts views Thread by saravanan82 | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.