MYSQL：查询获取上一个和下一个视频ID？ [英] MYSQL: Query to get previous and next video ID?

问题描述

我正在开发一个视频网站（ PHP - MYSQL ），就像youtube一样，我想在其中提供 code>视频和上一个视频。假设我目前在 videoId：234 ，所以下一页和上一个视频链接将指向 videoId：233 和 235 。

我的表结构如下：

  videoId videoName videoURL IsActive 
 -------------------------------- --------------------- 
 1爱情世界爱情世界1 
 2你好世界hellow-world 1 
 3世界新闻世界新闻0 
 4网络网络1 
 5 Google网页google-web 1 
 6互联网1 
  

IsActive a bool（0,1）类型列基本上表示视频在网站上是否可见。如果它的0 - 不可见和1 - 可见

现在有两种类型的情况，我是：

• 当我浏览videoId：4时，我想运行查询，获得下一个和上一个视频ID分别为2和5，3不是因为它被禁用。 / p>




• 其他条件是当我浏览videoId：1时，查询应该只返回一条记录，即2，因为没有上一条记录，

请告诉我如何进行查询？

感谢

解决方案

我认为你可以使用：

  SELECT * 
 FROM videos AS c 
 WHERE（VideoId =（SELECT MAX（VideoId）FROM videos WHERE VideoId< c.VideoId AND IsActive = 1）
或VideoId =（SELECT MIN（VideoId）FROM videos WHERE VideoId> c.VideoId AND IsActive = 1））
  

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

解决方案

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

这篇关于MYSQL：查询获取上一个和下一个视频ID？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！