chop Posted April 25, 2018 Share Posted April 25, 2018 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. chop Link to comment Share on other sites More sharing options...
Larry Posted April 26, 2018 Share Posted April 26, 2018 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 More sharing options...
chop Posted April 26, 2018 Author Share Posted April 26, 2018 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. thanks! Link to comment Share on other sites More sharing options...
chop Posted April 27, 2018 Author Share Posted April 27, 2018 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 More sharing options...
Larry Posted April 28, 2018 Share Posted April 28, 2018 No problem! Thanks for sharing! Link to comment Share on other sites More sharing options...
Recommended Posts