PHP PDO bindParam not working with array content

Question

I have a mechanism that's designed to allow a user to control the order in which products appear in a product group. There's a "grouplines" table (MySQL) with columns for group_id, product_id, and manual_order. The first two assign products to groups, the third determines the order.

The manual_order values start with 10 and go up in increments of 10. So, for example, moving a product to be 3rd in the list, is simply a case of changing it's manual_order value to 25, and then re-calculating the manual_order values. This is done by obtaining a list of product ids, ordered by manual_order, and renumbering them in increment.

This piece of code works:

$products = array();
$dbh = s3_pdo::get_connection();
$query = $dbh->prepare('SELECT product_id FROM grouplines WHERE group_id=:group_id ORDER BY manual_order');
$query->bindValue(':group_id', $group_id);
$query->execute();
$rows = $query->fetchAll();
$query->closeCursor();
if ($rows) {
    $query = $dbh->prepare('UPDATE grouplines SET manual_order=:manual_order WHERE group_id=:group_id AND product_id=:product_id');
    $query->bindValue(':group_id', $group_id);
    //$query->bindParam(':product_id', $row['product_id']);
    $query->bindParam(':product_id', $product_id);
    $query->bindParam(':manual_order', $i);
    $i = 10;
    foreach ($rows as $row) {
        $product_id = $row['product_id'];
        $query->execute();
        $i += 10;
    }
}

This does not:

$products = array();
$dbh = s3_pdo::get_connection();
$query = $dbh->prepare('SELECT product_id FROM grouplines WHERE group_id=:group_id ORDER BY manual_order');
$query->bindValue(':group_id', $group_id);
$query->execute();
$rows = $query->fetchAll();
$query->closeCursor();
if ($rows) {
    $query = $dbh->prepare('UPDATE grouplines SET manual_order=:manual_order WHERE group_id=:group_id AND product_id=:product_id');
    $query->bindValue(':group_id', $group_id);
    $query->bindParam(':product_id', $row['product_id']);
    //$query->bindParam(':product_id', $product_id);
    $query->bindParam(':manual_order', $i);
    $i = 10;
    foreach ($rows as $row) {
        //$product_id = $row['product_id'];
        $query->execute();
        $i += 10;
    }
}

As you can see, the difference is that the second piece of code binds $product_id which is given a value (from $row['product_id']) with the foreach loop, whereas the first piece of code tries to bind $row['product_id'] outside of the loop... which as far as I'm aware ought to work, but doesn't.

I can only guess at why the second piece of code doesn't work, because I can't find a definite answer. I'd like a definite answer because I'm new to using the PDO and don't want to fall foul of this kind of thing as I convert the rest of my system to using the PDO instead of the old mysql_ functions.

Answer 1

Let's see what your code does:

$query->bindParam(':product_id', $row['product_id']);

Here you are implicitly creating an array $row with one element and pass this element as reference to bindParam(). This results in a structure as follows:

$row : Array(
           'product_id' => &null
       )

Note that $row itself is not a reference! As soon as you reach the loop:

foreach ($rows as $row)

the Array $row gets overwritten. At this point you are losing the reference in your old $row-Array, the array does not exist anymore. A reference cannot automagically hop over into a different array, just because it has the same key and happens to be assigned to the same variable.

Long story short: what you are trying to do is not possible.