php - 使用 SQL 和 PHP 编辑实体中的多条记录

Question

我是 PHP 新手，似乎正在遵循一个半生不熟的教程。这真是令人沮丧，现在我迫切希望解决这个问题。

我正在尝试通过一次表单提交来更新多个实体中的多个记录。我的 PHP 函数如下。

function updateFixture() {

  global $server, $db, $dbUser, $dbKey, $message;

  try {
    $conn = new PDO("mysql:host=" . $server . ";dbname=" . $db, $dbUser, $dbKey);
    $conn -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = $conn -> prepare("UPDATE Date SET date=:date WHERE dateID=:dateID");

    $sql -> bindValue(":dateID", $_POST["dateID"]);
    $sql -> bindValue(":date", $_POST["date"]);

    $sql = $conn -> prepare("UPDATE Score SET homeScore=:homeScore1, awayScore=:awayScore1 WHERE scoredID=:scores1");

    $sql -> bindValue(":scores1", $_POST["scores1"]);
    $sql -> bindValue(":homeScore1", $_POST["homeScore1"]);
    $sql -> bindValue(":awayScore1", $_POST["awayScore1"]);

    $sql = $conn -> prepare("UPDATE Score SET homeScore=:homeScore2, awayScore=:awayScore2 WHERE scoredID=:scores2");

    $sql -> bindValue(":scores2", $_POST["scores2"]);
    $sql -> bindValue(":homeScore2", $_POST["homeScore2"]);
    $sql -> bindValue(":awayScore2", $_POST["awayScore2"]);

    $sql = $conn -> prepare("UPDATE Score SET homeScore=:homeScore3, awayScore=:awayScore3 WHERE scoredID=:scores3");

    $sql -> bindValue(":scores3", $_POST["scores3"]);
    $sql -> bindValue(":homeScore3", $_POST["homeScore3"]);
    $sql -> bindValue(":awayScore3", $_POST["awayScore3"]);

    $sql = $conn -> prepare("UPDATE Score SET homeScore=:homeScore4, awayScore=:awayScore4 WHERE scoredID=:scores4");

    $sql -> bindValue(":scores4", $_POST["scores4"]);
    $sql -> bindValue(":homeScore4", $_POST["homeScore4"]);
    $sql -> bindValue(":awayScore4", $_POST["awayScore4"]);

    $result = $sql -> execute();

    if ($result) {
      $message = "Customer record was updated";
    } else {
      $message = "The Customer record was not updated";
    }
  }

  catch(PDOException $e) {
    echo "<div class='notification container'><p>An error occured: " . $e -> getMessage() . "</p></div>";
  }

  $conn = null;
}

if (isset($_POST["updateFixture"])) {
  updateFixture($_POST["dateID"]);
}

这似乎与对 Score 实体发出的一个 prepare 语句配合得很好。当我添加额外的三个 prepare 语句时，该函数就会失败。

提前谢谢您。

Answer 1

您需要为每个 prepare() 调用 execute()，您只需在最后调用它一次。

所以...

$sql = $conn -> prepare("UPDATE Date SET date=:date WHERE dateID=:dateID");

$sql -> bindValue(":dateID", $_POST["dateID"]);
$sql -> bindValue(":date", $_POST["date"]);

$result = $sql -> execute();

$sql = $conn -> prepare("UPDATE Score SET homeScore=:homeScore1, awayScore=:awayScore1 WHERE scoredID=:scores1");

等等