I’ve seen this come up a few times in #php on irc.gamesurge.net:
How to find the total number of rows found, while using the
LIMITclause on a query.
I’ve seen people respond with some seriously incorrect solutions, such as ‘Simply load all of the results into an array and only show X amount’ or ‘Simple run another query without the LIMIT clause and count those rows.’
Those two, common, solutions are sadly not the best solution.
Take for example the following query:
Now, that looks all fine and dandy, you have probably used something very similar to that query many times. Now, say we want to actually show how many articles we have in our database?
We would add
SQL_CALC_FOUND_ROWS to our query. Here’s a working example with PHP:
So we’re running two queries, one to get the data and a second to discover the total row count, had the
LIMIT not been there.
So next time you’re looking to do a quick and pagination script, go ahead and try this technique out.