Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

Count Number of rows retrieved from SELECT

by dtharby (Acolyte)
on Jun 13, 2005 at 11:05 UTC ( [id://466108]=perlquestion: print w/replies, xml ) Need Help??

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

Hi All,
Can't seem to find the answer to this one..
Just need to count the number of rows retrieved one executing SELECT statement under DBI.
Is it just a cause of getting the values from $rows ?
Thanks
Danny

Replies are listed 'Best First'.
Re: Count Number of rows retrieved from SELECT
by monarch (Priest) on Jun 13, 2005 at 11:14 UTC
    Um, the way I do it is to modify the SELECT statement to do the counting for me. i.e.
    $sql = "SELECT count(*) FROM table WHERE name = 'Jamie'"; $sth = $dbh->prepare( $sql ); if ( $sth && $sth->execute() ) { $row = $sth->fetchrow_arrayref(); print( "Returned " . $row->[0] . " row(s)\n" ); } else { print( "Error " . $dbh->errstr() . "\n" ); exit( 1 ); }
      I was hoping not to have to do another SELECT statment just to get the number of rows that was pulled with the first statement.
      Oh well... will continue to play, there must be a way
        Can you include a counter (this is how I've done it in the past..)?
        my $count = 0; while ( my $row = $sth->fetchrow_arrayref() ) { $count++; # do something with $row.. } print( "There were $count row(s)\n" );

        I know what you're asking and it's a worthy question, I just haven't tried working it out for myself because I've just used either of the two techniques I've mentioned instead.

Re: Count Number of rows retrieved from SELECT
by trammell (Priest) on Jun 13, 2005 at 16:11 UTC
    Not sure how well this is supported across various databases, but in my version of MySQL, the result of the execute() call returns the record count:
    #!perl -l use strict; use warnings; use Data::Dumper; require 'getdbh.pl'; my $dbh = get_dbh(); $dbh->do('USE test'); $dbh->do('CREATE TEMPORARY TABLE t1 (name char(8))'); for (qw/ Jim Judy Steve Jack Jodie Sally Alice /) { $dbh->do('INSERT INTO t1 VALUES (?)', undef, $_); } for (qw/ J S A X /) { my $sth = $dbh->prepare("SELECT name FROM t1 where name like '$_%' +"); my $x = $sth->execute(); print "$_: \$x = $x"; } __END__ J: $x = 4 S: $x = 2 A: $x = 1 X: $x = 0E0
    Update: I'm not sure why this keeps getting downvoted. I know the documentation in DBI says that execute() is only guaranteed to return a true value on success, but a little poking around in DBD::mysql makes me think this is a real feature, if a poorly documented one. I'd be grateful if someone would take the time to explain the --'s.
      Without close inspection of the DBD::mysql source and possibly researching the MySQL API, I still wouldn't rely on this. Quite possibly for large queries that don't require sorting, the execute() call could return while at the back end the query is still matching rows.
      Downvotes can occasionally be a mystery!

      I think your comment on mysql is a good one, although I personally wouldn't rely on poorly documented features if I could avoid it..

Re: Count Number of rows retrieved from SELECT
by thcsoft (Monk) on Jun 13, 2005 at 12:28 UTC
    my $res = $dbh->selectall_hashref($stmt, $key); print scalar(keys %$res);
    or
    my $res = $dbh->selectall_arrayref($stmt); print scalar(@$res);

    just try read the f... manual. ^^

    language is a virus from outer space.
Re: Count Number of rows retrieved from SELECT
by aukjan (Friar) on Jun 13, 2005 at 11:13 UTC
    man DBI, then search for 'rows' (DBI::rows).
    Disregard my comment... Thanks for setting me straight dbwiz. I must be more carefull answering questions. I have done little with DBI and always thought that this worked... This means I will have to look at some of my old code... and learn to read the docs ;)

    .:| If it can't be fixed .. Don't break it |:.

      the rows method DOES NOT return the number of rows retrieved by SELECT.

      You are advising about reading the DBI docs. But have you really read them?

      rows

      Returns the number of rows affected by the last row affecting command, or -1 if the number of rows is not known or not available.

      Generally, you can only rely on a row count after a non-SELECT execute (for some specific operations like UPDATE and DELETE), or after fetching all the rows of a SELECT statement.

      For SELECT statements, it is generally not possible to know how many rows will be returned except by fetching them all. Some drivers will return the number of rows the application has fetched so far, but others may return -1 until all rows have been fetched. So use of the rows method or $DBI::rows with SELECT statements is not recommended.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://466108]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others musing on the Monastery: (8)
As of 2024-03-28 09:58 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found