Is it possible to add or subtract from value of previous loop?

Question

I have a table called item_movement wherein I store number of inventory purchased, sold, returned by customer or returned to supplier:

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

The inventory_id is the identification of each of the inventory.

My code is:

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

Problem: I haven't figured out how to show the running stock, as you can see in my code. The running stock column should show the number of stock movement per transaction like if the purchase quantity for inventory ID 1 is 10, then it should show 10. If there's a sale quantity of 2, then it should show 8 (10-2). Basically the formula for the stock is: Purchase - Sale + Customer Return - Supplier Pullout. The data should also show the recent transactions first (DESC), in which the sequence of the running stock should also be based.

Edit: Here's an example data I would like to show for an 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        |
-------------------------------------------------------------------

Let me know if this is possible.

I think it would help if you give us the expected output of the table you've provided. I'm still unsure of how you're trying to calculate the running stock. — Blue, Aug 02 '16 at 08:50
_Side Note:_ Dont use LIKE for this select use `inventory_id = $query` LIKE is for matching bits of a string against a longer string — RiggsFolly, Aug 02 '16 at 08:53
You will also have to show us how you expect to obtain the starting value for your current `IN_STOCK` value — RiggsFolly, Aug 02 '16 at 08:55
Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared statement and parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Aug 02 '16 at 08:57
@RiggsFolly I don't think he is using mysql_ extension, he is using mysqli — MuthaFury, Aug 02 '16 at 08:58
Can you show your connection code? I'd like to help you bind it properly to your database. — Blue, Aug 02 '16 at 09:03
@FrankerZ I've updated the code with my desired output and connection to database. — hello, Aug 02 '16 at 09:10
@RiggsFolly regarding the equal sign, I've updated it, thanks! Starting value for in_stock is always the first transaction which is a purchase transaction. It would be safe to say that it will start at zero then will add or subtract depending on the trans_type. Regarding the SQL Injection, if I'm running this script just locally, is it still prone to attacks? — hello, Aug 02 '16 at 09:13
RE: Running local: The point is if you get into good habits while you are learning you wont make that mistake when it comes to writing something that will go into the wild — RiggsFolly, Aug 02 '16 at 09:25

RiggsFolly · Accepted Answer · 2016-08-02T11:02:39.227

You can do this by keeping a running total of your stock in a variable.

I assume you have the current stock held somewhere, but this example will start the value at zero for demo purposes.

Also use mysqli_fetch_assoc() as that returns just an assoc array of columns and not a numeric array as well.

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

It would be better to use prepared statement and parameterized statements

An example of that would be

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

The simplest way to get the output you want i.e. reversed would be to temporarily store the result in an array and then reverse the array before outputting your HTML table.

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

@OP posted his connection code. I think it would be best if you rewrote the concatenation to use binded statements instead. — Blue, Aug 02 '16 at 09:13
@RiggsFolly This is working great, except on the part that the Running Stock is in the ascending manner instead of descending. — hello, Aug 02 '16 at 10:18
@RiggsFolly yes that made sense, but is it possible to reverse the order, just like the table I posted above? — hello, Aug 02 '16 at 10:36

score 1 · Answer 2 · edited May 23 '17 at 12:00

I'm keeping this answer around for clarity (If the OP ever needs to fetch the stock quantity for inventory items), but unfortunately this will not work for what the OP is looking for. This will get the stock, but not total up the running stock as things get added/removed. See @RiggsFolly's answer for how to do that.

By using GROUP BY and aggregation functions within mysql, you can sum up the total by the transaction type. This query should get you the stock that you're looking for:

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

Run a group by query first, to get the stocks, and then populate an array. In the second loop, reference this first array. Your updated code here:

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

Please note: You should be binding parameters to your queries. to prevent against SQL injection.

score 1 · Answer 3 · answered Aug 02 '16 at 10:37

You should be able to do this with a pure SQL solution.

If you join the table against itself, where the matching rows are older then you can have each row with all the older quantities. Then you can use SUM to add up the older quantities to get the running total.

As some of your quantities can be effectively negative depending on the value of Transaction Type you will need to conditionally multiply those ones by -1 within the SUM aggregate function.

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

Is it possible to add or subtract from value of previous loop?

3 Answers3