foreach inside while loop returning duplicate data

Question

Context: I have an ordering form, that has a html select and a number input. so a user selects the item and enter the amount they want of that item, these are sent as arrays to the hander page. $_POST['item']; is an array of id's that I want to select product info from the database with. $amount = $_POST['amount']; is just an array of the amounts of each item.

Problem: Each row is being duplicated by the amount of rows there are, so in this case it's returning three rows, but duplicating each one, three times.

Objective: All I'm trying to do is foreach $_POST['item'] get that rows data from the database and show them and the respective amounts in a table, so the user can confirm the order.

handle.php

<?php 
$item = $_POST['item']; // array of product ids to select data from db
$amount = $_POST['amount']; // array of amounts of each product the user ordered
$note = $_POST['note'];
$i = 0;

$each = implode(',',$item);

$stmt = $dbh->query('SELECT * 
          FROM `products` 
         WHERE `id` IN (' . $each . ')');


        <table class="table">
          <thead>
            <tr>
              <th scope="col">Item</th>
              <th scope="col">Quantity</th>
              <th scope="col">Price</th>
            </tr>
          </thead>
          <tbody>

    <?php 
while ($row=$stmt->fetch(PDO::FETCH_ASSOC)) {

    $product_id = $row['id'];
    $product_name = $row['name'];
    $product_price = $row['price'];
    $row['quantity'] = $amount[$row['id']];

    print "<pre>";
    print_r($row);
    print "</pre>";
    ?>

    <tr>
     <td><?php echo $product_name;?></td>
     <td><?php echo $row['quantity'];?></td>
     <td><?php echo $product_price;?></td>
    </tr>


<?php } ?>
              </tbody>
            </table>

Answer 1

I'm not really sure what you're trying to do, but you are reassigning

$key = array() ;

immediately after your

foreach ($amount as $key) 

That's causing your

<td><?php echo $key;?></td>

to try to echo an array because you overwrote the value of $key assigned by the foreach.

Your post does not detail what data is getting duplicated so I can't really address that in this answer.

You are duplicating the same three rows because you are setting

$new = array_combine($item, $amount);

Then your SQL is grabbing the rows

$stmt = $dbh->query('SELECT * 
      FROM `products` 
     WHERE `id` IN (' . $each . ')');

Then you're looping over the same items with

foreach ($new as $key => $val) {

If you want to display the items you found in the SQL then you shouldn't have the

foreach ($new as $key => $val) {

inside your while() loop. Your while() is already looping over the rows returned for those items. This assumes you only have one product per item number.

If you expect one or more 'products' to be returned for each item number then you should be executing your SQL while looping through foreach($new), but that doesn't appear to be what the top part of your code is doing.

After some back and forth we've identified the issue: the amounts need to be tied to the item numbers.

You are getting items numbers and quantities as arrays from your HTML. So you need to loop through the items and associate them with your quantities.

// where your qualities will live
$quantities = array() ; 

// loop through the array of items you received and match them up with their quantity 
foreach($item as $k=>$id) { 
    $quantities[$id] = $amount[$k] ; 
}

Then you can access the quantity in your while loop using:

$row['quantity'] = $quantities[$row['id']] ;