Re: Count Number of rows retrieved from SELECT

[trammell]

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.

[Akhasha]

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.

[monarch]

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..