MYSQL: request for previous and next video IDs?

Question

I am developing a video site ( PHP - MYSQL), like youtube, in which I want to provide video Nextand Previousvideo functionality . Suppose I'm on now videoId: 234, so the Next and Previous links of the video will point to videoId: 233and, 235respectively.

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 columnbasically says that the video can be viewed on the website or not. If its 0 is inconspicuous and 1 is visible

Now there are two types of situations that I, though:

• When I look at videoId: 4, I want to run a query to get the next and previous video IDs, respectively 2 and 5, 3, not because it is disabled.

• videoId: 1, , 2, , .

, , ?

Answer 1

, - :

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))

Answer 2

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

Answer 3

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

> < . count($all) 0, / .

Answer 4

Try it. Completely untested, but this will give you a normal row with two additional columns "nextID" and "prevID". If one of them is NULL, then it will not be.

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 problems / errors, let me know and I will check it correctly.

Answer 5

best query string:

SELECT
        (SELECT MAX(`id`) FROM `table` WHERE `id` < `t`.`id`) as `_prev_id`,
        (SELECT MIN(`id`) FROM `table` WHERE `id` > `t`.`id`) as `_next_id`

  FROM `table` `t`

 WHERE `t`.`id` = '1'

 LIMIT 1