else not working MySQL Perl statement

[monsterbuilt]

Hello,

I'm at a loss here and tried everything. All I want is "if" there are no results form the SQL statement to say so with my "else". any advice?

my $sth = $dbh->prepare(qq(select eviction_record_id,applicant_id,eviction_date,eviction_court,eviction_status,occurred_within_three_y ears,plantiff_name,address_of_eviction from eviction_record where applicant_id = '$applicant_id' )) or &dbdie;

$sth->execute or &dbdie;

while (($eviction_record_id,$applicant_id,$eviction_date,$eviction_court,$eviction_status,$occurred_within _three_years,$plantiff_name,$address_of_eviction) = $sth->fetchrow_array) {

if ($eviction_record_id) {

print qq (

<table id="simpleTable" >
<tr>
<th colspan="5" nowrap="nowrap" align="left">Evictions found:</th>
</tr>
<tr>
<th align="left" nowrap="nowrap">Eviction Date</th>
<th align="left" nowrap="nowrap">Plantiff Name</th>
<th align="left" nowrap="nowrap">Eviction Court</th>
<th align="left" nowrap="nowrap">Eviction Status</th>
</tr>

<tr>
<td align="left" nowrap="nowrap">$eviction_date</td>
<td align="left" nowrap="nowrap">$plantiff_name</td>
<td align="left" nowrap="nowrap">$eviction_court</td>
<td align="left" nowrap="nowrap"><b>$eviction_status</b></td>
</tr>
</table>
);

} else {

print qq (
<center>No eviction records found.</center>

);

}

}

[Nedals]

If there are no results then you will not cycle through the while loop.
Hence no return.

After the while loop, try...
unless ($eviction_record_id) {
print "......
}

but it does depend on what you expect back from your SQL statement

[monsterbuilt]

that would work until I reached that condition

go tit here.. - unless there is an even easier way :-)

FIRST: count the rows

my $sth = $dbh->prepare(qq(select eviction_record_id,applicant_id,eviction_date,eviction_court,eviction_status,occurred_within_three_y ears,plantiff_name,address_of_eviction from eviction_record where applicant_id = '$applicant_id' )) or &dbdie;

$sth->execute;
$numRows = $sth->rows;
$numFields = $sth->{'NUM_OF_FIELDS'};
$sth->finish;

SECOND: if number greater than zero

if ($numRows > 0) { Run SQL while }

THIRD: if zero

if ($numRows =~ /^0$/) { run no evictions text }

#############

##########################################################
### check for evictions #################################
##########################################################

################################################ select if evictions ###########
my $sth = $dbh->prepare(qq(select eviction_record_id,applicant_id,eviction_date,eviction_court,eviction_status,occurred_within_three_y ears,plantiff_name,address_of_eviction from eviction_record where applicant_id = '$applicant_id' )) or &dbdie;

$sth->execute;
$numRows = $sth->rows;
$numFields = $sth->{'NUM_OF_FIELDS'};
$sth->finish;

if ($numRows > 0) {

my $sth = $dbh->prepare(qq(select eviction_record_id,applicant_id,eviction_date,eviction_court,eviction_status,occurred_within_three_y ears,plantiff_name,address_of_eviction from eviction_record where applicant_id = '$applicant_id' )) or &dbdie;

$sth->execute or &dbdie;

while (($eviction_record_id,$applicant_id,$eviction_date,$eviction_court,$eviction_status,$occurred_within _three_years,$plantiff_name,$address_of_eviction) = $sth->fetchrow_array) {

if ($eviction_record_id) {

print qq (

<table id="simpleTable" >
<tr>
<th colspan="5" nowrap="nowrap" align="left">Evictions found: $numRows</th>
</tr>
<tr>
<th align="left" nowrap="nowrap">Eviction Date</th>
<th align="left" nowrap="nowrap">Plantiff Name</th>
<th align="left" nowrap="nowrap">Eviction Court</th>
<th align="left" nowrap="nowrap">Eviction Status</th>
</tr>

<tr>
<td align="left" nowrap="nowrap">$eviction_date</td>
<td align="left" nowrap="nowrap">$plantiff_name</td>
<td align="left" nowrap="nowrap">$eviction_court</td>
<td align="left" nowrap="nowrap"><b>$eviction_status</b></td>
</tr>
</table>
);

} else {

print qq (
<center>$numRows - No eviction records found.</center>

);

}
}

}


if ($numRows =~ /^0$/) {

print <<EOF;
<center>No eviction records found.</center>
EOF

}

######################## END select if evictions #######################################

##########################################################
### end check for evictions ##############################
##########################################################

[Nedals]

No sure, based on your response, if you solved the problem.

Here's my solution...
I'm making the assumption that your SELECT can return 0, 1, or more rows for each $applicant_id

my ($evictions,$any_rows) = (0,0);
my $sth = $dbh->prepare(qq{SELECT ... FROM eviction_record WHERE applicant_id=?}) or &dbdie;
$sth->execute($applicant_id) or &dbdie; ## using a placeholder - ?

while (my @row = $sth->fetchrow_array()) {
$any_rows = 1;
$evictions = 0;
if ($row[0]) { ## eviction_record_id
$evictions = 1;
# build and print the 'simpleTable' using row[xx]
# makes the code a little cleaner.

} else {
# print no evictions
}
}

if (!$any_rows && !$evictions ) { ## only prints if no rows found
#print no evictions
}