时间戳

首页 > 解决方案 > 获取mysql和php的平均购买价格

php - 获取mysql和php的平均购买价格

问题描述

我坚持要获得当前可用库存的平均购买价格，我有 3 个 mysql 表。

产品表

| prodId | prodName             | supId | purPrice | stock |
|-------:|----------------------|------:|---------:|------:|
|      3 | Kamyab 12 Ltr Ctn    |     8 |     3450 |   201 |
|      4 | kamyab 12 kg ctn     |     8 |     3420 |   406 |
|      5 | kamyab 1/2 10 kg ctn |     8 |     2850 |   188 |
|      6 | kamyab 1/4 10 kg ctn |     8 |     2870 |   172 |
|      7 | Zoqeen 16 1/2 kg ctn |     3 |     4400 |    94 |
|      8 | Zoqeen 16 1/4 kg ctn |     3 |     3310 |   290 |
|      9 | Zoqeen 1/16 900 gm   |     3 |     3490 |     1 |
|     10 | Awami 16 kg ctn      |     2 |     4380 |  1226 |
|     11 | Awami 12 ltr 900ml   |     2 |     2850 |     1 |
|     12 | Dastak 5 ltr Bottle  |     7 |      655 |     0 |
|     13 | Shandar 900 ml Ctn   |     8 |     2865 |     0 |
|     14 | kamyab 16 kg Tin     |     8 |     4786 |   175 |

采购表

| purId | supId | InvNo    | invDate    | invDueDate | invMode | invAmount |
|------:|------:|----------|------------|------------|--------:|----------:|
|     8 |     8 | 20102010 | 2021-10-20 | 2021-11-19 |       2 |    970650 |
|    10 |     8 | 3520     | 2021-10-20 | 2021-12-04 |       2 |   5730000 |
|    13 |     2 | 001      | 2021-10-20 | 2021-12-04 |       2 |  19316000 |
|    15 |     8 | 01576    | 2021-10-22 | 2021-12-06 |       2 |   1671200 |
|    16 |     2 | 1525     | 2021-10-22 | 2021-10-22 |       1 |     41403 |

购买明细表

| purDetailId | purId | prodId |  qty |   price |   |   |   |   |
|------------:|------:|-------:|-----:|--------:|---|---|---|---|
|          10 |     8 |      4 |  200 |    3421 |   |   |   |   |
|          11 |     8 |      5 |   50 |    2858 |   |   |   |   |
|          12 |     8 |      6 |   50 |    2871 |   |   |   |   |
|          14 |    10 |     13 | 2000 |    2865 |   |   |   |   |
|          17 |    13 |     10 | 4400 |    4390 |   |   |   |   |
|          19 |    15 |     14 |  200 |    4786 |   |   |   |   |
|          20 |    15 |      3 |  200 |    3570 |   |   |   |   |
|          21 |    16 |      3 |   12 | 3450.21 |   |   |   |   |

我需要获得可用库存的确切平均购买价格，到目前为止，我尝试自己获得所需的结果，但我失败了

下面是一些我尝试但没有得到期望结果的代码

$sql = "select * FROM purchase ORDER BY invDate DESC";
$res = $database->mysql->query($sql);
$fetchQty = 0;
$remainingQty = 0;
$productArray = array();
while($run = $res->fetch_assoc()){
  $purId = $run['purId'];
  $purIds = $purIds.$purId.',';
}
$purIds = substr($purIds, 0,-1);
$purIds = $purIds.')';
$query = "SELECT * FROM product";
$result = $database->mysql->query($query);
while($row = $result->fetch_assoc()){
      $prodId = $row['prodId'];
      $stock = $row['stock'];
      $prodName = $row['prodName'];
      $purPrice = $row['purPrice'];
      $sql = "SELECT qty, price FROM purchasedetail WHERE prodId = $prodId and purId in $purIds ORDER BY purId DESC";
      $res = $database->mysql->query($sql);
      if($res->num_rows > 0){
      while($run = $res->fetch_assoc()){
        $qty = $run['qty'];
        $price = $run['price'];
        $remainingQty = $stock-$qty;
        $amount = $qty * $price;
        if($stock>$fetchQty){
        $fetchQty = $fetchQty+$qty;
        $fetchAmount = $fetchAmount+$amount;

      }
      elseif ($fetchQty>$stock) {
        $remaining = $fetchQty - $stock;
        $amount = $remaining * $price;
        $fetchQty = $remaining;
        $fetchAmount = $fetchAmount+$amount;
      }
      else{
        $fetchQty = $fetchQty+$qty;
        $fetchAmount = $fetchAmount+$amount;
      }

      }
      $productArray[]=array("prodId"=>$prodId,
                  "prodName"=>$prodName,
                  "fetchQty"=>$fetchQty,
                  "fetchAmount"=>$fetchAmount,
                  "stock"=>$stock);
                  $fetchQty = 0;
                  $fetchAmount = 0;

    }
    else{
      echo 'Prod Id '.$prodId.'Purchase Quantity 0 averege rate from purchase rate <br>';
    }

      }
      echo '<pre>';
      print_r($productArray);
      echo '</pre>';```

标签： phpmysql

解决方案

要获得产品表的平均价格，

$query = "SELECT * FROM product";
$result = $database->mysql->query($query);
$row_cnt = mysqli_num_rows($result); //Number of rows
while($row = $result->fetch_assoc()){
      $purPrice += $row['purPrice']; //Add together all purPrice tables
}
$average = ($purPrice / $row_cnt); //Calculate the average based on amount of entries
echo $average;

我评论了解释，尝试将此代码实现到您的文件中。

编辑

考虑使用准备好的语句，出于安全目的，您稍后可能需要它。

推荐阅读