Click here to Skip to main content
15,920,632 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Please help me let me explain with this.....

purchase table

id purchase_id product_id qty net_unit_cost created_at

1 1 1 10 10 2022-10-10
2 2 1 15 12 2022-10-10

Sale table

sale_id product_id qty net_unit_price created_at

1 1 11 15 2022-10-10

in this, if i sold '11' units then how can i subtract from the rows to get remaining units? i've to subtract '10' units from first row and '1' unit from second row. Please help how to calculate fifo in php

What I have tried:


// stock_outcomes
$db = mysqli_connect('localhost','root','','pos1010');
$sql = mysqli_query($db,"SELECT * FROM `product_sales` WHERE product_id=1003");

while($mas = mysqli_fetch_array($sql)){
       echo    $OutcomeId=$mas[0];
echo "</br>";
        echo   $OutcomeBarCode=$mas[2];
echo "</br>";
        echo   $OutcomeQuantity=$mas[6]; 

/* - Start code */
if ($OutcomeQuantity > 0) {
$sql1 = mysqli_query($db,"SELECT * FROM `product_purchases` WHERE qty>0 and product_id=1003 order by id asc");
$mas1 = mysqli_fetch_assoc($sql1);

while($mas1 = mysqli_fetch_array($sql1)){
    //filing stock_outcomes_fifo 

        echo $IncomeId = $mas1[0];
        echo "</br>";
        echo $IncomeQuantity = $mas1[6];
        echo "</br>";
       echo $IncomeUnitPrice = $mas1[9];
       echo "</br>";
        echo $IncomeLeftv = $mas[6];

         $OutcomePart = min($OutcomeQuantity, $IncomeLeftv);
        $FifoLeftv = $IncomeQuantity - $OutcomeQuantity; 
        $FifoCost = $IncomeUnitPrice * $OutcomePart;

         $ins=mysql_query("INSERT INTO `stock_outcomes_fifo` (BarCode,IncomeId,OutcomeId,OutcomePart,UnitPrice,Leftv,Cost) VALUES ($OutcomeBarCode, $IncomeId, $OutcomeId, $OutcomePart, $IncomeUnitPrice, $FifoLeftv, $FifoCost)");      

        $upd = mysqli_query($db,"UPDATE `product_purchases` SET qty = ".$FifoLeftv." WHERE Id = ".$IncomeId."");

        $OutcomeQuantity -= $OutcomePart;
        if ($OutcomeQuantity <= 0) break;

    // $OutcomeCostQuery =mysqli_query($db, "select sum(Cost) as summ from stock_outcomes_fifo where OutcomeId = ".$OutcomeId."");
    // $OutcomeCost = mysql_query($OutcomeCostQuery);
    // $OutcomeCostResult = mysql_fetch_array($OutcomeCost);        
    // mysql_query("UPDATE `stock_outcomes` SET Cost = ".$OutcomeCostResult["summ"]." WHERE Id = ".$OutcomeId."");
 /* - Finish code */
Updated 21-Oct-22 2:59am
Richard Deeming 21-Oct-22 8:12am    
The What I have tried box is where you show us the code you've tried, and explain precisely where you are stuck.

Copying the question into that box tells us you've tried precisely nothing.
Member 15805385 21-Oct-22 8:19am    
i am trying many codes but no result.. Can you please help me. Can u send me code.
Richard MacCutchan 21-Oct-22 8:20am    
You first need to check that you have enough stock for that product to fulfil the request. That means reading all rows for the product and getting the total. You then need to subtract the amounts from each item until you have taken the total stock. In this case it will leave one row with zero stock.
Member 15805385 21-Oct-22 8:30am    
can you write a code?
Richard MacCutchan 21-Oct-22 8:33am    
No, because I do not have enough knowledge of your system. I have given you the logic to do it so it should not be too difficult for you to write and test.

1 solution

Can u send me code.
can you write a code?
That isn't what this site is about.
While we are more than willing to help those that are stuck, that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for us to do it all for you.

So we need you to do the work, and we will help you when you get stuck. That doesn't mean we will give you a step by step solution you can hand in!
Start by explaining where you are at the moment, and what the next step in the process is. Then tell us what you have tried to get that next step working, and what happened when you did.

If you are having problems getting started at all, then this may help: How to Write Code to Solve a Problem, A Beginner's Guide[^]
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900