1

The scenario is to list the photos uploaded by an User and his friends sorted by upload datetime. I have a MySql table Photo which is dynamic and contains the photos uploaded by all users.

Initially i get 10 rows of data by the below query. By the time i get next 10 rows, users will have uploaded more photos. So how can i select the next 10 rows starting from the row i already got before? 

select * from photo p where p.handle in (select handle from friends where user = '$handle') or p.handle='$handle' order by uldatetime desc limit ".$start.", 10;
Improve this question
2
  • @b0s3 Yeah, even if i do that. Theres a chance for getting rows i already got. Commented Oct 8, 2015 at 7:53
  • 1
    That is nothing you can solve by means of the database engine. For this you have to keep track inside your session and remember the last photo (by its ID) you picked. Then you can add another WHERE clause selecting only photos with a higher ID. Commented Oct 8, 2015 at 7:56

1 Answer 1

Reset to default
1

You must be having some Auto increment primary key in your database. And say you are fetching the records n reverse order,. say you had 50 records when you started displaying the results

  1. you fetched record from 50 to 41
  2. Mean while 5 more records got inserted so your last Id increased from 50 to 55
  3. now instead of displaying last 20 records fetch last 10 records having the primary key < 41 thus it will give you 40 to 31 irrespective of the records being inserted in the mean time

So your query would be updated as

select * from photo p where p.handle in (select handle from friends where user = '$handle') or p.handle='$handle' AND photo.[primarykey of photo table] < [last photo id displayed till now] order by uldatetime desc limit 0, 10;

replace [last photo id displayed till now] and [primarykey of photo table] with the respective variable and field.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Um, by this method i'm getting the data for very first time by having last photo id as 999999. Can you suggest me a better way?
for the second time send last photo id = 999999-10 and then for the third time send last photo id = 999999-20 and so on

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.