How to store a result of a row in a variable and use it in the next row in MySQL SELECT query?

Question

I have the following tables:

Stocks

id	name	amount
1	Pen	35
2	Cd	21
3	Bag	15

StockUnits

id	name	include	stockid
1	onepen	1	1
2	dozen	6	1
3	pocket	24	1
4	onecd	1	2
5	pocket	5	2
6	onebag	1	3

So how do I get a result like the following with SELECT query?

stock	unit	qty
Pen	onepen	5
Pen	dozen	1
Pen	pocket	1
Cd	onecd	1
Cd	pocket	4
Bag	onebag	15

As we see, each stock has some units. Now I want to know how many units do i have according to the amount field, For example:
We have 35 pens which is equal to 1 pocket and 1 dozen and 5 onepen because:

For pocket we have 1 pocket because (each pocket includes 24 pen)
35/24=1
35%24=11
For dozen we have 1 dozen because (each dozen includes 6 pen)
11/6=1
11%6=5
For onepen we have 5 onepen because
5 onepen because we don't have any smaller unit

Now we have:
1 pocket
1 dozen
5 onepen

Same calculation for Cd and Bag...

I know to get the desired result a temporary variable should be used to store the result of the MOD and use the variable for the next row but how?

Answer 1

How to store a result of a row in a variable and use it in the next row in MySQL SELECT query?

A pattern for MySQL version 5.x:

SELECT .. , 
       {an expression which uses @variable with the value from previous row} AS calculated_column,
       .. ,
       @variable := {an expression for current row which will be used for next row processing} AS dummy_column,
       ..
FROM {source single table or subquery}
CROSS JOIN ( SELECT @variable := {initial variable value} ) AS init_variable
ORDER BY {ordering expression which provides rows uniqueness}

On MySQL 8+ use window function(s).

Answer 2

First of all, your Stocks and StockUnits make no sense, or they are at least bad designed.

You show that you store the id of each stock-row in multiple unit-rows, but it really should be the other way around.

How Stocks table should be:

id	name	qty	unit_id
1	Pen	5	1
2	Pen	1	2
3	Pen	1	3
...

How StockUnits table should be:

id	name	include
1	onepen	1
2	dozen	6
3	pocket	24
...

Example

Once you fix the design mistake, all you would need to do is join to get unit's name, for example in Laravel do something like:

$stockTable = Stock::with('unit')->all();

echo 'stock | unit | qty';
foreach ($stockTable as $item) {
    echo $item->name . ' | ' . $item->unit->name . ' | ' . $item->qty;
}

To get output:

name	unit	qty
Pen	onepen	5
Pen	dozen	1
Pen	packet	1
...