Is this possible to get total number of rows count

Question

Hey Guyz Is this possible to get total number of rows count with offset limit

Scenario

SELECT * FROM users limit 0,5; 

This Query contain 300 records but the issue is if i call this query with offset the result will be show only 5 record and i don't want to write a Query in twice time. one for paging limit and other for total no of record count...

I don't want this

SELECT * FROM users limit 0,5; // paging 
SELECT count(*) FROM users; // count 

i have to merge this Queries or helps are definitely appreciated

Answer 1

You can use SQL_CALC_FOUND_ROWS like this

SELECT SQL_CALC_FOUND_ROWS * FROM users limit 0,5;

It gets the row count before applying any LIMIT clause. It does need another query to fetch the results but that query can simply be

SELECT FOUND_ROWS()

and hence you don't have to repeat your complicated query.