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.
Advertisement
Answer
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!