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

This will get the next one

$stmt = $PDO->prepare("SELECT * FROM vedios WHERE vedio > :id AND IsActive = 1")
$stmt->execute(array("id" => $id));
$all = $stmt->fetchAll(PDO::FETCH_ASSOC);

And change > to < for previous. If count($all) return 0, then you don't have a next/previous video.

Answer 2

I think you could use something like:

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

Answer 3

I'd use

 SELECT * FROM videos WHERE videoId > :id AND IsActive = 1 LIMIT 1
 UNION
 SELECT * FROM videos WHERE videoId < :id AND IsActive = 1 LIMIT 1

Answer 4

Try this. Completely untested, but this will give you a normal row with two additional columns 'nextID' and 'prevID'. If one of them is null, then there isn't one.

SELECT v.*, n.id AS nextID, p.id AS prevID
  FROM vedios v
       LEFT JOIN (SELECT vn.id FROM vedios vn WHERE vn.id > v.id AND isActive = 1 ORDER BY id ASC LIMIT 1) n
       LEFT JOIN (SELECT vp.id FROM vedios vp WHERE vp.id < v.id AND isActive = 1 ORDER BY id DESC LIMIT 1) p

If you have any issues / errors with it, let me know and I'll test it properly.