Jump to content

Looping Confusion With Mysqli Prepared Statements


nodirtyrockstar

Recommended Posts

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 by nodirtyrockstar
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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>';

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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>';
?>

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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()

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.