PHP/MySQL Pagination with one query

Question

The way I am doing my pagination now is as follow:

===============
First I do the normal query, and get all the result back.

After that I count the results, lets say 300. I do this with num_results

Now I check what page the user wants, and the limit, lets say 30,10.

Last I do the new query, with the limit selected.

===============

Using this method I do almost the same query twice, is there no other way to do this, in just one go.

Answer 1

Yes, you can use the SQL_CALC_FOUND_ROWS for exactly this purpose.

select SQL_CALC_FOUND_ROWS something from table limit 30,10

This way you can still get the amount of rows normally retrieved when NOT using the limit clause.