CODEWITHSUNDEEP
phpmysqlpdo
user2522177
user2522177

Reputation: 47

PDO loop for updating multiple rows

I have a form that retrieves multiple rows of data and each item has a textarea for the user to comment on a particular item. The number of items returned is variable, and they don't have to leave comments in any/all of the boxes.

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

The echo statement populates the text area with whatever is currently stored in the database as the user can modify what someone else entered.

When this gets passed to the form handling page, it returns this..

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

so it appears to be passing the array correctly. I am using this code to update the database

$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++;
    }
}

However, this does not seem to update at all, where am i going wrong?

Many Thanks

Upvotes: 0

Views: 14623

Answers (3)

Madara&#39;s Ghost
Madara&#39;s Ghost

Reputation: 174957

Few things:

  1. Set PDO to throw PDOExceptions on errors. It would make debugging a whole lot easier.
  2. The point of prepared statements, is that you can call it many times using different variables, that way, you only need to prepare it once, and call it multiple times. You gain a nice performance boost out of it too.

Code:

    $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();

        }
    }

Upvotes: 11

Your Common Sense
Your Common Sense

Reputation: 157860

<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]);
    }
}

Upvotes: 2

Tomek
Tomek

Reputation: 21

It will be 2-3 time faster if you use CASE, example query:

UPDATE website
    SET http_code = CASE id_website
        WHEN 1 THEN 200
        WHEN 2 THEN 201
        WHEN 3 THEN 202
    END,
    link_exists = CASE id_website
        WHEN 1 THEN 1
        WHEN 2 THEN 2
        WHEN 3 THEN 3
    END
WHERE id_website IN (1,2,3)

I tested it on 1000 rows.

Prepared: 0.328 s

Case: 0.109 s

Upvotes: 2

Related Questions