Jump to content
Larry Ullman's Book Forums

Pagination In Mysql Queries

Recommended Posts

I have run into a bit of a problem where I get repeated row results on separate pages within a paginated SELECT query.



It works okay if I sort on, say, the unique "pet_id" column. But if I SELECT and sort by a column that can have similar items in the column (such as sort by state), it's very possible that results for row 15 (which comes up on page 4), for example,  also shows up on page 7.


This is a problem not just because things are repeated within the same query, but also, because

I am using "LIMIT $start, $display", and if one $result row gets on the page twice, that means that another row (that should appear) is not getting on at all!


I tried using SELECT DISTINCT but, of course, that is for a use within a single query. Every page number in the pagination scheme is a separate query.


Thanks for any offerings.



Link to comment
Share on other sites

Ah, interesting one! The trick here is to find a reliable way to consistently order the results. If, say, "ORDER BY state" doesn't do it, then you should either sort by a different column or sort by state and a second column. That's probably not "ORDER BY state, pet_id", but something along those lines. 

Link to comment
Share on other sites

Yes, exactly! This worked perfectly:


$order_by = 'pet_id DESC, town_lost DESC';


First, order by the primary key "pet_id", then the town names (it's really towns I use not states) follow in the same order. No duplication, no missed data.


I never knew I could nest one ORDER BY inside another.



Link to comment
Share on other sites

Correction the the above post.

To clarify, if I want to order the output by the town in which a particular pet was lost (column= town_lost) AND at the same time not get duplicate data returned (because there may be many returns in the "town_lost" column that are not unique) I have to include the unique column "pet_id" which is my primary key.


The correct way to do this is:

$order_by = 'town_lost ASC, pet_id';

with the primary key being the second sort and with no ASC or DESC


In other words, Larry had it correct... sorry Larry!

Link to comment
Share on other sites


  • Create New...