while loops, stock removing when purchasing, logic

[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.

$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 )){
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 )){
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) {
$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 )) {
$stock=$row3['stock'];
$date=$row3['sell_by_date'];
if ($itemquan>=$stock) {
$itemquan=$itemquan-$stock;
$newstock=0;}
elseif ($stock>$itemquan) {
$newstock=$stock-$itemquan;
$itemquan=0;}
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());
}
$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());

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 )){
echo "<tr><td>";
echo $row4['sell_by_date'];
echo "</td><td>";
echo $row4['stock'];
echo "</td><td>";
echo $row4['drug_id'];
echo "</td><td>";
echo $row4['branch_id'];
echo "</td></tr>";}
echo "</table>";
}

 

 

 

[anser316]

line 75 is when "While ($itemquan>0) {"

[anser316]

i have commented my code, explaining what im trying to do, if theres anyway to bold inside code, please tell me if it helps

[anser316]

commented code, sorry 4 too many posts!

$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 )){            //get each item
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 )){   // get each sellbydate for item

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) {  // run while quantity is not 0
$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 )) {// get each sellbydate for item

$stock=$row3['stock'];
$date=$row3['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
$newstock=$stock-$itemquan; //stock=1
$itemquan=0;}  //quan=0, insert below
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());
}//insert, stock=0, while itemquan>0 repeat

$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()); //when itemquan=0, select sellybydates

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 )){//get values
echo "<tr><td>";
echo $row4['sell_by_date'];
echo "</td><td>";
echo $row4['stock'];
echo "</td><td>";
echo $row4['drug_id'];
echo "</td><td>";
echo $row4['branch_id'];
echo "</td></tr>";}
echo "</table>";
}//go beginning, new item