When paginating data on your website you’ll probably have a query something like the following:

SELECT *
FROM my_table
LIMIT 0,50

However in doing so, chances are you’ll want to have a ‘Showing x-y of z rows‘ clause at the top of your table. Retrieving that z value can be quite a hassle – it’d be very ugly to need to execute the same query again sans the LIMIT clause. Recently I’ve come across an easy way of accomplishing this in MySQL – the FOUND_ROWS() function.

To use it is simple. In your first query place SQL_CALC_FOUND_ROWS after the SELECT keyword like so:

SELECT SQL_CALC_FOUND_ROWS *
FROM my_table
LIMIT 0,50

Then immediately afterwards execute this query to return the total number of rows before the LIMIT clause was executed:

SELECT FOUND_ROWS()

That’s it. Make sure to use the second query straight after the first or the result count will be lost.