stock updates

[anser316]

In a table i have stock sell by dates.

drug id      sell_by_date    stock

1            2008-04-25        1

1            2008-05-30        2

1            2008-05-40        3

 

if quantity of stock '5' is entered, i want it to go earliest date, delete(decrement) all the stock up to 5, then if theres spare, go to the next date, delete(decrement) the rest, and so on. So in this case, it will go to the first date , delete 1, so theres 4 left, go to next one, delete 2, and then on the next one it will delete 2.

So it should end up

drug id      sell_by_date    stock

1            2008-04-25        0

1            2008-05-30        0

1            2008-05-40        1

 

I am having problems with the code, first in line 75, it times out, probably cuz the loop keeps running, also im not sure once this solved it will still work.

$presc=$_POST[presc_id];

$result=mysql_query("Select * from prescription_items where presc_id=$presc") or die ("Query:<br>result<br>Error:<br>".mysql_error());
while ($row = mysql_fetch_array( $result )){   
$itemquan=$row[drug_quantity];
$itemdrug=$row[drug_id];
$itemtotal=$row[drug_total];

$result2=mysql_query("SELECT * FROM stock_expdates
WHERE drug_id =$itemdrug AND branch_id =1
AND sell_by_date > CURDATE( ) AND STOCK >0
ORDER BY sell_by_date ASC") or die ("Query:<br>result2<br>Error:<br>".mysql_error());  
while ($row2 = mysql_fetch_array( $result2 )){   // get each sellbydate for item

if ($itemquan>0) {  // run while quantity is not 0
$stock=$row2['stock'];
$date=$row2['sell_by_date'];

if ($itemquan>=$stock) {               // if quantity is bigger than stock in sellby date, e.g. quant=5, stock=1
$itemquan=$itemquan-$stock;        //quan=4
$newstock=0;}                      // stock=0, end loop, insert stock below,go back to beginning of loop, with quan=4, repeat,stock=2, change to quan=2,stock=0

elseif ($stock>$itemquan)  {         //with quan=2,stock=3($stock>$itemquan) 
$newstock=$stock-$itemquan;        //stock=1
$itemquan=0;}                     //quan=0, insert below

$result5=mysql_query("UPDATE stock_expdates SET stock='$newstock'
WHERE drug_id=$itemdrug
AND branch_id=1
AND sell_by_date='$date'") or die ("Query:<br>result5 <br>Error:<br>".mysql_error());
}
}      //insert, stock=0, while itemquan>0 repeat
}    //next item (result)

its not deleting the stock