SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict anita_86's Avatar
    Join Date
    Aug 2010
    Location
    Nagpur, India
    Posts
    228
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Balance quantity update

    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.
    PHP Code:
    $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
    It's easy once you know how...

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by anita_86 View Post
    PHP Code:
    $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?

  3. #3
    SitePoint Addict anita_86's Avatar
    Join Date
    Aug 2010
    Location
    Nagpur, India
    Posts
    228
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    PHP Code:
    //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."'");
                                } 
    It's easy once you know how...

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    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.

  5. #5
    SitePoint Addict anita_86's Avatar
    Join Date
    Aug 2010
    Location
    Nagpur, India
    Posts
    228
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    PHP Code:
    //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.
    It's easy once you know how...

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Code:
    $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.

  7. #7
    SitePoint Addict anita_86's Avatar
    Join Date
    Aug 2010
    Location
    Nagpur, India
    Posts
    228
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    It's easy once you know how...

  8. #8
    SitePoint Addict anita_86's Avatar
    Join Date
    Aug 2010
    Location
    Nagpur, India
    Posts
    228
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    It's easy once you know how...


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •