How to get the SQL_CALC_FOUND_ROWS value using prepared statements?

mysql, mysqli, php, prepared-statement, sql-calc-found-rows

Solution

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!

Problem

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.

Original source