Updating record's quantity in mysql table if exist otherwise insert a new row.

[phpTeryoh]

Im completely new in php and in the process of trying to develop my project Im getting a problem in writting a code that can increase the quantity of the record by the value inserted in the textbox if the record exists otherwise a new row is added with the information from the form as inserted by the user.


Here's my code, Please help:

$productId = (int)$_POST['txtItemID'];
	$nwQty = "SELECT Quantity FROM stock WHERE itemCode = .'$productId'";
   // check if the product is already
	$solution = mysql_query($nwQty);
	
   if (mysql_num_rows($solution) == 0) 
   {
    	// put the product in table
    	$sql = "INSERT INTO Stock(itemCode, Description, typeModel, quantity,SupName)
		VALUES('$_POST[txtItemID]','$_POST[Descr]','$_POST[txtType]','$_POST[txtQty]', '$_POST[supname]')";
       
	    $result = mysql_query($sql);
   }
   else 
   {
	  // update product quantity in table
      $sql = "UPDATE Stock
              SET Quantity = Quantity + (" .$nwQty .")
              WHERE itemCode = $productId";

      $result = mysql_query($sql);
	  echo('Record Updated');
   }


	mysql_close($con);

?>

[Rabbit]

What's the problem exactly?

[AutumnsDecay]

I don't know why you're using the (int) on line 1, but let's remove it for for now. Also, you have a period '.' in your SQL query on line 2.

Here's what SHOULD work for you:

$productId = $_POST['txtItemID'];
$nwQty = "SELECT Quantity FROM stock WHERE itemCode = '$productId'";
// check if the product is already
$solution = mysql_query($nwQty);

if (mysql_num_rows($solution) == 0){
    // put the product in table
    $sql = "INSERT INTO Stock(itemCode, Description, typeModel, quantity,SupName)VALUES     
    ('$productId','$_POST[Descr]','$_POST[txtType]','$_POST[txtQty]', '$_POST[supname]')";

    $result = mysql_query($sql);
}

else {
    // update product quantity in table
    $sql = "UPDATE Stock SET Quantity = '$nwQty' WHERE itemCode = '$productId'";  //##REVISE THIS LINE TO MATCH YOUR NEEDS##
    $result = mysql_query($sql);
    echo('Record Updated');
}


mysql_close($con);

?>

The only thing I'm not sure of would be in the 'else' statement, where you're setting the new quantity. Your variable '$nwQty' was already set to the Quantity of that item. You originally had, what looked like, Quantity + Quantity, but I doubt that's what you wanted. Just modify the line with the

//##REVISE THIS LINE TO MATCH YOUR NEEDS##

for it match what you'd like.

[Ammu]

$productId = (int)$_POST['txtItemID'];
$nwQty = "SELECT Quantity FROM stock WHERE itemCode = .'$productId'";

what is the neccessity of using . before ProducetId
instead of this u can directly use

$nwQty = "SELECT Quantity FROM stock WHERE itemCode = ".$_POST['txtItemID']."";

[phpTeryoh]

I’m very much grateful coz after making changes to the codes as were suggested by you people it really worked well and help me from all that headache. Thanks a lot Automn and Ammu for thy contributions.