Looping Confusion With Mysqli Prepared Statements

[nodirtyrockstar]

I have a cart function that is supposed to use the post array (with product numbers as indices and the associated value is the customer's requested quantity) and populate product information in the session by accessing the database. I would like this function to add a $key and $value pair to $prodDetail for each item in the post array. Instead, it is replacing all the $value entries with the most recently selected product data. I do not see why it is resetting all of the $value fields upon each new iteration. Please let me know if this is still unclear after you have read the following.

 

Here is the function that I believe is the culprit:

 

 

function prodArr() {
if (!empty($_POST['cart'])) {
global $dbType;
$prodDetail = Array();
//connect to database
$mysqli = Database::getInstance();
//Retrieve product data
if ($dbType === 'distro') {
  $results = array('img' => &$img, 'artist' => &$artist, 'title' => &$title, 'label' => &$label, 'year' => &$year, 'price' => &$price, 'qty' => &$qty);
  $tbl = 'products';
}
elseif ($dbType === 'releases') {
  $results = array('img' => &$img, 'artist' => &$artist, 'title' => &$title, 'year' => &$year, 'price' => &$price, 'qty' => &$qty);
  $tbl = 'products';
}
elseif ($dbType === 'merch') {
  $results = array('img' => &$img, 'title' => &$title,'size' => &$size, 'color' => &$color, 'sex' => &$sex, 'price' => &$price, 'qty' => &$qty);
  $tbl = 'merch';
}
$query = 'SELECT ';
$query .= '`'.implode('`, `', array_keys($results)).'`';
$query .= ' FROM ' . $tbl . ' WHERE (`id` = ?) AND (`qty` > 0) AND (`agedOff` <> 1);';
foreach ($_POST['cart'] as $elKey => $element) {
  if (!$stmt = $mysqli->prepare($query)) {
     echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
  }
  if (!$stmt->bind_param('s', $elKey)) {
     echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
  }
  if (!$stmt->execute()) {
     echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
  }
  if (!call_user_func_array(array($stmt, 'bind_result'), $results)) {
     echo "Binding results failed: (" . $stmt->errno . ") " . $stmt->error;
  }
  if (!$stmt->fetch()){
     echo "Fetching results failed: (" . $stmt->errno . ") " . $stmt->error;
  }
  $prodDetail[$elKey] = $results;
  printArray($prodDetail);

 

I do not show the entire function, because I believe this is where the problem lies. Through dumping and echoing variables, I show that the $results array changes through each iteration of the foreach loop, but unfortunately it is setting all of the child arrays in $prodDetail to match the current $results array. All I want to do is add each new result set to the array. Maybe I have been staring at this for too long, but I can't seem to see the problem. Can someone please point how to fix this?

Edited November 10, 2012 by nodirtyrockstar

[nodirtyrockstar]

If you visit the page I am working on here, and try to put ten of each product into your basket, it will print the $prodDetail array through each iteration of the foreach loop and you can see the progression...

[Barand]

Your arrays are setting the values by reference so they all point to the same addresses in memory.

 

If you try this sample code. It sets 2 vars and puts them in an array.

It then changes the values and puts them in the second array.

 

But, as with yours, the values in the first array also change

 

$a = 1;
$b = 2;

$arr1 = array (
	    'a' => &$a,
	    'b' => &$b
    );

echo "OUTPUT A<br />";
echo '<pre> arr1 '.print_r($arr1, 1).'</pre>';		 // -> 1, 2

$a = 10;
$b = 20;

$arr2 = array (
	    'a' => &$a,
	    'b' => &$b
    );

echo "OUTPUT B<br />";
echo '<pre> arr1 '.print_r($arr1, 1).'</pre>';		 // -> 10, 20
echo '<pre> arr2 '.print_r($arr2, 1).'</pre>';		 // -> 10 20

[nodirtyrockstar]

Do you think the solution is to store each new iteration of $results in a new array, so therefore it won't matter that $results changes?

[nodirtyrockstar]

No that didn't seem to work. I have updated the code a bit. Here's the newest version:

 

function prodArr() {
  if (!empty($_POST['cart'])) {
     global $dbType;
     $prodDetail = Array();
     //connect to database
     $mysqli = Database::getInstance();
     //Retrieve product data
     if ($dbType === 'distro') {
        $results = array('img' => &$img, 'artist' => &$artist, 'title' => &$title, 'label' => &$label, 'year' => &$year, 'price' => &$price, 'qty' => &$qty);
        $tbl = 'products';
     }
     elseif ($dbType === 'releases') {
        $results = array('img' => &$img, 'artist' => &$artist, 'title' => &$title, 'year' => &$year, 'price' => &$price, 'qty' => &$qty);
        $tbl = 'products';
     }
     elseif ($dbType === 'merch') {
        $results = array('img' => &$img, 'title' => &$title,'size' => &$size, 'color' => &$color, 'sex' => &$sex, 'price' => &$price, 'qty' => &$qty);
        $tbl = 'merch';
     }
     $query = 'SELECT ';
     $query .= '`'.implode('`, `', array_keys($results)).'`';
     $query .= ' FROM ' . $tbl . ' WHERE (`id` = ?) AND (`qty` > 0) AND (`agedOff` <> 1);';
     foreach ($_POST['cart'] as $key => $reqQty) {
        if (!$stmt = $mysqli->prepare($query)) {
           echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
        }
        if (!$stmt->bind_param('s', $key)) {
           echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
        }
        if (!$stmt->execute()) {
           echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
        }
        if (!call_user_func_array(array($stmt, 'bind_result'), $results)) {
           echo "Binding results failed: (" . $stmt->errno . ") " . $stmt->error;
        }
        while($stmt->fetch()){
           $tmp = array(); // Attempt to store $results into temporary array
           $tmp = $results;
           $prodDetail[$key] = $tmp;
       }
       //If requested quantity is greater than inventory, put total qty available into cart
       if($reqQty > $qty) {
           echo "You requested $element copies of $title, and we only have $qty in stock. We put our remaining stock into your cart.<br /><br />";
           $prodDetail[$key]['reqQty'] = $qty;
        }
        else {
           $prodDetail[$key]['reqQty'] = $reqQty;
        }
        //$prodDetail[$key] = array_merge($results,$prodDetail[$key]);
        printArray($prodDetail);
        $stmt->free_result();
     }
     $stmt->close();

 

Any thoughts as to how I can capture each new iteration of the $results array without changing all of my variables would be a big help!

[nodirtyrockstar]

I keep researching this online, and could still use some help. Ultimately what I need to learn is how to pass a reference variable as a value. In other words, $results is passed by reference. I need to assign the values of the $results array to another array, and not have those values reference $results, just the values of $results at the time of the assignment.

[Barand]

Sorry for the delay - I was experimenting on one of my own tables.

 

I got this to work finally (it would be easier, though, to use the convention fetch row methods and loop through results)

 

$pups = array (1,2,3);	    // replaces your post array
$prodDetail = Array();

  $tbl = 'pupil';
  $columns = array('pupilid', 'pupil_name', 'houseid');
$query = 'SELECT ';
$query .= '`'.implode('`, `', $columns).'`';
$query .= ' FROM ' . $tbl . ' WHERE (`pupilid` = ?) ';
$data = array();
$i = 0;
foreach ($pups as $elKey) {
  $stmt = $mysqli->stmt_init();  
  if (!$stmt->prepare($query)) {
	  echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
  }
  if (!$stmt->bind_param('i', $elKey)) {
	  echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
  }
  if (!$stmt->execute()) {
	  echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
  }

  $results = array();
  foreach ($columns as $col) {   
   $results[] = &$data[$i][$col];
   }
  if (!call_user_func_array(array($stmt, 'bind_result'), $results)) {
	  echo "Binding results failed: (" . $stmt->errno . ") " . $stmt->error;
  }
  if (!$stmt->fetch()){
	  echo "Fetching results failed: (" . $stmt->errno . ") " . $stmt->error;
  }
  $prodDetail[$elKey] = $results;
  ++$i;
}
  echo '<pre>'.print_r($prodDetail, 1).'</pre>';

[nodirtyrockstar]

Thank you for your response. I feel like the issue with my code is how I am creating the $results array. I see that you did it differently, but I was wondering if you could explain how some of your changes work. More specifically, I am having a bit of trouble with understanding passing as reference. How can I assign a value to a variable from another variable that is passed as reference, without having the assigned variable change when the referenced variable changes?

[nodirtyrockstar]

Does my algorithm use the deprecated call time pass by reference? I was reading about that, too. I am also looking at the ReflectionClass in order to pass a dynamic array to bind_params.

[Barand]

The problem is that the binding needs the variables to passed by reference so we need to avoid putting values into the same ones for multiple records (or they all end up the same as we've seen).

 

In my version I create an array of references, but as I put them in a different array element each time with

 

  $results = array();
  foreach ($columns as $col) {   
       $results[] = &$data[$i][$col];
       }

 

it avoids the over-writing problem.

[nodirtyrockstar]

It is my understanding that assigning a variable with a referenced variable actually copies the reference.

[nodirtyrockstar]

Okay, not fully understanding it, I did my best to implement your solution. Now it is not copying my array keys. I would really benefit from an explanation of each step you take to copy the value without carrying over the reference so that I can make it work in my own script.

[Barand]

Imagine a single row of pigeon holes, one for each column in the result.

The first record's values are dropped into these pigeon holes. But the trouble is that all your following records point to the same pigeon holes so, as a new value is placed in there, each of the previous records now also pick up those same values.

 

What my code does is to create a new row of pigeon holes each time round the loop ($data[$i][colname]) so each record has its own set of pigeon holes.

[nodirtyrockstar]

I understand what is happening with my code in theory. The reference variables are set to point to the most recent result set. What I don't understand is why your solution works. Why does &$data[$i][$col] get around duplicating the reference and actually assign just a value?

[Barand]

Why does &$data[$i][$col] get around duplicating the reference and actually assign just a value?

 

Because you only have a single row of pigeon holes (effectively $data[colname]) whereas I have multiple ($data[$i][colname])

[nodirtyrockstar]

Maybe I am being dense, but that doesn't help me.

[nodirtyrockstar]

Maybe it will help to take a step backt. I noticed that you are not declaring the initial array with references, and that you are instead only using the ampersand here:

 

&$data[$i][$col]

 

Can you explain the difference?

[Barand]

If you don't understand what you are doing, this does the same thing

 

<?php
$mysqli = new mysqli('localhost', '****', '************', 'jointute');

$pups = array (1,2,3);		 // replaces your post array
$pupilList = join (',', $pups);

$prodDetail = array();

$res = $mysqli->query("SELECT pupilid, pupil_name, houseid FROM pupil
    WHERE pupilid IN ($pupilList)");
while ($row = $res->fetch_assoc()) {
   $product_detail[] = $row;
}

echo '<pre>'.print_r($product_detail, 1).'</pre>';
?>

[Barand]

Maybe it will help to take a step backt. I noticed that you are not declaring the initial array with references, and that you are instead only using the ampersand here:

 

 

 

Can you explain the difference?

 

I don't declare the array initially as you did because I am creating the new arrays in each loop - my new rows of pigeon holes

[nodirtyrockstar]

Each reference is new! I see it now. I will try to mess around with it some more now that I finally understand what you are doing. My server is down at the moment.

[Barand]

Ta-daaah!

[nodirtyrockstar]

I have gotten this far with it:

 

function prodArr() {
   if (!empty($_POST['cart'])) {
   global $dbType;
   $prodDetail = array();
   $columns = array();
   $data = array();
   $img = '';
   $artist = '';
   $title = '';
   $label = '';
   $year = null;
   $color = '';
   $size = '';
   $sex = '';
   $qty = 0;
   $price = 0.00;

   //check for database type and set up $results (array) and $tbl (string) accordingly
   if ($dbType === 'distro') {
       $columns = array('img' => $img, 'artist' => $artist, 'title' => $title, 'label' => $label, 'year' => $year, 'price' => $price, 'qty' => $qty);
       $tbl = 'products';
   }
   elseif ($dbType === 'releases') {
       $columns = array('img' => $img, 'artist' => $artist, 'title' => $title, 'year' => $year, 'price' => $price, 'qty' => $qty);
       $tbl = 'products';
   }
   elseif ($dbType === 'merch') {
       $columns = array('img' => $img, 'title' => $title,'size' => $size, 'color' => $color, 'sex' => $sex, 'price' => $price, 'qty' => $qty);
       $tbl = 'merch';
   }
   $mysqli = Database::getInstance();
   $query = 'SELECT ';
   $query .= '`'.implode('`, `', array_keys($columns)).'`';
   $query .= ' FROM ' . $tbl . ' WHERE (`id` = ?) AND (`qty` > 0) AND (`agedOff` <> 1);';
   //query for each item in post array
   foreach ($_POST['cart'] as $key => $reqQty) {
       if (!$stmt = $mysqli->prepare($query)) {
           echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
       }
       if (!$stmt->bind_param('s', $key)) {
           echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
       }
       if (!$stmt->execute()) {
           echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
       }
       $results = array();
       foreach ($columns as $colKey => $col) {
           $results[] = &$data[$colKey][$col];
       }
       if (!call_user_func_array(array($stmt, 'bind_result'), $results)) {
           echo "Binding results failed: (" . $stmt->errno . ") " . $stmt->error;
       }
       if (!$stmt->fetch()) {
           echo "Fetching results failed: (" . $stmt->errno . ") " . $stmt->error;
       }
       $prodDetail[$key] = $results;
       printArray($prodDetail);

 

I would like for my original array keys in the original $columns array to appear in the final $prodDetail array, and I am having trouble doing that with your example. I tried a couple variations of this line:

 

$results[] = &$data[$colKey][$col]; //this duplicates the reference AND omits the keys from the original $columns array
$results[$colKey] = &$data[$colKey][$col]; //this duplicates the reference, I think
$results[$key] = &$data[$colKey][$col]; //this completely confuses the result binding

}

 

How do you recommend that I keep my array keys, which, like the original array variables, are generated dynamically?

[Barand]

In my code, changing

 

  $results = array();
  foreach ($columns as $col) {   
    $results[] = &$data[$i][$col];
    }

 

to this

 

  $results = array();
  foreach ($columns as $col) {   
    $results[$col] = &$data[$i][$col];
    }

 

preserves the column names as keys as you wanted

[nodirtyrockstar]

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.

[PFMaBiSmAd]

This is a lot of work to execute a query to get product details, which btw should not be inside of a loop.

 

For your existing code, the ->prepare() and ->bind_parm() statements should be before the start of your loop.

 

You should actually be executing the query once, before the start of your loop, with a where clause - WHERE id IN(?,?,? - repeat for the number of id's...), 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()