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.

$query->bindParam(':product_id', $row['product_id']); won't work, as $row it's an array of table rows, ergo array of arrays. If the product_id is the same for all records, then what you need is $query->bindParam(':product_id', $row[0]['product_id']);. Also, there is no need for bindParam, almost never, so you should be ok with using bindValue. — N.B.
– N.B., Commented Feb 15, 2013 at 14:08
$row isn't an array of table rows. $rows (note the s on the end) is the array of table rows. $row is a single row. — Andy Slater
– Andy Slater, Commented Feb 15, 2013 at 14:27
You weren't exactly clear what you were trying to do. Bind the :product_id in the loop. — N.B.
– N.B., Commented Feb 15, 2013 at 14:28
bindValue assesses the value when it's bound. bindParam assessed when the execute() happens. So I could use bindValue if I did it within the loop but bindParam should let me do it before the loop. Note that it works fine with $i. — Andy Slater
– Andy Slater, Commented Feb 15, 2013 at 14:30
@N.B. I know I can bind it in the loop... but I don't understand why I have to do it with :product_if when I can bind :manual_order before the loop. What I need to know is why it works for $i and not for $row['product_id'] — Andy Slater
– Andy Slater, Commented Feb 15, 2013 at 14:35

Fabian Schmengler · Accepted Answer · 2013-02-15 14:49:20Z

2

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.

answered Feb 15, 2013 at 14:49

Fabian Schmengler

24.6k9 gold badges82 silver badges114 bronze badges

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

1 Comment

Andy Slater Over a year ago

Aha! That explains it. I didn't realise that bindParam would actually create the variable (if it wasn't already defined). Now that you've said it, it makes sense that it does, and of course explains WHY my second piece of code won't work.

Arun Killu · Accepted Answer · 2013-02-15 14:17:02Z

-1

You are referring to an undefined array index $row['product_id']
$query->bindParam(':product_id', $row['product_id']); statement fails .
use try catch to catch exception

edited Feb 15, 2013 at 14:17

answered Feb 15, 2013 at 14:11

Arun Killu

14.3k5 gold badges38 silver badges66 bronze badges

3 Comments

Andy Slater Over a year ago

I haven't tried to catch an exception but don't see why bindParam would fail with $row['product_id']. It works with $i and $i isn't defined when the bindParam happens. I thought the whole point with bindParam, as opposed to bindValue, is that the bindParam variable/value is assessed when the execute() is performed... and both $i and $row['product_id'] are defined by the time that happens.

Arun Killu Over a year ago

$row['product_id'] where have tou defined row? thats after bind

Andy Slater Over a year ago

$row is defined by the foreach loop.

Collectives™ on Stack Overflow

PHP PDO bindParam not working with array content

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related