是否可以增加或减去前一个循环的值？

Question

我有一张名为item_movement的表，其中存储了顾客购买、出售、退回或退回给供应商的库存数量：

------------------------------------------------------------------------
| trans_id |    date     |   trans_type     | inventory_id | quantity  |
------------------------------------------------------------------------
|     1    | 2016-07-26  | Purchase         |      1       |    10     |
------------------------------------------------------------------------
|     2    | 2016-07-26  | Purchase         |      2       |     8     |
------------------------------------------------------------------------
|     3    | 2016-07-27  | Sale             |      1       |     2     |
------------------------------------------------------------------------
|     4    | 2016-07-28  | Customer Return  |      1       |     1     |
------------------------------------------------------------------------
|     5    | 2016-07-29  | Supplier Pullout |      2       |     5     |
------------------------------------------------------------------------

inventory_id是每个库存的标识。

我的代码是：

$conn = new mysqli('localhost', 'username', 'password', 'database');
$query = $_GET['id'];
$item_stock = $conn->query("SELECT * FROM item_movement WHERE `inventory_id` = '%".$query."%' ORDER BY date DESC") or die(mysql_error());

echo '<table class="stock-info-table">
          <tr class="center">
            <th>Date</th>
            <th>Transaction Type</th>
            <th>Quantity</th>
            <th>Running Stock</th>
          </tr>';
while($row = mysqli_fetch_array($item_stock)){
        echo '<tr>
          <td>'.$row['date'].'</td>
          <td>'.$row['trans_type'].'</td>
          <td>'.$row['quantity'].'</td>
          <td>RUNNING STOCK HERE</td>
        </tr>';
}
echo '</table>';

问题:正如您在代码中所看到的那样，我还没有弄清楚如何显示运行中的股票。运行库存列应该显示每个交易的库存移动数，比如如果库存ID 1的购买量是10，那么它应该显示10。如果有一个销售数量为2，那么它应该显示8 (10-2)。基本上，股票的公式是:购销+客户退货-供应商退出。数据还应显示最近的交易优先(DESC)，其中运行股票的顺序也应该基于。

编辑:下面是我想为inventory_id显示的一个示例数据：

-------------------------------------------------------------------
|    Date     |   Transaction Type   |  Quantity  | Running Stock |
-------------------------------------------------------------------
|  2016-07-29 |   Purchase           |     5      |     12        |
-------------------------------------------------------------------
|  2016-07-28 |   Supplier Pullout   |     2      |      7        |
-------------------------------------------------------------------
|  2016-07-27 |   Customer Return    |     1      |      9        |
-------------------------------------------------------------------
|  2016-07-26 |   Sale               |     2      |      8        |
-------------------------------------------------------------------
|  2016-07-25 |   Purchase           |     10     |     10        |
-------------------------------------------------------------------

如果可能的话请告诉我。

Answer 1

您可以通过将股票的运行总量保存在一个变量中来做到这一点。

我假设您在某个地方持有当前的股票，但为了演示目的，此示例将将值从零开始。

还可以使用mysqli_fetch_assoc()，因为它只返回一个assoc列数组，而不是一个数字数组。

$cur_stock = 0;

$id= $_GET['id'];
$item_stock = $conn->query("SELECT * 
                            FROM `item_movement` 
                            WHERE `inventory_id` = $id 
                            ORDER BY date ASC") 
              or die(mysql_error());

echo '<table class="stock-info-table">
          <tr class="center">
            <th>Date</th>
            <th>Transaction Type</th>
            <th>Quantity</th>
            <th>Running Stock</th>
          </tr>';

while($row = $conn->fetch_assoc($item_stock)){

    switch ($row['trans_type']) {
        case 'Purchase' :
        case 'Customer Return' :
            $cur_stock += (int)$row['quantity'];
            break;

        case 'Sale' :
        case 'Supplier Pullout' :
            $cur_stock -= (int)$row['quantity'];
            break;
    }

    echo '<tr>
            <td>'.$row['date'].'</td>
           <td>'.$row['trans_type'].'</td>
            <td>'.$row['quantity'].'</td>
            <td>' . $cur_stock . '</td>
          </tr>';
}
echo '</table>';

最好使用准备语句和参数化语句

其中一个例子就是

$cur_stock = 0;

$id= $_GET['id'];

$stmt = $conn->prepare("SELECT * 
                         FROM `item_movement` 
                         WHERE `inventory_id` = ? 
                         ORDER BY date ASC"); 
if ( $stmt === false ) {
   echo $conn->error;
   exit;
}
$stmt->bind_param('i', $id);

$status = $stmt->execute();
if ( $status === false ) {
   echo $conn->error;
   exit;
}

$result = $stmt->get_result();

while($row = $result->fetch_assoc($item_stock)){

    switch ($row['trans_type']) {
        case 'Purchase' :
        case 'Customer Return' :
            $cur_stock += (int)$row['quantity'];
            break;

        case 'Sale' :
        case 'Supplier Pullout' :
            $cur_stock -= (int)$row['quantity'];
            break;
    }

    echo '<tr>
            <td>'.$row['date'].'</td>
           <td>'.$row['trans_type'].'</td>
            <td>'.$row['quantity'].'</td>
            <td>' . $cur_stock '</td>
          </tr>';
}
echo '</table>';

获得所需输出(即反向输出)的最简单方法是将结果暂时存储在数组中，然后在输出HTML表之前反转数组。

$temp = array();
while($row = $result->fetch_assoc($item_stock)){

    switch ($row['trans_type']) {
        case 'Purchase' :
        case 'Customer Return' :
            $cur_stock += (int)$row['quantity'];
            break;

        case 'Sale' :
        case 'Supplier Pullout' :
            $cur_stock -= (int)$row['quantity'];
            break;
    }
    $row['running_stock'] = $cur_stock;
    $temp[] = $row;
}
// reverse the array
$temp2 = array_reverse($temp);

foreach($temp2 as $row) {
    echo '<tr>
            <td>' . $row['date']          .'</td>
            <td>' . $row['trans_type']    .'</td>
            <td>' . $row['quantity']      .'</td>
            <td>' . $row['running_stock'] .'</td>
          </tr>';
}
echo '</table>';

Answer 2

为了清晰起见，我保留了这个答案(如果OP需要获取库存物品的库存数量)，但不幸的是，对于OP正在寻找的不起作用。这将得到股票，但不完全增加运行的股票，因为事情被添加/删除。关于如何做到这一点，见@RiggsFolly的答案。

通过在mysql中使用GROUP和聚合函数，您可以根据事务类型总结总计。此查询将为您提供您要寻找的股票：

SELECT `inventory_id`, SUM(
    CASE
      WHEN trans_type IN ('Purchase', 'Customer Return') THEN quantity
      WHEN trans_type IN ('Customer Return', 'Supplier Pullout') THEN -1 * quantity
    END
  ) AS `stock`
  FROM item_movement
  WHERE `inventory_id` LIKE '%".$query."%'
  GROUP BY inventory_id
  ORDER BY date DESC

首先按查询运行一个组，以获取股票，然后填充一个数组。在第二个循环中，引用这个第一个数组。您的更新代码如下：

<?php
$query = $_GET['id'];

//DO NOT CONCAT QUERY HERE. VERY BAD (See my comment below)
$running_stock = $conn->query("
  SELECT `inventory_id`, SUM(
    CASE
      WHEN trans_type IN ('Purchase', 'Customer Return') THEN quantity
      WHEN trans_type IN ('Customer Return', 'Supplier Pullout') THEN -1 * quantity
    END
  ) AS `stock`
  FROM item_movement
  WHERE `inventory_id` LIKE '%".$query."%'
  GROUP BY `inventory_id`") or die(mysqli_error());

$running_stocks = array();

while($row = mysqli_fetch_array($running_stock)){
    $running_stocks[ $row['inventory_id'] ] = $row['stock'];
}
//DO NOT CONCAT QUERY HERE. VERY BAD (See my comment below)
$item_stock = $conn->query("SELECT * FROM item_movement WHERE `inventory_id` LIKE '%".$query."%' ORDER BY date DESC") or die(mysqli_error());

echo '<table class="stock-info-table">
          <tr class="center">
            <th>Date</th>
            <th>Transaction Type</th>
            <th>Quantity</th>
            <th>Running Stock</th>
          </tr>';
while($row = mysqli_fetch_array($item_stock)){
    echo '<tr>
          <td>'.$row['date'].'</td>
          <td>'.$row['trans_type'].'</td>
          <td>'.$row['quantity'].'</td>
          <td>'.(isset($running_stocks[$row['inventory_id']]) ? $running_stocks[$row['inventory_id']] : 0).'</td>
        </tr>';
}
echo '</table>';

请注意:为了防止将参数绑定到查询.注入，您应该是。

Answer 3

您应该能够使用纯SQL解决方案来完成这一任务。

如果将表与其自身连接，匹配的行是旧的，那么每一行都可以包含所有旧的数量。然后，您可以使用SUM将较旧的数量相加，以得到正在运行的总量。

由于您的某些量可能实际上是负的，这取决于事务类型的值，因此需要在和聚合函数中有条件地将这些值乘以-1。

SELECT a.`Date`,
        a.`Transaction Type`,
        a.`Quantity`,
        SUM
        (
            CASE b.`Transaction Type`
                WHEN 'Supplier Pullout'
                THEN b.`Quantity` * -1
                WHEN 'Sale'
                THEN b.`Quantity` * -1
                ELSE b.`Quantity`
            END
        ) AS `Running Stock`
FROM item_movement a
INNER JOIN  item_movement b
ON a.inventory_id = b.inventory_id
AND a.`date` >= b.date
WHERE a.inventory_id = '%".$query."%' 
GROUP BY a.`Date`,
        a.`Transaction Type`,
        a.`Quantity`
ORDER BY a.`date` DESC