php 將兩個不同 array 算出個別的價格並加總？

Question

假設前端為

<input type="hidden" name="cart_prod_id[]" value="<?=$row['prod_id'];?>">
<input type="hidden" name="cart_quantity[]" value="<?=$row['quan'];?>">

後端

foreach($_POST["cart_prod_id"] as $value){
   echo $value;
}
foreach($_POST["cart_quantity"] as $value){
   echo $value;
}

送出後可以得到假設

11510612910751031

但其實是

// 商品ID
115
106
129
107

// 數量
5
10
3
1

我要先找出商品ID，去找他自己的 price
之後再回來乘上對應的數量

// 商品ID -> 數量
115 -> 5
106 -> 10
129 -> 3
107 -> 1

請問我該怎麼算出每個商品價格並加總？！
我的作法是這樣
但都只會算最後那一筆

$i = 0;
$productTotal = null;
foreach($_POST["cart_prod_id"] as $value){
      $product = mysqli_fetch_array($pdo->query(
        "SELECT
          p.price,
          p.original_price
        FROM `product` as p
        WHERE p.prod_id = '{$value}' "
      ));

      $productTotal += $product['price'] * $_POST["cart_quantity"][$i];

      $i++;
    }
    
echo $productTotal;

思路已死，懇請大神降臨

Answer 1

<?php
# 以下代码均未作容错处理。
$goods_id = [1,2,3,4,5,6];
$number = [1,5,6,7,7,8];

$goods_number = array_combine($goods_id,$number);
$goods_id_tmp = implode(',', $goods_id);
$sql = <<<SQL
SELECT 
    `p`.`prod_id`,`p`.`price`,`p`.`original_price`
FROM `product` as p
WHERE `p`.`prod_id` in ({$goods_id_tmp})

SQL;
# .... 此处假设你执行了上面SQL 拿到了结果 这里是假数据
$result = [
    [
    'prod_id'=>2,'price'=>'10.22','original_price'=>'100.00',
    ],
    [
    'prod_id'=>2,'price'=>'120.22','original_price'=>'10310.00',
    ],
    [
    'prod_id'=>3,'price'=>'130.22','original_price'=>'103210.00',
    ],
    [
    'prod_id'=>4,'price'=>'150.22','original_price'=>'12300.00',
    ],
];
$result = array_reduce($result, function($item,$next)use($goods_number){
    $number =$goods_number[$next['prod_id']]??0; 
    return bcmul($next['price'],$number,2);
},0);

var_dump($result);