MySQL COUNT(*) always returns 1

critical posted this at 18:38 — 16th August 2003.

Joined: May 2002

Hi,

I'm using Perl to query a MySQL database on a Red Hat Linux server. I can successfully obtain and handle a result set, but I can't get COUNT(*) to work.

my $dbh = DBI->connect($dsn, $db_user, $db_pass); # works

my $sql = "SELECT COUNT(*) FROM groups";

my $aff = $dbh->do($sql);

But $aff always comes out equal to 1 even though I have multiple records in the table "groups". Same thing when I specify a WHERE clause - $aff always comes out = 1 no matter how many records match the WHERE clause.

What am I doing wrong?

Thanks.

critical posted this at 19:37 — 16th August 2003.

They have: 46 posts

Joined: May 2002

Well, I guess I don't fully understand how COUNT(*) is supposed to work.

The following code works:

my $sql = "SELECT COUNT(*) FROM groups";

my $sth = $dbh->prepare($sql);

$sth->execute();

my $rows = $sth->fetchrow_array();

$sth->finish;

$rows = the correct number of records matching the SELECT criteria. Is $rows the scalar "size" of an array? If so, what are the contents of that array? Is this the most efficient way to return a count? I can believe it took me two hours to get to this point....

Busy posted this at 22:34 — 16th August 2003.

He has: 6,151 posts

Joined: May 2001

I don't know Perl, but what about

count($sql);

or $sth

Mark Hensler posted this at 07:05 — 17th August 2003.

He has: 4,048 posts

Joined: Aug 2000

I'm not very fluent with perl either. I don't know anything about the perl DBI module.

I believe that whenever you assign an @array to a $scalar, the $scalar then contains the number of elements in the @array.

Mark Hensler
If there is no answer on Google, then there is no question.

m3rajk posted this at 14:46 — 18th August 2003.

They have: 461 posts

Joined: Jul 2003

i'm not familliar with how perl interacts with mysql.
does it pull the entire array?

if so, count the array.
if it pulls it one row at a time, is there someplace you can search for a function like php's mysql_num_rows or myysql_affected_rows?

POSIX. because a stable os that doesn't have memory leaks and isn't buggy is always good.

zollet posted this at 16:03 — 18th August 2003.

He has: 1,016 posts

Joined: May 2002

Shouldn't it be my @rows = $sth->fetchrow_array(); ? And then the result will be stored in $rows[0]

druagord posted this at 21:57 — 18th August 2003.