Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get the SQL_CALC_FOUND_ROWS value using prepared statements?

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.

like image 473
cosmicsafari Avatar asked Oct 24 '12 12:10

cosmicsafari


People also ask

What is Sql_calc_found_rows?

MySQL has a nonstandard query modifier called SQL_CALC_FOUND_ROWS. When in use on a SELECT with LIMIT, it attempts to calculate how many rows would have been returned if the limit were not there, and then store that for later retrieval in FOUND_ROWS(). SQL_CALC_FOUND_ROWS has a number of problems.

Why use SQL_ CALC_ FOUND_ ROWS?

SQL_CALC_FOUND_ROWS and FOUND_ROWS() can be useful in situations when you want to restrict the number of rows that a query returns, but also determine the number of rows in the full result set without running the query again.


1 Answers

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!

like image 175
cosmicsafari Avatar answered Sep 22 '22 10:09

cosmicsafari