Select COUNT in MySQL

[Anonymous Monk]

Hi Monks,

I want to find the quickest possible way to count the number of rows in a mysql db.

Heres part of my code that returns this "ARRAY(0x1ae8674)"

    my $sqlc = "SELECT COUNT(*) FROM company_info";
        my $crows= $dbh->selectrow_arrayref($sqlc);
            print $crows;

#I did not use prepare or execute since I heard selectrow_arrayref wra
+ps it around

I also tried using print $crows[0], but then it says it requires global symbol @crows.

What am I doing wrong?

Since this code and sql statement will only generate 1 row returned with 1 column--would selectcol_arrayref be better then selectrow_arrayref ?

Thanks

[robartes]

$crows is a reference to an array, so you have to dereference it to get at the array elements:

print $code->[0];

See perlref and perlreftut for more information on references in Perl

[dbwiz]

Alternatively, you may use selectrow_array to get a single value.

my ($crows) = $dbh->selectrow_array($sqlc);
print $crows;

Notice that $crows was declared in list context, to avoid side effects.

[tchatzi]

First of all you Have to use prepare and execute with select.
Only with select though, it makes the execution of the whole thing faster.
With everything else use 'do'

and second to do what you want, do it like this (the right way....)

my $dbh = DBI->connect('DBI:mysql:foo','foo','foo') 
          or die "Connect Error: $DBI::errstr";

my $count_sth = $dbh->prepare("SELECT COUNT(*) FROM company_info;") 
                or die "Prepare Count Error: $DBI::errstr\n";

$count_sth->execute() 
           or die "Execute Count Error: $DBI::errstr\n";

print $count_sth->fetchrow;

$count_sth->finish;
$dbh->disconnect;


Isn't it simple ?

[Ben Win Lue]

Hi, just try declaring

my @crows = ...

instead of

my $crows = ...

update
Oops I did not see the ref....
robartes is right