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,157 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,044 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 ["Max Albert"] [Email]
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]
Saeed Sarvi [ Email | Profile ]
druagord posted this at 21:57 — 18th August 2003.
He has: 335 posts
Joined: May 2003
yes zollet that what it should be