BMaximus has asked for the wisdom of the Perl Monks concerning the following question:

I've been rumaging around PM and also the forums at DevShed. On devshed I found mention of using SQL_CALC_FOUND_ROWS within a SELECT statement to have the database return the actual number of rows found for a query as if there was no LIMIT put in it. In order to get that you need to retrieve it with SELECT FOUND_ROWS() after the main query you've run. So how would I run that query after the first one to retrieve that answer if SQL_CALC_FOUND_ROWS was used rather than running a separate query to count the rows without a LIMIT? I won't list the code for a normal DBI query, its been done adnausium on PM and we all know we can find it on DBI. Looking over the DBI docs it just seems that either I misunderstood the instructions but if I prepare another statement it destroys whatever I did before. So is there a way to retrieve the rows from SQL_CALC_FOUND_ROWS without any overly fancy code?

BMaximus

Replies are listed 'Best First'.
Re: using SQL_CALC_FOUND_ROWS with DBI
by clinton (Priest) on Jan 18, 2006 at 12:31 UTC
    Couldn't be easier.

    SELECT FOUND_ROWS() returns the number of rows that would have been found for the previous query executed using that DATABASE handle, not STATEMENT handle.

    clint

      Thank you clinton. So preparing a new statement won't destroy the previous result?

      BMaximus

        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.