List:General Discussion« Previous MessageNext Message »
From:Daniel Koch Date:April 2 1999 8:31pm
Subject:Re: Counting rows returned from a SELECT with perl DBI.
View as plain text  
Brian Seitz wrote:

> I am creating a web-front end to search for database entries and I want
> to split the output into more than one screen (limit 1 , 20, limit 21 ,
> 40, etc.)  But in order to know when to stop allowing the user to press
> "next" and get another screen of results when there are no results left,
> I need to return how many total results exist without the limit
> statement.  Is there a better way to do this than executing the
> statement without the limit and doing a count loop (i.e., "while
> $row_ref = $sth->fetchrow_arrayref { $i++ } )?  It seems like there
> should be some DBI function that returns this automatically.
>

You can use $sth->rows, but MySQL's count(*) function is better:

#!/usr/local/bin/perl

use ACBJ::DB;
use Benchmark;

use strict;

use vars qw($dbh);

$dbh = ACBJ::DB->connect('dbname', 'hostname'); # A little wrapper function

my $dbi_rows_function = sub {

    my $sql = q(select * from story where market = ?);
    my $sth = $dbh->prepare($sql);
    $sth->execute('atlanta');
    my $rows = $sth->rows;
    $sth->finish;

};

my $mysql_count_function = sub {

    my $sql = q(select count(*) from story where market = ?);
    my $sth = $dbh->prepare($sql);
    $sth->execute('atlanta');
    my $rows = $sth->fetchrow_arrayref->[0];
    $sth->finish;

};

timethese(50, {
    dbi_rows_function => $dbi_rows_function,
    mysql_count_function => $mysql_count_function,
});

$dbh->disconnect;
__END__

(dkoch) % ./bench.pl
Benchmark: timing 50 iterations of dbi_rows_function, mysql_count_function...

dbi_rows_function: 76 wallclock secs ( 6.83 usr + 15.93 sys = 22.76 CPU)
mysql_count_function: 38 wallclock secs ( 0.12 usr +  0.03 sys =  0.15 CPU)




--
Dan Koch
Webmaster
American City Business Journals
http://www.amcity.com/


Thread
Counting rows returned from a SELECT with perl DBI.Brian Seitz2 Apr
  • Re: Counting rows returned from a SELECT with perl DBI.Sasha Pachev2 Apr
  • Re: Counting rows returned from a SELECT with perl DBI.Brian Seitz2 Apr
    • Re: Counting rows returned from a SELECT with perl DBI.Vivek Khera2 Apr
  • Re: Counting rows returned from a SELECT with perl DBI.Daniel Koch2 Apr
  • Re: Counting rows returned from a SELECT with perl DBI.Douglas Brantz3 Apr
  • Counting rows returned from a SELECT with perl DBI.Michael Widenius3 Apr