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

Hi Monks!
I am trying to return the number of rows in this database table but I am getting a strange value from my code: 
...
      $sth = $dbh->prepare("select count(distinct all_id)
                            from users") or die ("Error in SQL\n");

      $sth->execute();
      my $counts = $sth->fetchrow_hashref();
      print "There are $counts in this table";

...

[download]
But I am getting this: HASH(0xcd3608)
Any help, thanks a lot!

Replies are listed 'Best First'.
Re: Rows Count Issue Help!
by ikegami (Patriarch) on Jan 20, 2011 at 17:30 UTC

    You didn't say which field the count was in, and you didn't close your statement handle (which you need to do if you don't read to exhaustion). You also have code that will never be called under RaiseError=>1, or missing error handling under RaiseError=>0.

    Fixed: 

    my $sth = $dbh->prepare("
   SELECT COUNT(DISTINCT all_id) AS cnt
     FROM users
");
$sth->execute();
my $count = $sth->fetchrow_hashref()->{cnt};
$sth->finish();

    [download]

    Simplified: 

    my $sth = $dbh->prepare("
   SELECT COUNT(DISTINCT all_id)
     FROM users
");
$sth->execute();
my $count = $sth->fetchrow_array();
$sth->finish();

    [download]

    Simplified further: 

    my $count = $dbh->selectrow_array("
   SELECT COUNT(DISTINCT all_id)
     FROM users
");

    [download]

    In all cases, RaiseError=>1 is assumed (although it's trivial to handle RaiseError=>0 in the last case).

[reply]
[d/l]
[select]
Re: Rows Count Issue Help!
by fisher (Priest) on Jan 20, 2011 at 16:35 UTC
    try this: 
    $sth = $dbh->prepare("select count(distinct all_id) as a from users") 
+or die ("Error in SQL\n");
$sth->execute();
my $counts = $sth->fetchrow_hashref();
print "There are ".$counts->{'a'}." in this table";

    [download]
    fetchrow_hashref() fetches the next row of data and returns it as a reference to a hash containing field name and field value pairs.
[reply]
[d/l]

Back to Seekers of Perl Wisdom