MYSQL: Query to get previous and next video ID?

Question

I am developing a video website (PHP - MYSQL), just like youtube, in which I want to provide the functionality of Next video and Previous video. Let's say I am currently on videoId: 234 so Next and Previous video links will point to videoId: 233 and 235 respecively.

My table structure is as follows:

videoId      videoName    videoURL      IsActive
-----------------------------------------------------
1            Love world    love-world        1
2            Hello world   hellow-world      1
3            World news    world-news        0
4            The web       the-web           1
5            Google web    google-web        1
6            Internet      internet          1

IsActive a bool (0,1) type column is basically tells that video is viewable on website or not. If its 0 - notviewable and 1 - viewable

Now there are two type of situations which I though are:

• When I am browsing through videoId: 4 then I want to run a query to get the the next and previous video id means 2 and 5 respectively, 3 is not because it is disabled.

• Other condition would be When I am browsing through videoId: 1 then query should return only one record which is 2, because there is no previous record, same in the case of last record.

Please tell me how to make query for this?

Thanks

Answer 1

I think you could use something like:

SELECT * 
  FROM videos AS c 
 WHERE (VideoId = (SELECT MAX(VideoId) FROM videos WHERE VideoId < c.VideoId AND IsActive = 1)
    OR  VideoId = (SELECT MIN(VideoId) FROM videos WHERE VideoId > c.VideoId AND IsActive = 1))