Get total number of rows when using LIMIT

Question

Been following examples from here and many other sites but not getting the expected results

Snippet of my PHP code;

$query="SELECT * FROM book";
$result=mysql_query($query);
echo mysql_num_rows($result); //returns 14


$query="SELECT FOUND_ROWS()";
$rtotal=mysql_query($query);
echo mysql_result($rtotal,0); //returns 14


$query="SELECT * FROM book LIMIT 0,4";
$result=mysql_query($query);
echo mysql_num_rows($result); //returns 4


$query="SELECT FOUND_ROWS()";
$rtotal=mysql_query($query);
echo mysql_result($rtotal,0); //returns 4 Why is it 4 and not 14??

In the last example why is it 4 and not 14?? What am I doing wrong?

Answer 1

Add a SQL_CALC_FOUND_ROWS to your select statement: http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_found-rows

This forces MySQL to count the rows as if the limit statement is not present.

$query="SELECT SQL_CALC_FOUND_ROWS * FROM book LIMIT 0,4";