How to get the SQL_CALC_FOUND_ROWS value using prepared statements?

Question

I'm currently scratching my head at how to implement SQL_CALC_FOUND_ROWS with prepared statements.

I'm writing a pagination class and obviously i want to add LIMIT to the query but also find what the total number of rows would be.

Here's an example from the class in question.

$query = "select SQL_CALC_FOUND_ROWS id,title,location,salary,employer from jobs where region=38 limit 0,3";

if($stmt = $connection->prepare($query)) {
    $stmt->execute()or die($connection->error); //execute query
    $stmt->bind_result($id,$title,$location,$salary,$employer,$image);
    while($stmt->fetch()){
        $jobs[$x]['id']=$id;
        $jobs[$x]['title']=$title;
        $jobs[$x]['location']=$location;
        $jobs[$x]['salary']=$salary;
        $jobs[$x]['employer']=$employer;
        $jobs[$x]['image']=$image;
        $x++;
    }
    $stmt->close();//close statement
}

I'm a bit stumped as to how to get the SQL_CALC_FOUND_ROWS actual value? I had thought adding in something like:

$stmt->store_result();
$count=$stmt->num_rows;

But that only gives a number based on the LIMIT, so in the above example its 3 rather than the full 6 that it should be.

Ok, i have since found out i need to use SELECT FOUND_ROWS() but im still unsure how to implement this? Do i need to run it like a completely new query? — cosmicsafari, Oct 24 '12 at 13:21

score 9 · Accepted Answer · answered Oct 24 '12 at 13:50

Managed to figure it out, i will detail my answer below for anyone whos interested in future.

Original Code

$query="select SQL_CALC_FOUND_ROWS id,title,location,salary,employer from jobs where region=38 limit 0,3";

if($stmt = $connection->prepare($query)) {
        $stmt->execute()or die($connection->error); //execute query
        $stmt->bind_result($id,$title,$location,$salary,$employer,$image);
        while($stmt->fetch()){
            $jobs[$x]['id']=$id;
            $jobs[$x]['title']=$title;
            $jobs[$x]['location']=$location;
            $jobs[$x]['salary']=$salary;
            $jobs[$x]['employer']=$employer;
            $jobs[$x]['image']=$image;
            $x++;
        }
        $stmt->close();//close statement
    }

Updated Code

$query="select SQL_CALC_FOUND_ROWS id,title,location,salary,employer from jobs where region=38 limit 0,3";

if($stmt = $connection->prepare($query)) {
        $stmt->execute()or die($connection->error); //execute query
        $stmt->bind_result($id,$title,$location,$salary,$employer,$image);
        while($stmt->fetch()){
            $jobs[$x]['id']=$id;
            $jobs[$x]['title']=$title;
            $jobs[$x]['location']=$location;
            $jobs[$x]['salary']=$salary;
            $jobs[$x]['employer']=$employer;
            $jobs[$x]['image']=$image;
            $x++;
        }
            //get total number of rows.
            $query="SELECT FOUND_ROWS()";
            $stmt = $connection->prepare($query);
            $stmt->execute();
            $stmt->bind_result($num);
            while($stmt->fetch()){
                $count=$num;
            }

        $stmt->close();//close statement
    }

Probably could do it better another way but couldn't seem to find any good examples anywhere online and this works!

You don't need to use the "while" as it's just returning a single row — , Mar 14 '13 at 23:36

Dharman · Answer 2 · 2019-12-27T21:31:35.510

If you want to get the result of SQL_CALC_FOUND_ROWS you need to run query SELECT FOUND_ROWS() in MySQL. To do that you don't need prepared statement. You can just use query() method.

$connection->query('SELECT FOUND_ROWS()')->fetch_row()[0];

If you are using mysqlnd, which you should be using if you are on one of the supported PHP versions, you can make your code much simpler.

// Enable error reporting instead of using or die($connection->error)
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$query = "SELECT SQL_CALC_FOUND_ROWS id,title,location,salary,employer 
    FROM jobs 
    WHERE region=38 
    LIMIT 0,3";
$stmt = $connection->prepare($query);
$stmt->execute();
$jobs = $stmt->fetch_all(MYSQLI_ASSOC);

// There is no need for prepared statement when using FOUND_ROWS()
$connection->query('SELECT FOUND_ROWS()')->fetch_row()[0];

As you can see, it is much cleaner to use fetch_all() rather than while loop.

Warning

The SQL_CALC_FOUND_ROWS query modifier and accompanying FOUND_ROWS() function are deprecated as of MySQL 8.0.17 and will be removed in a future MySQL version. As a replacement, considering executing your query with LIMIT, and then a second query with COUNT(*) and without LIMIT to determine whether there are additional rows. For example, instead of these queries:
SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10;
SELECT FOUND_ROWS(); 
Use these queries instead:
SELECT * FROM tbl_name WHERE id > 100 LIMIT 10;
SELECT COUNT(*) WHERE id > 100;

How to get the SQL_CALC_FOUND_ROWS value using prepared statements?

2 Answers2

Linked