current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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.

share|improve this question
    
$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. Feb 15 '13 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 Feb 15 '13 at 14:27
    
You weren't exactly clear what you were trying to do. Bind the :product_id in the loop. –  N.B. Feb 15 '13 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 Feb 15 '13 at 14:30
    
PHP doesn't work that way, the sole difference between bindParam and bindValue is that bindParam takes a variable reference so it can modify the variable's value once execute occurs. That's because you can pass variables to MySQL's stored procedures which can alter the value of input parameters. So, tl;dr: bindValue(':product_id', $row['product_id'], PDO::PARAM_INT) in the loop. –  N.B. Feb 15 '13 at 14:33

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
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. –  Andy Slater Feb 15 '13 at 17:29
up vote 0 down vote
  1. You are referring to an undefined array index $row['product_id']
  2. $query->bindParam(':product_id', $row['product_id']); statement fails .
  3. use try catch to catch exception
share|improve this answer
    
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. –  Andy Slater Feb 15 '13 at 14:25
    
$row['product_id'] where have tou defined row? thats after bind –  Arun Killu Feb 15 '13 at 14:27
    
$row is defined by the foreach loop. –  Andy Slater Feb 15 '13 at 14:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.