使用PDO循環更新多行數據

Question

我有一個可檢索多行資料的表單，每個項目都有一個文字區域，供使用者對特定項目發表評論。返回的項目數量是可變的，並且他們不必在任何/所有框中留下評論。

<textarea name="comment[]" cols="25" rows="2"><?php echo $f2; ?></textarea>
    <input name="tableid[]" type="hidden" value="<?php echo $f1; ?>">

echo 語句用目前儲存在資料庫中的任何內容填滿文字區域，因為使用者可以修改其他人輸入的內容。

當它被傳遞到表單處理頁面時，它會傳回這個..

Submit: Submit
    comment: Test Comment 1,Test Comment 2
    tableid: 590,591

所以它似乎正確地傳遞了陣列。我正在使用此程式碼來更新資料庫

$conn = new PDO("mysql:host=xxxx;dbname=xxxxx",$username,$password);

$i = 0;
if(isset($_POST['submit'])) {
    foreach($_POST['comment'] as $comment) {
                        $comment = $_POST['comment'][$i];

            $id = $_POST['tableid'][$i];
            $stmt = $conn->prepare("UPDATE reservations SET comment=:comment WHERE     tableid=:id");

            $stmt->bindValue(':comment', $comment, PDO::PARAM_INT);
            $stmt->bindValue(':id', $id, PDO::PARAM_INT);

            $stmt->execute();

            $i++;
    }
}

但是，這似乎根本沒有更新，我哪裡出錯了？

非常感謝

Answer 1

幾件事：

1. 將 PDO 設定為在出現錯誤時拋出 PDOException。這將使調試變得更加容易。
2. 準備好的語句的要點是，您可以使用不同的變數多次呼叫它，這樣，您只需要準備一次，然後多次呼叫它。您也可以從中獲得不錯的效能提升。

程式碼：

$conn = new PDO("mysql:host=xxxx;dbname=xxxxx", $username, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, //Set PDO to fire PDOExceptions on errors.
        PDO::ATTR_EMULATE_PREPARES => false //Disable emulated prepares. Solves some minor edge cases.
    ]);

//No need for incrementer. The index of the comment should be enough.
    if (isset($_POST['submit'])) {
        //Note the prepare on the outside.
        $stmt = $conn->prepare("UPDATE `reservations` SET `comment` = :comment WHERE `tableid` = :id");
        //As well as the binding. By using bindParam, and supplying a variable, we're passing it by reference.
        //So whenever it changes, we don't need to bind again.
        $stmt->bindParam(":comment", $comment, PDO::PARAM_STR);
        $stmt->bindParam(":id", $id, PDO::PARAM_INT);

        foreach ($_POST['comment'] as $index => $comment) {

            //All that's left is to set the ID, see how we're reusing the $index of the comment input?

            $id = $_POST['tableid'][$index];

            $stmt->execute();

        }
    }

Answer 2

<textarea name="comment[<?=$f1?>]" cols="25" rows="2"><?=$f2?></textarea>

<?php
$dsn = "mysql:host=xxxx;dbname=xxxxx";
$opt = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);
$conn = new PDO($dsn, $username, $password, $opt);

$sql  = "UPDATE reservations SET comment=? WHERE tableid= ?";
$stmt = $conn->prepare($sql);

foreach ($_POST["comment"] as $id => $comment) {
    if ($comment) {
        $stmt->execute([$comment, $id]);
    }
}