CODEWITHSUNDEEP
phpmysqlrest
Kirill Ryabin
Kirill Ryabin

Reputation: 193

REST & MySQL offset selection

I have REST service and method which receives properties offset and count.

Example of url : blablabla?offset=10&count=10

Let's imagine that i am selecting data for infinite scroll and my site has many users so when i scrolls down i have new items in database it means that when i select offset 10 and count 10 it has new 10 items, so I'll select same data. Does MySQL has some tricks to prevent it, and select it correctly?

Upvotes: 0

Views: 110

Answers (1)

deceze
deceze

Reputation: 522382

Put your data into a defined, reproducible order; meaning sort it by something like date or id or so in which the order is stable every time you sort it. Then use the date/id/whatever of the last item in the list as "offset":

bla?last=12345&count=10

  SELECT *
    FROM foo
   WHERE id < [last id]
ORDER BY id DESC
   LIMIT [count]

In this case, when using ids, you need to make sure your ids are always incrementing so newer records always have a higher id than older records. Alternatively use another criterium to sort/offset by.

Upvotes: 1

Related Questions