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.

In reply to Re: using SQL_CALC_FOUND_ROWS with DBI by Spenser
in thread using SQL_CALC_FOUND_ROWS with DBI by BMaximus

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.