PHP Basics: MySQL FOUND_ROWS()

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 LIMIT clause 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:

1
2
3
4
5
6
7
8
SELECT
id,
name,
full_title
FROM
articles
ORDER BY id
LIMIT 0,10

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:

1
2
3
4
5
$sql = "SELECT SQL_CALC_FOUND_ROWS id, name, full_title FROM articles ORDER BY id LIMIT 0,10";
$get = mysql_query($sql);
$sql_2 = "SELECT FOUND_ROWS()";
$found_rows = mysql_query($sql_2);

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.