decrement??

[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 have already done validation that does not remove if the quantity entered (5) is bigger than the total of sellbydates (6).

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.

 

 

Code:

[/font]
$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 )){            //[color=red]get each item[/color]
echo "<table border=1>";
echo "<tr><th>PID</th><th>DID</th><th>Quantity</th><th>Total</th></tr>";
echo "<tr><td>";
echo $row['presc_id'];
echo "</td><td>";
echo $row['drug_id'];
echo "</td><td>";
echo $row['drug_quantity'];
echo "</td><td>";
echo $row['drug_total'];
echo "</td></tr>";
echo "</table>";

$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());  
echo "<b>$itemdrug</b>";
echo "<table border=1>";
echo "<tr><th>Sell By Date</th><th>Stock</th><th>Drug</th><th>Branch</th></tr>";
while ($row2 = mysql_fetch_array( $result2 )){   // [color=red]get each sellbydate for item[/color]

echo "<tr><td>";
echo $row2['sell_by_date'];
echo "</td><td>";
echo $row2['stock'];
echo "</td><td>";
echo $row2['drug_id'];
echo "</td><td>";
echo $row2['branch_id'];
echo "</td></tr>";}
echo "</table>";

While ($itemquan>0) {  // [color=green]Line 75 runtime error [/color][color=red]run while quantity is not 0[/color]
$result3=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>result3<br>Error:<br>".mysql_error());

while($row3 = mysql_fetch_array( $result3 )) {// [color=red]get each sellbydate for item[/color]

$stock=$row3['stock'];
$date=$row3['sell_by_date'];
if ($itemquan>=$stock) { // [color=red]if quantity is bigger than stock in sellby date, e.g. quant=5, stock=1[/color]
$itemquan=$itemquan-$stock; //[color=red]quan=4[/color]
$newstock=0;}
//[color=red]stock=0,endloop,insert stock below,go back to begin of loop,with quan=4,repeat,stock=2,change to quan=2,stock=0[/color]

elseif ($stock>$itemquan) {  //[color=red]with quan=2,stock=3[/color]
$newstock=$stock-$itemquan; //[color=red]stock=1[/color]
$itemquan=0;}  //[color=red]quan=0, insert below[/color]
else {
echo "error";}
}

$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 ".$newstock."".$itemdrug."".$date."<br>Error:<br>".mysql_error());
}//[color=red]insert, stock=0, while itemquan>0 repeat[/color]

$result4=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>result4<br>Error:<br>".mysql_error());
//[color=red]when itemquan=0, select sellybydates[/color]

echo "<b>$itemdrug</b>";
echo "<table border=1>";
echo "<tr><th>Sell By Date</th><th>Stock</th><th>Drug</th><th>Branch</th></tr>";
while ($row4 = mysql_fetch_array( $result4 )){//[color=red]get values[/color]
echo "<tr><td>";
echo $row4['sell_by_date'];
echo "</td><td>";

[anser316]

can someone please help me, ive been tryin to work this code out for days