For pagination we generally execute two query ,first for geting resultset by using limit in sql query and second to count the total no. of records . For total no. of records we again execute the same query with count(*).
You would need two queries like these:
SELECT COUNT(*) FROM author WHERE name LIKE 'a%'; SELECT name, email FROM author WHERE name LIKE 'a%' LIMIT 10;
But if you have a complex query that joins several tables and takes a while to execute – well, you probably wouldn’t want to execute it twice and waste server resources.
Since MYSQL 4.0 we can use SQL_CALC_FOUND_ROWS option in query which will tell MySQL to count total number of rows disregarding LIMIT clause. In main query add SQL_CALC_FOUND_ROWS option just after SELECT and in second query use FOUND_ROWS() function to get total number of rows without executing the query.
Queries would look like this:
SELECT SQL_CALC_FOUND_ROWS name, email FROM author WHERE name LIKE 'a%' LIMIT 10; SELECT FOUND_ROWS();
Limitation: Must call second query immediately after the first one(or before next one) because SQL_CALC_FOUND_ROWS does not save number of rows anywhere.In the absence of the SQL_CALC_FOUND_ROWS option in the most recent successful SELECT statement, FOUND_ROWS() returns the number of rows in the result set returned by that statement.
Read the rest of this entry »
) is a Content Management System developed by Marc Palmer for JCatalog that is built on Grails. It is open source and easy to use. Apart from being customizable, the other thing that I liked about it was the ability to create custom content, and that too using grails conventions!!.
