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;
}
[download]

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.