{"id":5559,"date":"2012-05-17T17:21:11","date_gmt":"2012-05-17T11:51:11","guid":{"rendered":"http:\/\/www.tothenew.com\/blog\/?p=5559"},"modified":"2017-05-10T16:53:29","modified_gmt":"2017-05-10T11:23:29","slug":"get-totalcount-of-records-when-using-limit-in-one-query","status":"publish","type":"post","link":"https:\/\/www.tothenew.com\/blog\/get-totalcount-of-records-when-using-limit-in-one-query\/","title":{"rendered":"get totalCount of records when using limit in one Query"},"content":{"rendered":"

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(*).<\/p>\n

You would need two queries like these:<\/p>\n

[java]
\nSELECT COUNT(*) FROM author WHERE name LIKE ‘a%’;<\/p>\n

SELECT name, email FROM author WHERE name LIKE ‘a%’ LIMIT 10;
\n[\/java]<\/p>\n

But if you have a complex query that joins several tables and takes a while to execute \u2013 well, you probably wouldn\u2019t want to execute it twice and waste server resources.<\/p>\n

Since MYSQL 4.0 we can use SQL_CALC_FOUND_ROWS<\/strong> option in\u00a0 query which will tell MySQL to count total number of rows disregarding LIMIT clause<\/em><\/strong>. In main query add SQL_CALC_FOUND_ROWS<\/strong> option just after SELECT and in second query\u00a0 use FOUND_ROWS()<\/strong> function to get total number of rows without executing the query.<\/p>\n

Queries would look like this:<\/p>\n

[java]
\nSELECT SQL_CALC_FOUND_ROWS name, email FROM author WHERE name LIKE ‘a%’ LIMIT 10;<\/p>\n

SELECT FOUND_ROWS();
\n[\/java]<\/p>\n

Limitation:<\/strong> 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.
\n<\/p>\n","protected":false},"excerpt":{"rendered":"

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: [java] SELECT COUNT(*) FROM author WHERE name LIKE […]<\/p>\n","protected":false},"author":45,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":22,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-5559","post","type-post","status-publish","format-standard","hentry","category-technology"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t