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:
That’s it. Make sure to use the second query straight after the first or the result count will be lost.