Balance quantity update

[anita_86]

Hi again!
I have two interrelated tables,purchase_order & purchase_order_data.
purchase_order table contains info like supplier name,po no,gr_status etc, whereas purchase_order_data table contains values that are inserted through dynamic text fields, like qty, price etc.
I want to run the following query so as to make gr_status=1 if all the quantities in dynamic rows are received in goods received note.

$gr_status=mysql_query("UPDATE  purchase_order SET gr_status=1 WHERE po_no='".$po_no."'"); 

But the problem here is, if this query finds even one quantity received totally, it updates the status as 1.

Can anyone guide me how to check that all the quantities with the purchase order are received or not??

I hope I sound clear

[guido2004]

$gr_status=mysql_query("UPDATE  purchase_order SET gr_status=1 WHERE po_no='".$po_no."'"); 

But the problem here is, if this query finds even one quantity received totally, it updates the status as 1.

There is no link with quantity at all in this query?

[anita_86]

Sorry for that.The query posted above is just piece of code.
Here is a bit of code that i am using in this regard.Dont know if it is correct way to do this.

//query to select data from purchase_order_data which has po_no=$_POST['po_no'];
            
$balance=mysql_query("SELECT * FROM purchase_order_data WHERE po_no='".$po_no."'");
            while($row=mysql_fetch_array($balance)){
            $row_qty=$row['qty'];
            $ps_qty=$qty; //ie actual quantity received in grn.
            $bal_qty=($row_qty)-($ps_qty);
            $update=mysql_query("UPDATE purchase_order_data SET balance_qty='".$bal_qty."' WHERE po_no='".$po_no."' AND item_name='".$item_name."'");
            if($bal_qty==0){
            $bal_update=mysql_query("UPDATE purchase_order_data SET bal_status=1 WHERE po_no='".$po_no."' AND item_name='".$item_name."'");

//update gr_status as 1 if no pending gr found in purchase order data

$gr_query=mysql_query("SELECT * FROM purchase_order_data WHERE bal_status=1 AND po_no='".$po_no."'");
while($gr_fetch=mysql_fetch_array($gr_query))
        {
$gr_status=mysql_query("UPDATE  purchase_order SET gr_status=1 WHERE po_no='".$po_no."'");
                            } 

[guido2004]

1) You get all rows from purchase_order_data with a certain po number. And then in the loop you update only the rows with that po number, and a certain item name. But where does that item name come from? Is it always the same? Does po number + item name uniquelly identify a row in purchase_order_data?

2) To update gr status if no pending gr is found (I guess that means if all rows in purchase_order_data for that po number have bal_status = 1?), change your gr_query to select all rows with bal_status = 0. If none are found, do the update of gr_status.

[anita_86]

Yes po number + item name uniquely identify a row in purchase_order_data & item names are always different depending upon the purchase order.I changed the query as follows:

//query to select data from purchase_order_data which has po_no=$_POST['po_no'];
            $balance=mysql_query("SELECT * FROM purchase_order_data WHERE po_no='".$po_no."'");
            while($row=mysql_fetch_array($balance)){
            $row_qty=$row['qty'];
            $ps_qty=$qty; //ie actual quantity received in grn.
            $bal_qty=($row_qty)-($ps_qty);
            $update=mysql_query("UPDATE purchase_order_data SET balance_qty='".$bal_qty."' WHERE po_no='".$po_no."' AND item_name='".$item_name."'");
            if($bal_qty==0){
            $bal_update=mysql_query("UPDATE purchase_order_data SET bal_status=1 WHERE po_no='".$po_no."' AND item_name='".$item_name."'");
                
                
                //update gr_status as 1 if no pending gr found in purchase order data
                
                $gr_query=mysql_query("SELECT * FROM purchase_order_data WHERE bal_status=0 AND po_no='".$po_no."'");
                            if(mysql_num_rows($gr_query)==0){
                                while($gr_fetch=mysql_fetch_array($gr_query)){
                                    if($gr_fetch==0){
                                        $gr_status=mysql_query("UPDATE  purchase_order SET gr_status=1 WHERE po_no='".$po_no."'");
                    
                                                    }
                                                }
                                            } 

And now I found that the quantities are not updating if I make GRN 2nd time with same PO No.Means if :
PO no. 1 is, item misc,qty 100.
GRN no. 1 of po no 1 is item misc, qty 80.
GRN no. 2 of po no 1 is item misc, qty 20.
It still shows balance qty as 80 in purchase_order_data.

[guido2004]

$gr_query=mysql_query("SELECT * FROM purchase_order_data WHERE bal_status=0 AND po_no='".$po_no."'");
if(mysql_num_rows($gr_query)==0){                                
  while($gr_fetch=mysql_fetch_array($gr_query)){                                    
    if($gr_fetch==0){                                        
      $gr_status=mysql_query("UPDATE  purchase_order SET gr_status=1 WHERE po_no='".$po_no."'");                                                                        
    }                                                
  }                                            
}

Does this code actually work? If you don't extract any rows, that while loop should never be entered. Just get rid of the red lines.

I don't know about the '2nd GRN'. You have a variable $qty containing the GRN quantity, but I have no idea where it comes from.

[anita_86]

Whoa that was very stupid.
PO no. 1 is, item misc,qty 100.
GRN no. 1 of po no 1 is item misc, qty 80. //ie 100-80=20
GRN no. 2 of po no 1 is item misc, qty 20. //ie 100-20=80
so updated balance qty showed 80 again.i made changes accordingly.but still it is not updating gr_status in purchase_order table

[anita_86]

yuhu!!!! i cant tell u how much this help of yours mean to me.it is working perfectly now.
thx a ton for your valuable help.