Find total number of results in mySQL query with offset+limit

Nyxynyx picture Nyxynyx · May 8, 2011 · Viewed 20.7k times · Source

I'm doing a pagination feature using Codeigniter but I think this applies to PHP/mySQL coding in general.

I am retrieving directory listings using offset and limit depending on how many results I want per page. However to know the total number of pages required, I need to know (total number of results)/(limit). Right now I am thinking of running the SQL query a second time then count the number of rows required but without using LIMIT. But I think this seems to be a waste of computational resources.

Are there any better ways? Thanks!

EDIT: My SQL query uses WHERE as well to select all rows with a particular 'category_id'

Answer

Dr.Molle picture Dr.Molle · May 8, 2011

Take a look at SQL_CALC_FOUND_ROWS