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/
