How to update stock quantity using MySQL in PHP

Question

All stock saved already in stock table. But how can I update stock quantity when I sell my product? I want to update my stock table quantity row. I'm using MySQL.

table name stock:

--------------------------------------------------------------------
    id|username | date      | item     | quantity|  amount
------------------------------------------------------------------
    1 |xyz      |2013-10-09 | computer |25       | 25.00
-----------------------------------------------------------------

and this is sale table:

--------------------------------------------------------------------
    id|username | date      | item     | quantity|  amount
------------------------------------------------------------------
    1 |xyz      |2013-10-09 | computer |25       | 25.00
-----------------------------------------------------------------

and this is sale.php page. When I sell a product this page save a record in sale table but i want to update stock table quantity row:

 <?php
/* 
 NEW.PHP
 Allows user to create a new entry in the database
*/

 // creates the new record form
 // since this form is used multiple times in this file, I have made it a function that is easily reusable
 function renderForm($date ,$username,$item,$quantity,$amount, $error)

 {
 ?>
 <form id="searchform" action="" method="post" enctype="multipart/form-data">
  <div align="center">
 <fieldset>

   <div align="center">
     <legend align="center" >Stock!</legend>
   </div>
   <div class="fieldset">
     <p>
   <label class="field" for="date">Date: </label>

       <input name="date" type="text" class="tcal" value="<?php echo date("Y-m-d");; ?>" size="30"/>
         </p>
   <p>
     <label class="field" for="username">User Name : </label>

     <input name="username" type="text"  id="username"  value="<?php echo $username; ?>" size="30"/>
   </p>
   <p>
     <label class="field" for="item">Item: </label>

     <input name="item" type="text"  value="<?php echo $item; ?>" size="30"/>
   </p>


       <p>
       <label class="field" >Quantity :</label>
       <input  name="quantity" type="text" value="<?php echo $quantity; ?>"  size="30"/>
     </p> 
       <p>
       <label class="field" >Amount :</label>
       <input  name="amount" type="text" value="<?php echo $amount; ?>"  size="30"/>
     </p> 
  </div>
 </fieldset>
   <p align="center" class="required style3">Please Fill The Complete Form </p>
   <div align="center">
     <input name="submit" type="submit" class="style1" value="Submit">

   </div>
 </form> 


 <?php 
 // if there are any errors, display them
 if ($error != '')
 {
 echo '<div style="padding:4px; border:1px solid red; color:red;">'.$error.'</div>';
 }
 ?> 



 <?php 
 }




 // connect to the database
 include('connect-db.php');

 // check if the form has been submitted. If it has, start to process the form and save it to the database
 if (isset($_POST['submit']))
 { 
 // get form data, making sure it is valid
 $date = mysql_real_escape_string(htmlspecialchars($_POST['date']));
 $username = mysql_real_escape_string(htmlspecialchars($_POST['username']));
 $item = mysql_real_escape_string(htmlspecialchars($_POST['item']));
 $quantity = mysql_real_escape_string(htmlspecialchars($_POST['quantity']));
 $amount = mysql_real_escape_string(htmlspecialchars($_POST['amount']));



 // check to make sure both fields are entered
 if ($date == '' || $quantity == '')
 {
 // generate error message
 $error = 'ERROR: Please fill in all required fields!';

 // if either field is blank, display the form again
 renderForm($date ,$username,$item,$quantity,$amount,  $error);

 }
 else
 {
 // save the data to the database
  mysql_query("INSERT  sale SET date='$date', username='$username',item='$item',quantity='$quantity',amount='$amount'")
 or die(mysql_error()); 
 echo "<center>Stock Enter Complete!</center>";
 // once saved, redirect back to the view page

 }
 }
 else
 // if the form hasn't been submitted, display the form
 {
 renderForm('','','','','','','','','');
 }

         ?>

Add another SQL query to decrease your stock (this will be an update, and will probably do `UPDATE stock SET quantity = quantity - $quantity WHERE ...`. Can you give it a go and edit that attempt into your question? — halfer, Oct 09 '13 at 12:54

score 2 · Answer 1 · edited Oct 09 '13 at 15:10

2

You will store stock id in sale table and get stock id while selling a stock at that time to update stock table:

<?php
$stockId="Here get stock id from sale table";
$qty="Here get qty from sale table";
$query="UPDATE stocktable SET quantity='".$qty."' WHERE id='".$stockId."'";
 ?>

edited Oct 09 '13 at 15:10

halfer

19,824
17
99
186

answered Oct 09 '13 at 13:00

Punitha

152
1
12

stockid in sale table can get the values from db using select query – Punitha Oct 09 '13 at 13:15

score 1 · Answer 2 · answered Oct 09 '13 at 12:55

1

use UPDATE query as

UPDATE stock set quantity = quantity - '$quantity' WHERE id = '$id'

answered Oct 09 '13 at 12:55

PravinS

2,640
3
21
25

i update like this but its showing error mysql_query("INSERT sale SET date='$date', username='$username',item='$item',quantity='$quantity',amount='$amount'") mysql_query("UPDATE stock set quantity = quantity - '$quantity' WHERE id = '$id'") – user2468472 Oct 09 '13 at 12:59
Parse error: syntax error, unexpected T_STRING in sale.php on line mysql_query("UPDATE stock set quantity = quantity - '$quantity' WHERE id = '$id'") – user2468472 Oct 09 '13 at 13:00
try this mysql_query("UPDATE stock set quantity = quantity-".$quantity." WHERE id = '".$id."'"); – PravinS Oct 09 '13 at 13:05
have you assigned value to variable $id or you can use "WHERE item = '".$item."'" in where condition – PravinS Oct 09 '13 at 13:16
see my code mysql_query("INSERT sale SET date='$date', username='$username',item='$item',quantity='$quantity',amount='$amount'") mysql_query("UPDATE stock set quantity = quantity-".$quantity." WHERE id = '".$id."'"); – user2468472 Oct 09 '13 at 13:21
actually it should work, echo the update query and execute it in phpmyadmin – PravinS Oct 09 '13 at 13:49
so how can i fix this – user2468472 Oct 09 '13 at 13:58
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/38893/discussion-between-user2468472-and-pravins) – user2468472 Oct 09 '13 at 13:59

score 0 · Answer 3 · edited Oct 14 '18 at 15:27

0

You will need to add quantityand onstock into your database, then do this:

$update=UPDATE table SET onstock=onstock-'$quantity' WHERE id='$id';
$result=mysqli_query($conn,$update);

edited Oct 14 '18 at 15:27

LundinCast

9,412
4
36
48

answered Oct 14 '18 at 12:37

user10502643

1

score 0 · Answer 4 · answered May 12 '19 at 10:21

I use to enter a row in stock upon sale and purchase both. When I make a purchase I put the quantity a positive number , with all other related fields like product I'd, purchaseid and when I sell I put a row with quantity as a negative number, that way I always have history. And when get stock list I use the sum when I return stock rows. Upon closing period I wipe out stock to another archive table.

How to update stock quantity using MySQL in PHP

4 Answers4