Jump to content
Larry Ullman's Book Forums
Sign in to follow this  
chop

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.

chop

 

Share this post


Link to post
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. 

Share this post


Link to post
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.

 

thanks!

Share this post


Link to post
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!

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...