How to retrieve the total number of rows when using LIMIT X, XXX

With php+mysql, $results -> num_rows returns the number of rows of the current query, however with LIMIT X, XXX in that query the $results -> num_rows only contains the LIMITED number of rows rather than the total amount of rows meeting the WHERE clause without LIMIT.

e.g. With LIMIT 0, 100 in my query $results -> num_rows returns 100 rather than 2000 which is the total number of rows had I not limited the returning results for paging.

Some search site has the capability of paging big amount of results while displaying also the total amount. Does it require 2 or more queries to do this? Is it possible to do it with just 1 query?

So what's the most efficient way to achieve this:

 

to return the total number just use another query without the limits , it will always return the limited number, use another var

 

Usually doing a "SELECT COUNT(id) FROM table;" will be the quickest way to perform the total count.

You could also use a stored procedure to run it, but either way it's a very quick query as long as you count on the primary key.

You can probably use a nested query as well, but this is generally a more resource intensive query and I think you would lose performance trying to do it in a single query.

 

Use the combination of SQL_CALC_FOUND_ROWS and FOUND_ROWS().

 

Thanks jestep and mwasif.

It's a great way using FOUND_ROWS()!