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 );
}
| [reply] [d/l] [select] |
|
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
| [reply] |
|
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. | [reply] [d/l] |
|
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. | [reply] [d/l] |
|
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.
| [reply] |
|
| [reply] |
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.
| [reply] [d/l] [select] |
Re: Count Number of rows retrieved from SELECT
by aukjan (Friar) on Jun 13, 2005 at 11:13 UTC
|
| [reply] [d/l] |
|
| [reply] [d/l] |