By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,530 Members | 880 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,530 IT Pros & Developers. It's quick & easy.

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

P: 2
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:
Expand|Select|Wrap|Line Numbers
  1. $productId = (int)$_POST['txtItemID'];
  2.     $nwQty = "SELECT Quantity FROM stock WHERE itemCode = .'$productId'";
  3.    // check if the product is already
  4.     $solution = mysql_query($nwQty);
  5.  
  6.    if (mysql_num_rows($solution) == 0) 
  7.    {
  8.         // put the product in table
  9.         $sql = "INSERT INTO Stock(itemCode, Description, typeModel, quantity,SupName)
  10.         VALUES('$_POST[txtItemID]','$_POST[Descr]','$_POST[txtType]','$_POST[txtQty]', '$_POST[supname]')";
  11.  
  12.         $result = mysql_query($sql);
  13.    }
  14.    else 
  15.    {
  16.       // update product quantity in table
  17.       $sql = "UPDATE Stock
  18.               SET Quantity = Quantity + (" .$nwQty .")
  19.               WHERE itemCode = $productId";
  20.  
  21.       $result = mysql_query($sql);
  22.       echo('Record Updated');
  23.    }
  24.  
  25.  
  26.     mysql_close($con);
  27.  
  28. ?>
Dec 8 '11 #1
Share this Question
Share on Google+
4 Replies


Rabbit
Expert Mod 10K+
P: 12,359
What's the problem exactly?
Dec 8 '11 #2

AutumnsDecay
100+
P: 170
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:

Expand|Select|Wrap|Line Numbers
  1. $productId = $_POST['txtItemID'];
  2. $nwQty = "SELECT Quantity FROM stock WHERE itemCode = '$productId'";
  3. // check if the product is already
  4. $solution = mysql_query($nwQty);
  5.  
  6. if (mysql_num_rows($solution) == 0){
  7.     // put the product in table
  8.     $sql = "INSERT INTO Stock(itemCode, Description, typeModel, quantity,SupName)VALUES     
  9.     ('$productId','$_POST[Descr]','$_POST[txtType]','$_POST[txtQty]', '$_POST[supname]')";
  10.  
  11.     $result = mysql_query($sql);
  12. }
  13.  
  14. else {
  15.     // update product quantity in table
  16.     $sql = "UPDATE Stock SET Quantity = '$nwQty' WHERE itemCode = '$productId'";  //##REVISE THIS LINE TO MATCH YOUR NEEDS##
  17.     $result = mysql_query($sql);
  18.     echo('Record Updated');
  19. }
  20.  
  21.  
  22. mysql_close($con);
  23.  
  24. ?> 
  25.  
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
Expand|Select|Wrap|Line Numbers
  1. //##REVISE THIS LINE TO MATCH YOUR NEEDS##
for it match what you'd like.
Dec 8 '11 #3

P: 78
Expand|Select|Wrap|Line Numbers
  1. $productId = (int)$_POST['txtItemID'];
  2. $nwQty = "SELECT Quantity FROM stock WHERE itemCode = .'$productId'";
  3.  
what is the neccessity of using . before ProducetId
instead of this u can directly use

Expand|Select|Wrap|Line Numbers
  1. $nwQty = "SELECT Quantity FROM stock WHERE itemCode = ".$_POST['txtItemID']."";
  2.  
Dec 9 '11 #4

P: 2
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.
Dec 14 '11 #5

Post your reply

Sign in to post your reply or Sign up for a free account.