R – How to check if more than one record has been returned from DBI query in Perl

countdbihashperl

I searched for this on the site already but couldn't locate anything that answered my question. Which brings me to the details:

I am querying database using a simple select query, realistically (given the scenario) it should never return more than about 5 to 6 records. I just want to check if more than one has been returned. And if more than one has been return act upon that (in an else statement) if only one has been returned then I progress with the script. 


my $sql = qq { SELECT col1, col2, col3
                 FROM table
            WHERE col1 = 'foo'
          };

my $sth = $dbc->prepare_cached($sql) or die "Could not prepare statement: " . $dbc->errstr;

$sth->execute() or die "Could not execute statement: " . $sth->errstr;

# Not sure how to efficiently check for more than one row returned and still progress if true... This is my problem! I'm thinking a nested if to see if any rows were returned, and then progress with the check of one or more rows? But how can this me checked (like a count function?)
if (my $ref = $sth->fetchrow_hashref()) {

 # One row was returned...

} else {
 # More than one row was returned... Uh oh!
}

If you guys know of a thread that I couldn't locate that answers this question simply redirect my and I'll nullify this thread!

Regards,
BorisTheBulletDodger!

Best Answer

my $sth = $dbh->prepare("SELECT col1, col2, col3 FROM tablename");
$sth->execute();
my $rows = $sth->fetchall_arrayref({});
if (@$rows == 0) {
    die "no rows returned";
} elsif (@$rows > 1) {
    die "too many rows returned";
}
my $row = $rows->[0];
print "col1 is $row->{col1}\n";
Related Topic