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."'");
            }
            }


                 }
?>

[anita_86]

I am extremely sorry for unreadable code.
Here is my more readable code:

<?PHP
if(empty($error)){ //if empty error start
$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 to get total value
$total = (isset($_POST['total'][$i]) && !empty($_POST['total'][$i])) ? mysql_real_escape_string($_POST['total'][$i]) : 0;
}// for end to get total value

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 to insert values that are in array
$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 to insert values that are not in array
$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."')
");

//update grn status as 1 if bill is made
//honestly dont know how to do this if all the goods are received
$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)){ //while start for balance quantity
$row_qty=$row['qty'];//actual qty as per goods received note
$ps_qty=$qty;//qty received i.e.$_POST['qty'];
$bal_qty=($row_qty)-($ps_qty);
//this query works only for 1 item and for other quantities it updates 0
$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){//if start for balance qty==0 i.e. all goods are received
$bal_update=mysql_query("UPDATE goods_received_note_data SET bal_status=1 WHERE gr_no='".$grn_no."' AND item_name='".$item_name."'");
}//if end
}//while end for bal qty
}//if empty error end
?>