doubts regarding a dynamic select

Question

My question is this, I have a table in the bank with the following specifications.

ID | Produto | Qtdade | valor unitário de venda
--------------------------------------
1  | ABC     | 5000   | 10
2  | ABC     | 2000   | 9.5
3  | ABC     | 3000   | 11

From this table I need to make a sale, in which I always have to take the first, when finish the amount of the first dai caught the next. So let’s assume that the sale I’m going to make is 8000 units, I have to list the order as follows:

PRODUTO  |  QUANTIDADE  |  VALOR TOTAL
ABC      | 5000         | 50000
ABC      | 2000         | 19000
ABC      | 1000         | 11000

Can someone suggest me a way to do this?

part of the code

$pedido = 8000; // (veio da quantidade da tabela de pedido)

$sql_pedido = 'SELECT * FROM estoque';
$busca_pedido = mysqli_query($mysqli,$sql_pedido);

while ($linha_pedido = mysqli_fetch_object($busca_pedido)) {

    if ($pedido > 0) {
    $quant = $linha_pedido->quantidade;
    $valor = $linha_pedido->valorUnit;
    $pedido = $pedido - $linha_pedido->quantidade;
    $total = $quant*$valor;
    echo "$quant - R$ $valor ... Total em R$ $total<br>";
    }

}

Answer 1

You need to do something like below, extracted from SO-en:

SELECT *, @TOTAL := @TOTAL + quantidade AS total
FROM tabela1, (SELECT @TOTAL := 0) t
WHERE @TOTAL <= 8000

But note that the above query does not return exactly what you need, as it selects the last row with the total amount available in the item, totaling in the column total 10000 instead of 8000.

I believe that this already helps you, because all you have to do is take the side of php to get what you need. In the link I passed above you have more examples of how to do what you need.

Answer 2

You can sort requests with the SQL query with ORDER BY nome_do_compo DESC

SELECT * FROM produtos WHERE id_compra = 'o id da compra' ORDER BY quantidate DESC;

sql above sorts the products by the field 'quantity' in descending order, the inverse of DESC Voce can use ASC which leaves in ascending order.

So the records already come in print order on the screen.

A complementary reading
https://www.techonthenet.com/mysql/order_by.php

In order to manipulate values you can do something like this:

$lista = [
        [1,"ABC",5000,10],
        [2,"ABC",2000,9.5],
        [3,"ABC",3000,11]
    ];

    $valorTotal = 8000;
    $valor = 0;

    foreach($lista as $key => $linha){
        // verifica se a soma passa do valor total 
        if($valor + $linha[2] < $valorTotal){
            $valor =  $valor + $linha[2];
            // zera o valor desse registro
            $lista[$key][2] = 0;
        }else{
            // ve a diferença dos valores
            $debito = $valorTotal - $valor;
            // debita apenas a diferença
            $saldoTranspor = $linha[2] - $debito;

            $lista[$key][2]  = $saldoTranspor ;
        }
    }
    echo "<pre>";
    print_r($lista);
    echo "</pre>";

So you only need to do a new query to update the database