php - 用于更新数据库条目的多个 if 语句

Question

具有以下代码，在单击按钮时转到名为“colleges”的数据库，其中列名为“college1”、“college2”等(到“college10”)以及“sports1”、“sports2”等(到'sports10')

if ($row['college4'] == null) {
     $sql = "UPDATE colleges SET college3 = NULL, sports3 = NULL";
}
if ($row['college3'] == null) {
     $sql = "UPDATE colleges SET college2 = NULL, sports2 = NULL";
}
if ($row['college2'] == null) {
     $sql = "UPDATE colleges SET college1 = NULL, sports1 = NULL";
}

本质上，如果数据库中的特定列等于 NULL，则将前面的列设置为 NULL。然而，先前的编码仅适用于最近的 if 语句(在本例中为 college2)。一旦执行，其余的 if 语句将被忽略(即学院 4 和学院 3。

如果需要更多解释，请告诉我。我想我只是在寻找一种如何让 sql 查询以级联方式识别的方法。谢谢。

完整的 PHP:

<?php
            session_start();

            define("DB_HOST", "");
            define("DB_USER", "");
            define("DB_PASS", "");
            define("DB_NAME", "");

            // Create connection
            $conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

            if ($conn->connect_error) {
                die("Connection failed: " . $conn->connect_error);
            }

                    if ($row['college4'] == null) {
                        $sql = "UPDATE colleges SET college3 = NULL, sports3 = NULL";
                    }
                    if ($row['college3'] == null) {
                        $sql = "UPDATE colleges SET college2 = NULL, sports2 = NULL";
                    }
                    if ($row['college2'] == null) {
                        $sql = "UPDATE colleges SET college1 = NULL, sports1 = NULL";
                    }

            if ($conn->query($sql) === TRUE) {
                echo "Record deleted successfully";
            } else {
                echo "Error deleting record: " . $conn->error;
            }

            mysqli_close($conn);
        ?>

Answer 1

您的所有更新都缺少 WHERE 子句，并使用 AND 或 OR 作为分隔符，而不是逗号 posted in comments :

UPDATE colleges SET college3 = NULL, sports3 = NULL 
WHERE college3 != NULL, sports3 != NULL

应该读作:

UPDATE colleges SET college3 = NULL, sports3 = NULL 
WHERE college3 != NULL AND sports3 != NULL

或使用 OR 逻辑运算符，具体取决于条件。

另外，正如我在评论中提到的；您正在更新整个数据库而没有 WHERE 子句。

查阅手册:

• https://dev.mysql.com/doc/refman/5.7/en/update.html
• https://dev.mysql.com/doc/refman/5.7/en/where-optimization.html