testing for retuning value of DBI querry is empty

demonlazeros has asked for the wisdom of the Perl Monks concerning the following question:

I have several tables that may or may not contain data at any given time. I need to query eatable to see if a $userid resides in each table. I have a loop that queries each table for the $userid varable. It may be that there is no data in one or more tables but I need to know if that is the case then I can wriote to the browser that the $userid was not found in that table then e loop goes on to to the next table which may have the $userid in it which would then tell the user he is in that particular table. But for some reason if the table is empty and the query return has nothing in it I cant test for the $userid; it gives me nothing. Here is the code I am using:

#======================= [ open_dbi ] ======================= 
my $sql = "SELECT * FROM $course";
my $query=$dbh->prepare("$sql")
    or die "Couldn't prepare statement: " . $dbh->errstr;
if (defined($query)) {
    $query->execute();

######################################################################
+#########

while (@row=$query->fetchrow_array()) {
        foreach  ($row[0]) {
        print "$course";        
        if ($row[0] eq $userid) {
        
        print "$userid you are registered for this course $course<BR>"
+;        
    
        }elsif ($row[0])
            {print "$course";}
        
        }
}

######################################################################
+#########
$dbh->disconnect;
[download]

(The variable $course is brought in from another db search.) The code above fails to execute the else statement if there is no data in the table, if there is data the code works. Ive tried everything I can think of. I need to be able to tell the student that he is registered this course but not for these.

Comment on testing for retuning value of DBI querry is empty Download Code

Replies are listed 'Best First'.
Re: testing for retuning value of DBI querry is empty by pc88mxer (Vicar) on Apr 22, 2008 at 17:20 UTC
The code above fails to execute the else statement if there is no data in the table The body of the `while` statement will be executed for each row returned by the query. If there are no rows returned, nothing in the `while` loop will get executed. You need to keep track of whether the `while` loop found the data you are looking for: `my $found; while (@row = $query->fetchrow_array) { ... if (...found desired data...) { $found = 1; # might also exit loop here with 'last' } } if ($found) { # data was found } else { # data was not found }` [download] But, why have perl do the searching when you can have the database do it? Just add a `WHERE` clause to your query: `my $sql = "SELECT * FROM $course WHERE column = ?"; my $sth = $dbh->prepare($sql); $sth->execute($userid); if (@row = $sth->fetchrow_array) { # row was found where column == $userid } else { # no such row found }` [download]	[reply] [d/l] [select]
Re^2: testing for retuning value of DBI querry is empty by demonlazeros (Acolyte) on Apr 22, 2008 at 17:54 UTC
I completely whent blank on the WHERE conditinal !!! Gonna try it! I'm sure it will work, else could still try the Perl path as a good Perl Monk should.	[reply]