选择器上的 PHP 逻辑问题

Question

我有一个事件预订系统，它显示了测试系统的可用时段，我真的被某些逻辑困住了。

event_information (A) 存储有关事件的详细信息

event_machine_time (B) 存储测试笔记本电脑事件时间，因为机器只允许在每天的特定时间使用

*********************************************
* id * machine_name * start_time * end_time *
*********************************************
* 1  * LPN832       * 09:30:00   * 11:00:00 *
* 2  * LPN832       * 11:30:00   * 13:00:00 *
* 3  * LPN832       * 13:30:00   * 15:00:00 *
* 4  * MHY348       * 09:30:00   * 11:00:00 *
* 5  * MHY348       * 11:30:00   * 13:00:00 *
* 6  * MHY348       * 13:30:00   * 15:00:00 *
*********************************************

event_booking (C) 存储预订详细信息，包括该人是否已通过c_confirmed 拒绝该事件以及该人是否已通过live

***************************************************************************
* id * information_id * candidate_id * machine_name_id * live * confirmed *
* 3  * 2666           * 356          * 1               * 1    * 2         *
* 4  * 2666           * 456          * 1               * 1    * 0         *
***************************************************************************

So my statuses for confirmed are:
0 - Not confirmed
1 - Confirmed
2 - Declined

My statuses for live are:
1 - Live
0 - Deleted

这是我的代码:

$Machine_Query = "SELECT *,
        B.id AS m_id,
        C.id AS c_id,
        C.confirmed AS c_confirmed,
        C.live AS c_live
        FROM event_information A
        INNER JOIN event_machine_time B on (1=1)
        LEFT JOIN event_booking C on (B.id = C.machine_time_id and A.id = C.information_id )
        WHERE A.id = '$Event_Data_ID'
        ORDER BY B.machine_name ASC, B.start_time ASC";
        $Machine = $dbconn->query($Machine_Query);

对于我的下拉选择器，我使用以下内容:

<?php foreach ($Machine->fetchAll() as $Machine_Row) { 
                //if($Machine_Row["confirmed"] == 2) { } else {
                ?>
              <option value="<?php echo $Machine_Row["m_id"]; ?>" <?php if($Machine_Row["c_id"] != NULL && $Machine_Row["c_confirmed"] != "2" && $Machine_Row["c_live"] != "0") { echo "disabled"; } ?>><?php echo $Machine_Row["machine_name"]." - ".date("g:ia", strtotime($Machine_Row["start_time"])); ?><?php if($Machine_Row["c_id"] != NULL && $Machine_Row["c_confirmed"] != "2" && $Machine_Row["c_live"] != "0") { echo " - Booked"; } ?></option>
        <?php } //} ?> 

这是使用上面示例的输出，其中一个用户正在等待确认(因此它显示已预订)而另一个已拒绝 - 看看它如何显示一个额外的插槽，如果我更改实时状态同样适用:

现在您可以看到我已经添加了一个我评论过的脏修复程序，它确实解决了问题，但它确实应该存在于 SQL 中，因为如果不尝试在 SQL 中进行隔离，我将无法正确构建有效性检查器.

这让我发疯，这很可能是我看待它的角度，所以非常感谢新鲜的眼睛。

Answer 1

您可以选择一个 bool 值来了解何时禁用该选项(此处命名为 booked):

(c.id IS NOT NULL AND C.confirmed != 2 AND C.live != 0) AS booked

我稍微编辑了您的 PDO 查询:

$Machine = $dbconn->prepare(
    'SELECT
        *,
        B.id AS m_id,
        C.id AS c_id,
        C.confirmed AS c_confirmed,
        C.live AS c_live,
        (c.id IS NOT NULL AND C.confirmed != 2 AND C.live != 0) AS booked   
    FROM event_information A
        INNER JOIN event_machine_time B
            ON (1 = 1)
        LEFT JOIN event_booking C
            ON (B.id = C.machine_time_id AND A.id = C.information_id)
    WHERE A.id = :event_data_id
    ORDER BY B.machine_name ASC, B.start_time ASC'
);

$dbconn->execute([ 'event_data_id' => $Event_Data_ID ]);

这在 HTML 中的输出会更短(使用三元运算符在一行中看起来会更好):

<?php
foreach ($Machine->fetchAll() as $Machine_Row) { 
    ?>
    <option value="<?php echo $Machine_Row["m_id"]; ?>" <?php echo $Machine_Row['booked'] ? 'disabled' : ''; ?>>
        <?php echo $Machine_Row["machine_name"] . " - " . date("g:ia", strtotime($Machine_Row["start_time"])) . ($Machine_Row['booked'] ? ' - Booked' : ''); ?>
    </option>
    <?php
}
?>