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

Hey monks, I am in a situation where I need to the count of rows returned by a 'SELECT' query. However, I have read that the DBI::rows function is unreliable at best. After having tried to think of other ways to get a row count on a query, the best I could come up with was: 
$sth = $dbh->prepare("SELECT name FROM people WHERE l_name LIKE 'B%'")
+;
$sth->execute();

while ( $name = $sth->fetchrow_array() ) {
    $count++;
}

[download]
I was wondering if there is a less verbose, and perhaps more efficient way of getting a row count. Processing the results of the query is completely unnecessary to this script, so it seems kind of silly to have to loop through all of them. Thanks in advance.

Amel - f.k.a. - kel

Replies are listed 'Best First'.
Re: DBI::rows
by Maestro_007 (Hermit) on Jun 29, 2001 at 01:16 UTC
    I agree that if you can, SELECT COUNT(name) WHERE ... works best. Also, depending on whether or not the NAME column is a primary key or otherwise unique value, you may want SELECT COUNT (DISTINCT name) WHERE ... to get a count of all the guaranteed unique names. But if for some reason you can't or you don't want to, you can use this snippet after your original query: 
    my $rows = $sth->fetchall_arrayref()
my $num_rows = scalar (@$rows);

    [download]
    It will return a reference to an AoA (Array of Arrays). The scalar is optional, of course. You can then use the data in $rows for actual manipulation, if the need arises...

    MM

[reply]
[d/l]
[select]
Re: DBI::rows
by LD2 (Curate) on Jun 29, 2001 at 00:47 UTC
    How about "SELECT COUNT(*) as Count FROM people WHERE l_name LIKE 'B%'"?

    That should work fine..
[reply]
[d/l]

      In fact, if you use Sybase, that is the only of counting the number of rows. In essence, the only way the Sybase engine can tell you how many rows would be returned by a select is to go and fetch them.


      --
      g r i n d e r
[reply]
(ichimunki) Re: DBI::rows
by ichimunki (Priest) on Jun 29, 2001 at 00:49 UTC
    From reading the DBI docs on this matter, it sounds like the best way to not have to count the rows is to switch to a DBMS that supports this function. Or on a more serious note, change your SQL to SELECT count( name ) FROM people WHERE l_name LIKE 'B%';
[reply]
[d/l]
Re: DBI::rows
by Centaurus (Novice) on Jun 29, 2001 at 01:08 UTC
    I believe the $sth->execute method returns the number of rows or -1 if no rows affected.
    You may also want to try the $sth->rows method.
[reply]
      In the leopard book (O'Reilly's DBI book), p. 223, it says that $sth->execute() only returns the number of rows affected by non-SELECT statements. In SELECT statements it "simply returns a true value".

      MM

[reply]
[d/l]
      Centaurus writes :
      I believe the $sth->execute method returns the number of rows or -1 if no rows affected. You may also want to try the $sth->rows method.
      These methods are unreliable in some databases -- they may only return an accurate count if all the rows have been read.
[reply]

Back to Seekers of Perl Wisdom