php - MySQL 只返回符合条件的结果

Question

我试图只选择数据库中符合确切条件的项目，在这种情况下，我需要该项目既处于事件状态又处于点击状态。然后，这会填充轮播，并且箭头会按 ID 导航到该项目。

/* mySql  code */
if ($select = $db -> prepare("SELECT id, name, type, active, abv, ibu, og, hops, color, description, growlers, tap, cans, bottles, image FROM beers WHERE tap = 'Yes' AND (active = 'Yes') AND (id = ?)"))
{
    $select -> bind_param('s', $_GET['id']);
    $select -> execute();
    $select -> bind_result($id, $name, $type, $active, $abv, $ibu, $og, $hops, $color, $description, $growlers, $tap, $cans, $bottles, $image);
    $select -> fetch();
    $select -> close();
}

/* carousel  code */
<a id="prev" class="controls" href="./?page=tapbeer&id=<?php echo $_GET['id'] - 1; ?>">
 <i class="fal fa-angle-left"></i>
</a>
<a id="next" class="controls" href="./?page=tapbeer&id=<?php echo $_GET['id'] + 1; ?>">
  <i class="fal fa-angle-right"></i>
</a>

我只想要符合条件的下一个 ID，而不是序列中的下一个 ID。

例如，我们有 5 个项目，三个处于事件状态，两个处于点击状态

+-------+-------+--------+------+
| id    | name  | active | tap  |
+-------+-------+--------+------+
| 1     | beer1 | Y      | Y    |
+-------+-------+--------+------+
| 2     | beer2 | Y      | N    |
+-------+-------+--------+------+
| 3     | beer3 | N      | N    |
+-------+-------+--------+------+
| 4     | beer4 | Y      | Y    |
+-------+-------+--------+------+
| 5     | beer5 | M      | N    |
+-------+-------+--------+------+

我只想在轮播中显示 beer1 和 beer4。

我一直遇到的问题是所有 ID 都滚动浏览，并在轮播中显示为空，因为它们不符合条件，因为我们查看 ID 并添加或减去 1 以获得序列中的下一个 ID。

Answer 1

获取下一个和上一个值的查询是:

SELECT id
FROM beers 
WHERE tap = 'Yes' AND active = 'Yes' AND id > ?
ORDER BY id ASC
LIMIT 1

SELECT id
FROM beers 
WHERE tap = 'Yes' AND active = 'Yes' AND id < ?
ORDER BY id DESC
LIMIT 1

? 应替换为当前 ID。

执行这些查询并获取下一个/上一个链接所需的 id。

基本上是:

if ($select = $db -> prepare("SELECT id, name, type, active, abv, ibu, og, hops, color, description, growlers, tap, cans, bottles, image FROM beers WHERE tap = 'Yes' AND (active = 'Yes') AND (id = ?)"))
{
    $select -> bind_param('s', $_GET['id']);
    $select -> execute();
    $select -> bind_result($id, $name, $type, $active, $abv, $ibu, $og, $hops, $color, $description, $growlers, $tap, $cans, $bottles, $image);
    $select -> fetch();
    $select -> close();


    $nextId = $db->prepare('query_1');
    $nextId -> bind_param('s', $_GET['id']);
    $nextId -> execute();
    $nextId -> bind_result($next_id);
    $nextId -> fetch();
    $nextId -> close();

    $prevId = $db->prepare('query_2');
    $prevId -> bind_param('s', $_GET['id']);
    $prevId -> execute();
    $prevId -> bind_result($prev_id);
    $prevId -> fetch();
    $prevId -> close();
}

/* carousel  code */
<a id="prev" class="controls" href="./?page=tapbeer&id=<?php echo $prev_id; ?>">
 <i class="fal fa-angle-left"></i>
</a>
<a id="next" class="controls" href="./?page=tapbeer&id=<?php echo $next_id; ?>">
  <i class="fal fa-angle-right"></i>
</a>