Today I was having a problem with the FOUND_ROWS() function using the DBI.pm with MySQL. I searched the Perl Monastery and only found this posting along with this one--neither of which were answered in depth. I then started to type a new posting to get help and in doing so, by cutting and pasting together the relevant code, I figured out my problem. For the benefit of others, I thought I'd post my code and some comments to this thread. My code also shows how to capture the results of the FOUND_ROWS() function, which is what BMaximus was asking for quite a while ago.
With my program, I was having problems retrieving the results of a SELECT FOUND_ROWS() statement. Below are the highlights of my Perl program. I created a separate function for each SELECT statement I'm running to retrieve data from MySQL since each is elaborate and very different--I've simplified the non-relevant SQL statements below. I also created a function (&search_count) for the FOUND_ROWS() function since I call it a few times.
The &search_count sub-routine would work the first time I would call it, but would return a result of 1 for subsequent calls--a value of 1 is returned by MySQL when there are no previous SELECT statements. The problem was that I have two separate database handles (i.e., $dbh1 and $dbh2) because I'm accessing two separate databases on two different, remote servers. What dawned on me as I was writing this posting and after reading clinton comments here is that I have to use the appropriate database handle to be able to get the correct results--see my if statement below.
#!/usr/bin/perl -w
use strict;
use DBI;
our $dbh1 = DBI->connect("DBI:mysql:db1:my_host.com",
"spenser", "my_pwd")
|| die "Failed: " . DBI->errstr;
our $dbh2 = DBI->connect("DBI:mysql:db1:my_host.com",
"spenser", "my_pwd")
|| die "Failed: " . DBI->errstr;
my ($results1) = &search_docs($keyword);
my ($count1) = &search_count('1');
my ($results2) = &search_docs($keyword);
my ($count2) = &search_count('2');
print "Total Possible for First Search: $count1", "\n",
"Total Possible for Second Search: $count2", "\n";
$dbh1->disconnect();
$dbh2->disconnect();
exit;
sub search_count {
my $search = shift;
$sql_stmnt = "SELECT FOUND_ROWS()";
# Change database handles as appropriate
if($search == 1) { $sth = $dbh1->prepare($sql_stmnt) }
else{ $sth = $dbh2->prepare($sql_stmnt) }
$sth->execute();
my ($count) = $sth->fetchrow_array();
$sth->finish();
return $count;
}
sub search_table1 {
my $keyword = shift;
$sql_stmnt = "SELECT SQL_CALC_FOUND_ROWS
col1, col2, col3
FROM table1
WHERE col_text LIKE '%keyword%'
LIMIT 1, 10";
$sth = $dbh1->prepare($sql_stmnt);
$sth->execute();
my $results = $sth->fetchall_arrayref();
$sth->finish();
return $results;
}
sub search_table2 {
my $keyword = shift;
$sql_stmnt = "SELECT SQL_CALC_FOUND_ROWS
col1, col2, col3
FROM table2
WHERE col_text LIKE '%keyword%'
LIMIT 1, 10";
$sth = $dbh2->prepare($sql_stmnt);
$sth->execute();
my $results = $sth->fetchall_arrayref();
$sth->finish();
return $results;
}
I hope this posting proves useful for someone else trying to resolve problems with the FOUND_ROWS() function. By the way, you will need a more recent version (i.e., 3.x) of the DBD::mysql (Thank you, Patrick Galbraith--a.k.a., another Gibbs brother). Older versions didn't work with the FOUND_ROWS() function. |