Checking return results from an execute

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

Hi all,

What I want to do is check to see how many rows were returned from a database select using the DBI. If it is only one row I want to do one thing, if it is more than 1 row I want to do something completely different. So how do I find out how many rows I have gotten back before I start printing out data? Is there a way to check that at the execute stage? Or somewhere between execute and the fetch?

Thanks for any help provided.

Comment on Checking return results from an execute

Replies are listed 'Best First'.
Re: Checking return results from an execute by chromatic (Archbishop) on Jun 28, 2002 at 21:31 UTC
With some databases, you can call `rows()` on the statement handle before fetching results. That doesn't work on all of them. Executing a 'SELECT COUNT(*)' is more reliable.	[reply] [d/l]
Re: Re: Checking return results from an execute by ides (Deacon) on Jun 28, 2002 at 21:52 UTC
I thought it was worth mentioning that I have found 'SELECT COUNT(1) FROM TABLE' to be faster on certain databases than 'SELECT COUNT(*) FROM TABLE'. ----------------------------------- Frank Wiles <frank@wiles.org> http://frank.wiles.org	[reply]
Re: Checking return results from an execute by tadman (Prior) on Jun 28, 2002 at 21:57 UTC
Something like this is probably what you're thinking: `my $sth = $dbh->prepare($sql); # In "real" code you'd test $rv to make sure things are OK my $rv = $sth->execute(); if ($sth->rows() == 1) { # Something (fetch, etc.) } else { # Something else }` [download]	[reply] [d/l]
Re: Checking return results from an execute by aersoy (Scribe) on Jun 28, 2002 at 21:45 UTC
Hello, It is highly dependant on the database backend you use, but I guess `my $sth = $dbh->prepare("SELECT * FROM foo"); $sth->execute(); my $num_rows = $sth->rows;` [download] is what you are looking for. I know it works with MySQL at least. You should check the man page of your database driver (ie. DBD::mysql) to learn more about it. -- Alper Ersoy	[reply] [d/l]