PHP PDO bindParam not working on array content
I have a mechanism that allows the user to control the order in which products appear in a product group. There is a table <<20> ( MySQL ) with columns for group_id
, product_id
and manual_order
. The first two assign products to groups, the third determines the order.
Values manual_order
start at 10 and increase in steps of 10 . So, for example, moving a product to list 3 is just a case of changing the value manual_order
to 25 and then recalculating the values manual_order
. This is done by getting a list of product IDs, sorted by manual_order
, and renumbering them in increments.
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;
}
}
It does not mean:
$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 the $product_id
assigned value (from $row['product_id'])
to the foreach loop, whereas the first piece of code tries to bind $row['product_id']
outside of the loop ... which, as far as I know, should work, but doesn't.
I can only guess why the second part of the code is not working, because I cannot find a definite answer. I would like to get a definite answer because I am new to use PDO
and do not want to crash with this kind of thing as I am converting the rest of my system to use PDO
instead of the old mysql_ function.
source to share
See what your code is doing:
$query->bindParam(':product_id', $row['product_id']);
Here you are implicitly creating an array $row
with one element and passing that element as a reference to bindParam (). This results in the following structure:
$row : Array(
'product_id' => &null
)
Please note that $row
this is not a link in itself! Once you reach the loop:
foreach ($rows as $row)
The array $row
gets overwritten . At this point, you loose the reference in your old $ row-Array, the array no longer exists. A reference cannot automatically jump to another array, just because it has the same key and is usually assigned to the same variable.
In short: what you are trying to do is impossible.
source to share