php - CASE语句未同时运行

我有查询问题。 （我将在文章底部详细介绍）我有一个复选框表。我将复选框发送到POST上的PHP文件中以更新db。我能弄清楚是否有一个以前的checked框在POST之前未被选中的唯一方法是查询表中应检查的内容，以及是否不是通过POST发送的，未被检查。我决定在一个查询中处理所有这些问题。但是由于某种原因，它不会处理未检查的内容。如果我选中了“未事先检查”，则它将保持选中状态（它将在db中将其标记为选中状态）。但是，如果我取消选中了先前选中的一项，它将保持选中状态（我希望它取消选中它）。

这是我用于创建复选框的代码：

echo "<table class='tables' border='1'>";   //start an HTML table

    $fields =array();

    $result = mysqli_query($con, "SELECT u.unit_nickname AS unit, r.id AS reservation,
                                CONCAT(g.fname,' ',g.lname) AS guest, r.arrival, r.departure,
                                r.total_price - COALESCE(p.payment_amt, 0) AS balance,
                                r.checked_in
                                FROM reservations r
                                JOIN units u ON r.unit = u.id
                                JOIN guests g ON r.guest = g.id LEFT JOIN
                                (SELECT reservation, SUM(payment_amt) payment_amt
                                FROM payments
                                GROUP BY reservation) AS p
                                ON r.id = p.reservation
                                WHERE r.checked_out = 0");

    echo '<th></th><th>Unit</th><th>Reservation</th><th>Guest</th><th>Arrival</th>
        <th>Depart</th><th>Balance</th><th>Status</th>';

    while ($row = mysqli_fetch_array($result))
    {
        $fields[] = $row['unit']; $fields[] = $row['reservation']; $fields[] = $row['guest'];
        $fields[] = $row['arrival']; $fields[] = $row['departure']; $fields[] = $row['balance'];
        $fields[] = $row['checked_in'];

        //rowid == id of table (the row corresponds to the row in the table)
        $rowid = $row['reservation'];

        $count = count($fields);

        echo "<tr class='edit_tr' id='".$rowid."'>";
        echo "<td><input type='checkbox' value='".$fields[1]."' name='checkins[]'";
        if ($row['checked_in'] == 1)
            echo "checked='yes'";
        echo "/></td>";

        for ($j = 0; $j < $count; $j++)
        {
            echo "<td><span id='".$fields[1]."'>", ($j+1 == $count ? ($fields[$j] == 0 ? "Not Checked In" : "Checked In") : "".$fields[$j]."");
            echo "</span></td>";
        }

        echo "</tr>";

        //empty array
        $fields = "";
    }

    echo "</table>";    //close the HTML table

这是提交给我的PHP文件的一部分：

if(isset($_POST['checkins']))
{
    //open connection
    $con = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);

    //set POST to array
    $checkins = array();

    //get all checkins that are not checked out
    $query = "SELECT id FROM reservations WHERE checked_in = 1 AND checked_out = 0";
    $result = mysqli_query($con, $query);
    while ($row = mysqli_fetch_assoc($result)) $checkedins[] = $row;

    //create WHERE clause for new checkins
    $where = " WHERE id =";
     foreach ($_POST['checkins'] as $id) {
        $where .= " $id OR id =";
        array_push($checkins, $id);
    }

    //loop through $checkedins[]
    foreach ($checkedins as $id) {
        //if they were checked in before and are now not checked in, add to list
        if (!in_array($id["id"], $checkins))
            $where .= " ".$id['id']." OR id =";
    }

    //remove last 8 characters from WHERE clause
    $where = substr($where, 0, -8);

    echo $query = "UPDATE reservations
                SET checked_in = CASE
                WHEN checked_in = 0 THEN 1
                WHEN checked_in = 1 THEN 0 END
                $where";
    mysqli_query($con, $query);
    mysqli_close($con);
}

我已尝试评论得足够好，以解释发生了什么事。

我唯一遇到的问题是查询

$query = "UPDATE reservations
                SET checked_in = CASE
                WHEN checked_in = 0 THEN 1
                WHEN checked_in = 1 THEN 0 END
                $where";

我确定它与我的CASE语句有关，但是无论我如何修改它，我似乎都无法查明。

应该发生的事情是这样的：我将新checkins的列表放入名为$checkins的数组中，并且还获得了check * ed * ins的列表，这些列表是在POST之前检查过的所有内容。一个名为$checkedins的数组。如果它们不在$checkins列表中，则将其添加到$checkins数组中。因此，$checkins现在包含所有要检查和未检查的行。我的查询应该做的就是将数组中的所有内容都更改为1，即为0；将所有内容中的所有内容都更改为1，即为1。我要求另一半的帮助。

最佳答案

如果checked_in确保为零或一，则可以使用以下小技巧完全避免使用“ CASE”：

SET checked_in = 1 - checked_in
WHERE ...

当1-x为1时，表达式x的值为0；当0为x时，表达式的1。