CODEWITHSUNDEEP
mysqldatabase
sid
sid

Reputation: 15

Fetching next and previous records from database

When I fetch a specific record from db table, I want to fetch the "previous" and "next" records as well. For example I have a photos table:

photo_id    user_id     photo_name      
1             1         xx
2             2          --
3             1          --
4             1          --

Now for example, I want to fetch photos of user_id 1. For example if the photo_id is 1, there wont be any previous photo, if photo_id is 3, the previous record will be photo_id 1 and next will be 4. Thank you for any help.

Upvotes: 1

Views: 490

Answers (3)

JWL
JWL

Reputation: 14191

Here is the required SQL in just one query - allows you to fetch all the 3 photos you want at a go (if they exist):

SELECT p.`photo_id` AS photo, 
(SELECT o.`photo_id` FROM photos o WHERE o.`user_id` = p.`user_id` AND o.`photo_id` < p.`photo_id` LIMIT 1) AS previous_photo, 
(SELECT o.`photo_id` FROM photos o WHERE o.`user_id` = p.`user_id` AND o.`photo_id` > p.`photo_id` LIMIT 1) AS next_photo 
FROM photos p 
WHERE p.`user_id` = 1 AND p.`photo_id` = 3 LIMIT 1;

This will place the current photo (photo_id = 3) in photo, place the previous one in previous_photo and the next one in next_photo.

Hope this helps, Test it and tell me...

Upvotes: 1

Lightness Races in Orbit
Lightness Races in Orbit

Reputation: 385098

If you're on photo_id == 3 and you know that it belongs to user_id == 1, then:

the "previous" photo_id for that user is:

SELECT `photo_id`
  FROM `photos`
 WHERE `photo_id` < 3 AND `user_id` = 1
 ORDER BY `photo_id` DESC
 LIMIT 1

and the "next" photo_id for that user is:

SELECT `photo_id`
  FROM `photos`
 WHERE `photo_id` > 3 AND `user_id` = 1
 ORDER BY `photo_id` ASC
 LIMIT 1

For a bonus point, you can make this more efficient with an index:

CREATE INDEX `idx_user_photo`
    ON `photos`
       (`user_id`, `photo_id`)

Upvotes: 1

WhiteFang34
WhiteFang34

Reputation: 72039

You could get the previous photo_id like this:

SELECT photo_id FROM photos
WHERE user_id = ? AND photo_id < ?
ORDER BY photo_id DESC LIMIT 1

And the next like this:

SELECT photo_id FROM photos
WHERE user_id = ? AND photo_id > ?
ORDER BY photo_id ASC LIMIT 1

Be sure that you have a composite index on (user_id, photo_id) for better performance. E.g.:

CREATE INDEX idx_user_photo ON photos (user_id, photo_id)

Upvotes: 1

Related Questions