Page 1 of 1
Most effective and efficient pagination style
Posted: Tue Sep 23, 2008 5:55 am
by someberry
I'm going to be making a generic pagination class, but was wondering how would be best to pass (or process in the class) the amount of results that have been found.
Basically, do you have to perform 2 queries - one to get how many results are possible and one to LIMIT the results? Or is there a way to combine them into one query, perhaps using a JOIN or some such?
Thank you

Re: Most effective and efficient pagination style
Posted: Tue Sep 23, 2008 6:17 am
by onion2k
There's two options... do two queries, one to find out the total results and another to fetch the limited recordset. That method is more memory efficient but it does require 2 queries. The other method is to fetch the entire recordset and then seek (with mysql_data_seek() if you're using a vanilla mysql connection) to the right place to start. That uses more memory but it's a little faster in most circumstances because it's only a single query.
Re: Most effective and efficient pagination style
Posted: Wed Sep 24, 2008 5:13 pm
by josh
SQL_CALC_ROWS flag,
Also for displaying the actual page links theres already tons of classes that handle that for you
Re: Most effective and efficient pagination style
Posted: Wed Sep 24, 2008 5:33 pm
by Christopher
Remember, you only need to do the COUNT(*) query once. After than you can pass it as a parameter or save it in the session.
Re: Most effective and efficient pagination style
Posted: Thu Sep 25, 2008 10:50 am
by VirtuosiMedia
I've been buried in JavaScript for about the last month, but one of the features I've used several times is the length function applied to an array to get the number of items in the array. PHP's equivalent is the sizeof($array) function. Might you be able to use that with a single query if the results array fetched is of the BOTH type? You could possibly eliminate COUNT altogether that way.
Re: Most effective and efficient pagination style
Posted: Thu Sep 25, 2008 1:42 pm
by Eran