468,117 Members | 1,463 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,117 developers. It's quick & easy.

Subtract quantity from another table

1 Bit
I am making a simple Point of Sale system for personal use and I have two table namely: sales and items. Items to be sold are based on the list of items in the items table. Below is the insert code to the sales table.

Expand|Select|Wrap|Line Numbers
  1. if(isset($_POST['sales']))
  2.       {
  3.         $id        =    $_SESSION['posid'];
  4.  
  5.             $sql    = "    INSERT
  6.                         INTO sales (uid, itemname, category, price, quantity, amount, paid, changeP, cashier, salesDate)
  7.                         VALUES        (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  8.  
  9.             if($stmt = mysqli_prepare($con, $sql))
  10.                 {
  11.                 mysqli_stmt_bind_param($stmt, "isssssssss", $id, $itemname, $category, $price, $quantity, $amount, $paid, $changeP, $cashier, $salesDate);
  12.  
  13.                 $itemname    = mysqli_real_escape_string($con, $_POST['itemname']);
  14.                 $category    = mysqli_real_escape_string($con, $_POST['category']);
  15.                 $price        = mysqli_real_escape_string($con, $_POST['price']);
  16.                 $quantity    = mysqli_real_escape_string($con, $_POST['quantity']);
  17.                 $amount        = mysqli_real_escape_string($con, $_POST['amount']);
  18.                 $paid        = mysqli_real_escape_string($con, $_POST['paid']);
  19.                 $changeP    = mysqli_real_escape_string($con, $_POST['changeP']);
  20.                 $cashier      = mysqli_real_escape_string($con, $_POST['cashier']);
  21.                 $salesDate  = mysqli_real_escape_string($con, $_POST['salesDate']);
  22.  
  23.               if(mysqli_stmt_execute($stmt))
  24.                 {
  25.                     echo "<script type='text/javascript'>alert('Payment was successfully processed.');
  26.                             </script>";
  27.                     echo "<script>window.location.href='pos.php'</script>";
  28.                 } 
  29.                     else
  30.                     {
  31.                         echo "<script type='text/javascript'>alert('Oops! Something went wrong. Please try again.');
  32.                                 </script>";
  33.                         echo "<script>window.location.href='pos.php'</script>";
  34.                     }
  35.  
  36.                 }
  37.  
  38.             mysqli_stmt_close($stmt);    // Close statement
  39.  
  40.     mysqli_close($con);        // Close connection
  41.  }
  42.  
Now, I want as I insert a purchased item in the sales table, the quantity in the items table to reduce by the number of items being purchased.
I really don't know how to go round this, please help.
Feb 24 '21 #1
1 1821
bakertaylor28
38 32bit
After writing to the sales table, You need to store quantity of items to be reduced as a php variable, and then make a separate SQL call to the items table using UPDATE:
Expand|Select|Wrap|Line Numbers
  1. UPDATE sales 
  2. SET columnname = foo;
  3. $quantity = foo;
  4. UPDATE items
  5. SET columnname = $quantity; 
  6.  
Feb 25 '21 #2

Post your reply

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

Similar topics

2 posts views Thread by terence.parker | last post: by
3 posts views Thread by Shannan Casteel via AccessMonster.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.