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

php mysql codeigniter pagination
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

Related questions

CodeIgniter: Unable to connect to your database server using the provided settings Error Message

I have been using CI just fine using the MySQL driver. I want to use the MySQL driver instead, but as soon as I change it (just add the ‘i’ at the end of MySQL, and added the port number) …

Read More
insert multiple rows via a php array into mysql

I'm passing a large dataset into a MySQL table via PHP using insert commands and I'm wondering if its possible to insert approximately 1000 rows at a time via a query other than appending each value on the end of a …

Read More
CodeIgniter: How To Do a Select (Distinct Fieldname) MySQL Query

I'm trying to retrieve a count of all unique values in a field. Example SQL: SELECT count(distinct accessid) FROM (`accesslog`) WHERE record = '123' How can I do this kind of query inside of CodeIgniter? I know I can …

Read More