PDO loop for updating multiple rows

Question

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

You can do the loop somewhat more efficiently, but this PDO::PARAM_STR is probably the constant you intended to use for comment — datasage
– datasage, Commented Jul 26, 2013 at 16:20
you can remove this line $comment = $_POST['comment'][$i]; — cmorrissey
– cmorrissey, Commented Jul 26, 2013 at 16:20
First line of the foreach loop, which uses $comment as its index, you change $comment. Why? What do you need the $i for? — Traveling Tech Guy
– Traveling Tech Guy, Commented Jul 26, 2013 at 16:21

Your Common Sense · Accepted Answer · 2022-12-08 06:02:01Z

Few things:

Set PDO to throw PDOExceptions on errors. It would make debugging a whole lot easier.
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();

        }
    }

Dharman · Accepted Answer · 2021-04-11 14:16:07Z

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

edited Apr 11, 2021 at 14:16

Dharman♦

34k27 gold badges106 silver badges158 bronze badges

answered Jul 26, 2013 at 16:31

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Comments

Tomek · Accepted Answer · 2015-07-02 15:04:13Z

2

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

answered Jul 2, 2015 at 15:04

Tomek

211 bronze badge

1 Comment

vee Over a year ago

This is good answer, it's really fast ...but there is no sample code about PDO. How they use placeholder and how they bind param/value?

Collectives™ on Stack Overflow

PDO loop for updating multiple rows

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related