how to write code in order to update stock after we sell a product

Question

I have written following code for my sales_invoice.php

           if(isset($_POST['submit1'])){

// $cat_array = $_POST['category_name'];

// $qua_array = $_POST['quantity'];

 //$mrp_array = $_POST['mrp'];

 //$vat_array = $_POST['vat'];

// print_r($_POST);

if(!empty($_POST['item_name']) && is_array($_POST['item_name'])){

    $name_array =$_POST['item_name'];
    //print_r($name_array);
 for($j=0; $j< count($name_array);$j++){

$name = mysql_real_escape_string($name_array[$j]);
//echo $name;
//$n = explode('/',$name);

 $sql = "select quantity from purchase_stock where item_name = '$name'";  
// echo $sql;

$res = mysql_query($sql) or die(mysql_error());

$num_rows = mysql_num_rows($res);

if($num_rows <=0){

echo "<b>not in stock</b>";

exit();

}
else

continue;

}
}
 $sql = "insert into material_inv set order_date='$new_date', due_date='$new_date1', dealer='".$_POST['dealer']."', customer='".$_POST['customer']."'";

// echo $sql;



 $res = mysql_query($sql) or die(mysql_error());

 if($res == true){

echo "sales invoice created";
 }
 else
 {
 echo "error";
 }
 $myid = mysql_insert_id();

 $sales_id = $myid;
 //display_item();

// print_r($_POST);

if (!empty($_POST['category_name']) && !empty($_POST['item_name']) && !empty($_POST['quantity']) && !empty($_POST['mrp']) && !empty($_POST['vat']) &&
   is_array($_POST['category_name']) && is_array($_POST['item_name']) && is_array($_POST['quantity']) && is_array($_POST['mrp']) && is_array($_POST['vat'])){


  // echo 'start';
 $name_array = $_POST['item_name'];

 $cat_array = $_POST['category_name'];
 $qua_array = $_POST['quantity'];
 $mrp_array = $_POST['mrp'];
 $vat_array = $_POST['vat'];
 //print_r($mrp_array);

  for ($i = 0; $i < count($name_array); $i++) {

        $name = mysql_real_escape_string($name_array[$i]);

        $cat = mysql_real_escape_string($cat_array[$i]);

        $q = mysql_real_escape_string($qua_array[$i]);

        $mrp = mysql_real_escape_string($mrp_array[$i]);

        //echo $mrp;

        $vat = mysql_real_escape_string($vat_array[$i]);

       // mysql_query("INSERT INTO users (name, age) VALUES ('$name', '$age')");

      $sql ="Insert into material_items SET category_name='$cat', item_name='$name', quantity='$q',
       mrp='$mrp', vat='$vat', inv_id ='$sales_id'";

// echo $sql;

       $res = mysql_query($sql) or die(mysql_error());

       $sql1 = "Update stock set quantity=quantity -'$q' where item_name='$name'";
       $res1 = mysql_query($sql1) or die(mysql_error());
       echo "stock updated and invoice created";
    }  


} 
 } 

I have first checked if item is present in purchase_stock table. (items are inserted in purchase_stock table after a purchase order is created). Then i insert in the sales table quantity which is sold. And then I write query to update stock table.

My question is do I write a insert query for stock table if no items are initially present there? Is it logically correct? Or should I keep same stock table to update quantity and items after purchase and sales transactions?

Answer 1

Here is an example of a stored procedure syntax

DELIMITER //
CREATE PROCEDURE GetProduct(IN param_ProductID INT)
    BEGIN
        SELECT * 
        FROM Products
        WHERE ProductID = param_ProductID;

    END //
DELIMITER ;

and this is how you call it from your php:

$mysqli->query("CALL GetProduct(".$param_ProductID.")");

regarding your question about inserting the the product, you need to perform an UPSERT (insert if not exists and update if exists) here is an example how to do it: PHP / MySQL : Insert if doesnt exist else update

shimon :)