PHP & MySQL Tip #2 – Better Way of Pagination
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.
Read on to discover my solution.
Take for example the following query:
$sql = "SELECT id as id, name as name, full_title as 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 simply change that query to:
$sql = "SELECT SQL_CALC_FOUND_ROWS id as id, name as name, full_title as title FROM articles ORDER BY id LIMIT 0,10";
$get = mysql_query($sql) or die(mysql_error());
$sql_2 = "SELECT FOUND_ROWS();";
$found_rows = mysql_query($sql_2) or die(mysql_error());
Now yes, we're running two queries, but we're not actually queries against any rows. So therefore, this solution is more elegant and ultimately more important, it's not attempting to scan any tables for data.
So next time you're looking to do a quick and pagination script, go ahead and try this technique out.
No comments yet.