php - 在站点中运行时出现 SQL 错误

Question

我有一个生成和运行 mysql 更新查询的函数。当我在 phpMyAdmin 中运行生成的代码时没有遇到任何问题，但是当我尝试从我的站点运行它时出现错误。奇怪的是，我对其他几个更新查询使用了相同的格式，而且它们工作正常。

MySQL 查询:

UPDATE `capc`.`bio_positions` SET `Pos_Name` = 'IT Specialist' WHERE `bio_positions`.`Pos_ID` = 63;UPDATE `capc`.`bio_positions` SET `Pos_Company` = 'CSG' WHERE `bio_positions`.`Pos_ID` = 63;

错误:

Could not get data: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE `capc`.`bio_positions` SET `Pos_Company` = 'CSG' WHERE `bio_position' at line 1

用于生成 MySQL 查询的代码

function update($rank, $name, $company) {
    if (!empty($rank) && $rank !== $this->Pos_Rank) {
        $sql = "UPDATE `capc`.`bio_positions` SET `Pos_Rank` = " . $rank . " WHERE `bio_positions`.`Pos_ID` = " . $this->Pos_ID . ";";
    }
    if (!empty($name) && $name !== $this->Pos_Name) {
        $sql .= "UPDATE `capc`.`bio_positions` SET `Pos_Name` = '" . $name . "' WHERE `bio_positions`.`Pos_ID` = " . $this->Pos_ID . ";";
    }
    if (!empty($company) && $company !== $this->Pos_Company) {
        $sql .= "UPDATE `capc`.`bio_positions` SET `Pos_Company` = '" . $company . "' WHERE `bio_positions`.`Pos_ID` = " . $this->Pos_ID . ";";
    }
    echo "<br>" . $sql . "<br>";
    if (!empty($sql)) {
        $capc = new CAPC;
        $capc->query($sql);
        Bio_Position($this->Pos_ID);
    }
}

根据有效的答案更新更新函数

function update($rank, $name, $company) {
    $capc = new CAPC;
    $sql = "UPDATE `capc`.`bio_positions` SET ";
    $go = 0;
    if (!empty($rank) && $rank !== $this->Pos_Rank) {
        $sql .= " `Pos_Rank` = " . $rank;
        $go++;
    }
    if (!empty($name) && $name !== $this->Pos_Name) {
        if($go > 0){
            $comma = ",";
        }
        $sql .= $comma . " `Pos_Name` = '" . $name . "'";
        $go++;
    }
    if (!empty($company) && $company !== $this->Pos_Company) {
        if($go > 0){
            $comma = ", ";
        }
        $sql .= $comma . " `Pos_Company` = '" . $company . "'";
        $go++;
    }
    $sql .= " WHERE `bio_positions`.`Pos_ID` = " . $this->Pos_ID . ";";

    if (!empty($sql) && $go > 0) {
        //echo $sql . "<br>";
        $capc = new CAPC;
        $capc->query($sql);
    }
}

Answer 1

When I run the generated code in phpMyAdmin I have no troubles

UPDATE capc.bio_positions SET Pos_Name = 'IT Specialist' WHERE bio_positions.Pos_ID = 63;

UPDATE capc.bio_positions SET Pos_Company = 'CSG' WHERE bio_positions.Pos_ID = 63;

虽然 phpMyAdmin 似乎一次执行多个查询(它实际上是在后端为您拆分它们)，但在您的代码中，您需要单独执行它们或重写 SQL 以在单个列中更新多个列语句，即 UPDATE capc.bio_positions SET Pos_Name = 'IT Specialist', Pos_Company = 'CSG' WHERE bio_positions.Pos_ID = 63。

如果您使用的是 mysqli，您还可以查看 mysqli::multi_query .