How to update balance quantity in store purchase system

[anita_86]

Hi!!
I have a store purchase management system which manages purchase orders, goods received notes and purchase bills.

problem is, if the quantity in goods received note and purchase order dont match, similarly, if quantity in goods received note and purchase bill dont match,
i have to keep track of the balance goods untill the balance gets 0.

i dont know i am doing it right or not, but i added two fields in my database as balance quantity and status so as to every time grn and pb is made, it will update the quantity and if balance gets 0 status will change to 1.
This is some of the code i am using.but it is working just for first item.similarly i have to show up the values in text boxes so i am fetching data from balance quantity field but that too dosent work at all.
can anyone tell me where i am going wrong??

<?PHP

if(empty($error)){

$supplier_name         = $_POST['supplier_name'];

$grn_no              = $_POST['gr_no'];

$bill_no             = $_POST['bill_no'];

$bill_date          = $_POST['bill_date'];



            for($i = 0, $maxi = count($_POST['total']); $i < $maxi; $i++)  { // for start

                    $total  = (isset($_POST['total'][$i]) && !empty($_POST['total'][$i])) ? mysql_real_escape_string($_POST['total'][$i]) : 0;

                    }



                        if (isset($_POST['qty']) && sizeof($_POST['qty']) > 0)  { //isset name of item start

                                   for($i = 0, $maxi = count($_POST['qty']); $i < $maxi; $i++)  { // for start

                    $qty        = (isset($_POST['qty'][$i]) && !empty($_POST['qty'][$i])) ? mysql_real_escape_string($_POST['qty'][$i]) : 0;

                       $unit         = (isset($_POST['unit'][$i]) && !empty($_POST['unit'][$i])) ? mysql_real_escape_string($_POST['unit'][$i]) : 0;

                    $rate          = (isset($_POST['rate'][$i]) && !empty($_POST['rate'][$i])) ? mysql_real_escape_string($_POST['rate'][$i]) : 0;

                    $unit_1     = (isset($_POST['unit_1'][$i]) && !empty($_POST['unit_1'][$i])) ? mysql_real_escape_string($_POST['unit_1'][$i]) : 0;

                    $amt          = (isset($_POST['amt'][$i]) && !empty($_POST['amt'][$i])) ? mysql_real_escape_string($_POST['amt'][$i]) : 0;

                    $item_name  = (isset($_POST['item_name'][$i]) && !empty($_POST['item_name'][$i])) ? mysql_real_escape_string($_POST['item_name'][$i]) : 0;



            $query_date=mysql_query

            ("INSERT INTO purchase_bill_entry_data

            (item_name,

            qty,

            unit,

            rate,

            unit_1,

            amt,

            bill_no)

            VALUES

            ('".$item_name."',

            '".$qty."',

            '".$unit."',

            '".$rate."',

            '".$unit_1."',

            '".$amt."',

            '".$bill_no."')

            ");

            

        } // for end

        

         } //isset name of item end





         $query=mysql_query

         ("INSERT INTO purchase_bill_entry

         (supplier_name,

         grn_no,

         bill_no,

         bill_date,

         amt,

         total )

         VALUES

         ('".$supplier_name."',

         '".$grn_no."',

         '".$bill_no."',

         '".$bill_date."',

         '".$amt."',

         '".$total."')

          ");

          

        $bill_status=mysql_query("UPDATE goods_received_note SET bill_status=1 WHERE gr_no='".$grn_no."'");

                                

            header("location:".$_SERVER['PHP_SELF']."?action=success");

            $balance=mysql_query("SELECT * FROM goods_received_note_data WHERE gr_no='".$grn_no."'");

            while($row=mysql_fetch_array($balance))

            {

            $row_qty=$row['qty'];

            $ps_qty=$qty;

            $bal_qty=($row_qty)-($ps_qty);

            $update=mysql_query("UPDATE goods_received_note_data SET balance_qty='".$bal_qty."' WHERE gr_no='".$grn_no."' AND item_name='".$item_name."'");

            if($bal_qty==0)

            {

            $bal_update=mysql_query("UPDATE goods_received_note_data SET bal_status=1 WHERE gr_no='".$grn_no."' AND item_name='".$item_name."'");

            }

            }





                 }

?>