Take the 2-minute tour ×

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'

share|improve this question

3 Answers 3

up vote 10 down vote accepted

Take a look at SQL_CALC_FOUND_ROWS

share|improve this answer

SELECT COUNT(*) FROM table_name WHERE column = 'value' will return the total number of records in a table matching that condition very quickly.

Database SELECT operations are usually "cheap" (resource-wise), so don't feel too bad about using them in a reasonable manner.

EDIT: Added WHERE after the OP mentioned that they need that feature.

share|improve this answer
Sorry, I forgot to add that my SQL query uses WHERE as well to select all rows with a particular 'category_id' –  Nyxynyx May 8 '11 at 16:17
nyx, wouldn't SELECT COUNT(*) FROM table_name WHERE condition still work? –  Nick ODell May 8 '11 at 16:18
SELECT COUNT(id) FROM `table` WHERE `category_id` = 9

Gives you the number of rows for your specific category.

share|improve this answer

Your Answer


