Fetch statements and while loops

[CherylD]

I'm fairly new to Perl so please bear with me.

I have some code that does a select, bind those results, and continues fetching while there is more records. That works great, but after I get the first piece of data the runs my while loop I need to get more data depending of its value and assign it to variables. I'm not sure how to do this. Below is what I have tried, but it's obviously not working.

my $sth = $dbh->prepare("SELECT tblA.data1
FROM tblA WHERE tblA.data2='' AND
tblA.data3=$var1&quot || die $!;

$sth->execute();
$sth->bind_columns(undef, \$data1);

print header();
open(TEMPLATE, "file.htm&quot || die $!;
while (<TEMPLATE>) {
if ($data1 > 900) {
my $sth1 = $dbh->prepare(&quot;SELECT tblB.data2, tblB.data3, tblC.data1
FROM (tblD INNER JOIN tblB ON tblD.data1 = tblB.data1) INNER JOIN tblC ON
tblD.data2 = tblC.data2
WHERE (((tblB.data1)=$data1));

$sth1->execute();
$sth1->bind_columns(undef, \$data2, \$data3, \$data4);
$sth1->fetch();
}
else if($data1 < 900) {
my $sth1 = $dbh->prepare(&quot;SELECT tblB.data2, tblB.data3, tblC.data1
FROM (tblE INNER JOIN tblB ON tblE.data1 = tblB.data1)
INNER JOIN tblC ON
tblE.data2 = tblC.data2
WHERE (((tblB.data1)=$data1));

$sth1->execute();
$sth1->bind_columns(undef, \$data2, \$data3, \$data4);
$sth1->fetch();

}
.......................(mostly html)

while($sth->fetch()){

...............(mostly html)



Thanks in advance for any help.
Cheryl

 

[hmerrill]

Cheryl, nothing is jumping out at me(yet) that could be wrong with your code - what errors or symptoms are you seeing? What is your level of experience with Perl DBI? Have you written other Perl DBI scripts that work? You didn't show your database connect statement - are you sure that you have a good connection to the database? Give some details about your setup - operating system, database, version of perl, version of DBI, version of web server, etc.
Hardy Merrill
Mission Critical Linux, Inc.

http://www.missioncriticallinux.com

 

[CherylD]

The DBI statement works. If I only use one statement then fetch with HTML it works, but it won't do it with the two statements. I get a forbidden page (500?). I have written a few perl scripts before. Mostly just do do a few update, insert or select statements and then spit out some html. The database is MySQL (a fairly new version, at least version 3), on UNIX...and I don't maintain the server so I'm not sure of the rest. Does this help?

 

[hmerrill]

One thing did just pop out at me - you have:

while (<TEMPLATE>) {
if ($data1 > 900) {
blah blah...
}
else if ($data1 < 900) { ### this may be a problem
blah blah
}

......................(mostly html)

while($sth->fetch()){

...............(mostly html)
=============================================
It could just be your copy and pasting, but it looks to me as if you may have a problem ending the &quot;if&quot; and &quot;while&quot; blocks correctly.

Also, when you say &quot;one statement&quot; or &quot;two statements&quot;, what do you mean? I'm still not clear about what works and what doesn't.
Hardy Merrill
Mission Critical Linux, Inc.

http://www.missioncriticallinux.com