循环遍历 MySQL 结果

发布于 2024-11-25 15:31:43 字数 1394 浏览 0 评论 0原文

我不确定这到底是如何称呼的,但我会尽力描述我想要实现的目标。

所以,首先有一个变量,叫做$id,它实际上是$_GET['id']。假设用户通过请求进入以下页面:/page.php?id=6。现在我需要做的是提供数据库中接下来 3 页的信息。这是数据库:

  TABLE `pages`

  id   |   page_name
  ______________________
  1    |   AAAAA
  2    |   BBBBB
  3    |   CCCCC
  4    |   DDDDD
  5    |   EEEEE
  6    |   FFFFF
  7    |   GGGGG
  8    |   HHHHH
  9    |   IIIII

因此,在请求 id 6 的页面时,以下脚本返回接下来的 3 个页面 (7,8,9):

 $res = mysql_query("SELECT * FROM `pages` WHERE `id`>'".intval($id)."' ORDER BY `id` DESC LIMIT 3");
 while($arr = mysql_fetch_assoc($res))
 {
       print("Page ID: ".$arr['id']."; Page Name: ".$arr['page_name']."\n");
 }

这是输出:

 Page ID: 7; Page Name: GGGGG
 Page ID: 8; Page Name: HHHHH
 Page ID: 9; Page Name: IIIII

它工作正常,直到$id 大于 6。当它是 (/page.php?id={7/8/9}) 时,输出不会显示 3 页更多,但2页、1页时分别没有输出$id9

所以我的问题是:当没有足够的结果(少于3)显示时,有没有办法返回并从头开始?

  • 访问 /page.php?id=8 时,输出应包含 id 为 912 的页面>。
  • 访问 /page.php?id=9 时,输出应包含 id 为 123 的页面>。
  • 访问 /page.php?id=3 时,输出应包含 id 为 456 的页面>等等。

I'm not sure exactly how this is called but I'll try to describe as good as I can what I want to acheive.

So, first of all, there is a variable, called $id, which is actually $_GET['id']. Assuming the user is entering the following page by requesting: /page.php?id=6. Now what I need to do is to provide the information about the next 3 pages from database. Here is the database:

  TABLE `pages`

  id   |   page_name
  ______________________
  1    |   AAAAA
  2    |   BBBBB
  3    |   CCCCC
  4    |   DDDDD
  5    |   EEEEE
  6    |   FFFFF
  7    |   GGGGG
  8    |   HHHHH
  9    |   IIIII

So, while requesting the page with id 6, the following script returns the next 3 pages (7,8,9):

 $res = mysql_query("SELECT * FROM `pages` WHERE `id`>'".intval($id)."' ORDER BY `id` DESC LIMIT 3");
 while($arr = mysql_fetch_assoc($res))
 {
       print("Page ID: ".$arr['id']."; Page Name: ".$arr['page_name']."\n");
 }

And here is the output:

 Page ID: 7; Page Name: GGGGG
 Page ID: 8; Page Name: HHHHH
 Page ID: 9; Page Name: IIIII

And it works fine until the $id is greater then 6. When it is (/page.php?id={7/8/9}), the output doesn't show 3 pages any more, but 2 pages, 1 page and respectively no output when $id is 9.

So my question is: Is there a way to go back and start from the beginning when there are not enough results (less than 3) to display?

  • When accessing /page.php?id=8, the output should contain pages with id 9, 1 and 2.
  • When accessing /page.php?id=9, the output should contain pages with id 1, 2, 3.
  • When accessing /page.php?id=3, the output should contain pages with id 4, 5, 6 and so on.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

笙痞 2024-12-02 15:31:43
(SELECT *, 0 AS custom_order FROM `pages` WHERE `id`>'".intval($id)."' ORDER BY `id` ASC LIMIT 3)
UNION ALL
(SELECT *, 1 AS custom_order FROM `pages` ORDER BY `id` ASC LIMIT 3)
ORDER BY custom_order, id ASC
LIMIT 3

这样你总能得到 3 页。如果下一页不够,您将从头开始最多 3 页。

(SELECT *, 0 AS custom_order FROM `pages` WHERE `id`>'".intval($id)."' ORDER BY `id` ASC LIMIT 3)
UNION ALL
(SELECT *, 1 AS custom_order FROM `pages` ORDER BY `id` ASC LIMIT 3)
ORDER BY custom_order, id ASC
LIMIT 3

This way you always get 3 pages. If not enough next pages, you will get up to 3 from the beginning.

半透明的墙 2024-12-02 15:31:43

您可以将查询修改为如下所示:

 select * from 
     (select *, id-$inval($id) as order_by 
         from pages were id > $inval($id) order by id asc limit 3
      union
      select *, id as order_by 
         from pages order by id asc limit 3 ) as pages
 order by order_by asc

You could modify the query to be something like:

 select * from 
     (select *, id-$inval($id) as order_by 
         from pages were id > $inval($id) order by id asc limit 3
      union
      select *, id as order_by 
         from pages order by id asc limit 3 ) as pages
 order by order_by asc
蓝天白云 2024-12-02 15:31:43

我会用这种方式解决(一个可能的问题是结果集最多可以包含 6 条记录而不是 3 条):

$res = mysql_query("(SELECT * FROM `pages` WHERE `id`>'".intval($id)."' ORDER BY `id` ASC LIMIT 3) UNION DISTINCT (SELECT * FROM `pages` WHERE id>0 ORDER BY id ASC LIMIT 3)");
$counter = 0;
 while($arr = mysql_fetch_assoc($res) && $counter<3)
 {
       $counter++;
       print("Page ID: ".$arr['id']."; Page Name: ".$arr['page_name']."\n");
 }

I would solve this way (one possible issue is that the resultset could contain at most 6 records instead of 3):

$res = mysql_query("(SELECT * FROM `pages` WHERE `id`>'".intval($id)."' ORDER BY `id` ASC LIMIT 3) UNION DISTINCT (SELECT * FROM `pages` WHERE id>0 ORDER BY id ASC LIMIT 3)");
$counter = 0;
 while($arr = mysql_fetch_assoc($res) && $counter<3)
 {
       $counter++;
       print("Page ID: ".$arr['id']."; Page Name: ".$arr['page_name']."\n");
 }
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文