CODEWITHSUNDEEP
sqlmysqllimit
Tristan
Tristan

Reputation: 3885

MySQL return result count along with all results or check if query limited

I have a query that returns a large amount of results, so i'm limiting the amount returned. But I want to find out if there were more results then the number I limited them to, either by getting all the results back with a count(*) or some way of determing if the results were limited all in the same query as the one that returns the results!

Upvotes: 1

Views: 1093

Answers (2)

Ronnis
Ronnis

Reputation: 12843

If you don't care about how many more rows there are, you can also just add 1 to the limit. Say you wanted to display 100 rows per page. So you limit by 101. If at anytime you receive 101 rows, you know that there is at least one more page.

Obviously, you have to discard the extra row each time, which adds some extra complexity to the application code.

Upvotes: 5

OMG Ponies
OMG Ponies

Reputation: 332641

Use the FOUND_ROWS function after the query that uses the LIMIT:

SELECT FOUND_ROWS();

From the documentation:

A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. To obtain this row count, include a SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward:

Upvotes: 5

Related Questions