Looping Confusion With Mysqli Prepared Statements

[nodirtyrockstar]

Thank you for jumping in. I think Barand was trying to get at something similar to what you were saying previously, but I did not understand how.

 

First, I took your advice about modifying the query. Since I already have the search keys in the post array, I don't have to bind my parameters at call time. I thus edited the query string and tested it, and I know it works with the new changes. Now I should have one set of results for all the products a user adds to their cart.

 

As for processing the results, this is where I still need help.

 

If I do this:

 

 

if (!$stmt = $mysqli->prepare($query)) {
   echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$stmt->execute()) {
   echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}
if (!call_user_func_array(array($stmt, 'bind_result'), $columns)) {
   echo "Binding results failed: (" . $stmt->errno . ") " . $stmt->error;
}
while ($stmt->fetch()) {
   $prodDetail[$id] = $columns;
   if($_POST['cart'][$id] > $prodDetail[$id]['qty']) {
       echo "You requested " . $_POST['cart'][$id] .  " copies of $title, and we only have $qty in stock. We put our remaining stock into your cart.<br /><br />";
       $prodDetail[$id]['reqQty'] = $qty;
   }
   else {
       $prodDetail[$id]['reqQty'] = $_POST['cart'][$id];
   }
}

 

I still end up with the same referencing problem. I am copying pointers, instead of values, to my $prodDetail array, which results in my entire array being populated with copies of the last result row from the query.

 

I am trying to think of what you mean by this:

 

then just loop over the result set from that one query. Using prepared statements, this will require that you dynamically build the ->bind_parm parameters in an array and use call_user_func_array()

[Barand]

Thank you for jumping in. I think Barand was trying to get at something similar to what you were saying previously, but I did not understand how.

 

I showed you a far simpler method in reply #18 earlier

http://forums.phpfreaks.com/topic/270554-looping-confusion-with-mysqli-prepared-statements/?do=findComment&comment=1391890

 

That did not work for me. I think a key difference between my code and yours is that my $columns arrays don't contain anything. They are initialized as arrays with keys that have no values set yet.

 

No. The difference is you created an array of references. I created an array of arrays of references.

[nodirtyrockstar]

This

was extremely helpful for learning some new things!

 

Here's the final snippet that really nailed it down perfectly:

 

 

$meta = $stmt->result_metadata();
while ($field = $meta->fetch_field()) {
   $params[] = &$row[$field->name];
}
if (!call_user_func_array(array($stmt, 'bind_result'), $params)) {
   echo "Binding results failed: (" . $stmt->errno . ") " . $stmt->error;
}
while ($stmt->fetch()) {
   $x = array();
   foreach ($row as $key => $val) {
       $x[$key] = $val;
       if ($key === 'id') {
           $id = $val;
       }
   }
   $prodDetail[$id] = $x;