MySQL pagination without double-querying?
I was wondering if there was a way to get the number of results from a MySQL query, and at the same time limit the results.
The way pagination works (as I understand it), first I do something like
query = SELECT COUNT(*) FROM `table` WHERE `some_condition`
After I get the num_rows(query), I have the number of results. But then to actually limit my results, I have to do a second query like:
query2 = SELECT COUNT(*) FROM `table` WHERE `some_condition` LIMIT 0, 10
My question: Is there anyway to both retrieve the total number of results that would be given, AND limit the results returned in a single query? Or any more efficient way of doing this. Thanks!
Answer
I almost never do two queries.
Simply return one more row than is needed, only display 10 on the page, and if there are more than are displayed, display a "Next" button.
SELECT x, y, z FROM `table` WHERE `some_condition` LIMIT 0, 11
// iterate through and display 10 rows.
// if there were 11 rows, display a "Next" button.
Your query should return in an order of most relevant first. Chances are, most people aren't going to care about going to page 236 out of 412.
When you do a google search, and your results aren't on the first page, you likely go to page two, not nine.