PHP MySQL/PDO Update Issue

Question

What I'm trying to do is a drag/drop order setup. I have the jQuery setup to send the serialized job to my php script, but for some reason it is not updating. I have an echo statement that is showing me that the order of objects is correct:

    ID = 2 Counter = 1
    ID = 1 Counter = 2

This also means the function is being called properly, but it does not seem to be actually updating the table, the rest of my inserts/updates work fine, so it isn't the connection.

The function:

public function set_member_order($OrderArray) {
    $query = $this->db->prepare(
        "UPDATE
            `member`
        SET
            `sort` = :sort
        WHERE
            `member_id` = :member_id");
    $sort = 0;
    $member_id = 0;
    $query->bindValue(':sort', $sort);
    $query->bindValue(':member_id', $member_id);
    $counter = 1;

    foreach ($OrderArray as $member_id) {
        $sort = $counter;
        echo "ID = ".$member_id." Counter = ".$sort. "<br />";
        $query->execute();
        $counter ++;
    }
}

I must be missing something, as this should be fairly simple, but I can't pin it down. When I run it and check the data it says the sort order is still 1,1 and 2,2 (the default). I'm also sure the IDs are correct as I pulled them from the database to get them in the first place.

$rows = $query->rowCount();

Is also showing 0 rows effected on each row, when it should be updating, not sure why it's not hititng the database, did I bind them incorrectly? Casting them as int, and using PDO::PARAM_INT did not help either. The only two member_ids in this table are 1 and 2. The query is also valid if I run it manually and input values by hand.

EDIT: I tried to change the member_id initialization to

$member_id = 1;

and it set the member_id of 1 to sort 0, which really makes no sense to me.

check the return value of the $query->execute() call. Maybe you're creating a unique key conflict when you (temporarily) have 1,1 and 2,1 in the table. — Marc B
– Marc B, Commented Feb 19, 2014 at 21:41

jeroen · Accepted Answer · 2014-02-19 21:50:34Z

2

You are using the wrong method to bind. You need to bind the parameter and not the value (both of which are 0 at the time of binding).

Change:

$query->bindValue(':sort', $sort);
$query->bindValue(':member_id', $member_id);

to:

$query->bindParam(':sort', $sort);
$query->bindParam(':member_id', $member_id);

You could use bindValue() but then you would have to bind in the loop, after you set the variables.

answered Feb 19, 2014 at 21:50

jeroen

91.8k22 gold badges118 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DarkSnake Over a year ago

Ah, yeah, that did it. I feel dumb now. Most of my queries thus far have just been a single instance (no loops required) so bindValue had been working fine for that. It's working fine now.

Collectives™ on Stack Overflow

PHP MySQL/PDO Update Issue

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related