How to add or substract quantity then the database quantity will also change?

Question

If I input a number in stock field then click add stock the quantity should increase and update, same as substract.

form and table

code for adding item and initial quantity

<label for="item_code">Item Code</label>

    <select name="code" id="code">
        <?php 
            $query = "SELECT * FROM item_code";
            $select_item_name = mysqli_query($connection, $query);
            while ($row = mysqli_fetch_assoc($select_item_name)) {
            $item_id = $row["item_id"];
            $item_code = $row["item_code"];

            echo "<option value='{$item_id}'>$item_code</option>";
            }
        ?>
        
    </select>
    <br>


<label for="item_name">Item Name</label>
<input type="text" name="item_name"><br>

<label for="item_name">Add Initial Quantity</label>
<input type="number" name="quantity"><br>

<input type="submit" name="add_item" value="Add Item">

code for displaying the table

code picture

My delete function is working

    if(isset($_GET['delete'])) {
  $the_item_management_id = $_GET['delete'];
  $query = "DELETE FROM item_management WHERE item_management_id = {$the_item_management_id}";
  $delete_query = mysqli_query($connection, $query);
  header("Location:inventory_management.php");

}

I'm trying this for add stock

    if(isset($_GET['add_stock'])) {
  $the_item_management_id = $_GET['add_stock'];
  $stock = $_POST["stock"];

  $query = "SELECT item_quantity + $stock AS item_quantity FROM item_management";
  $result = mysqli_query($connection, $query);

}

this is my database table

database

**code for displaying the table** should be rather posted as text, not as a screenshot — HoRn, May 05 '21 at 08:34
Try tp provide a minimal working example and please describe clearly what you want to achieve — HoRn, May 05 '21 at 08:35
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, May 05 '21 at 09:53
Hi Gami, Welcome to SO. As others have mentioned, it would be really useful to have a minimal example with code (formatted as text) and help us understand exactly what you want to do. It will help people to help you! — Liam, May 05 '21 at 12:19

score 0 · Answer 1 · edited May 05 '21 at 09:57

Change the below code

$query = "SELECT item_quantity + $stock AS item_quantity FROM item_management";
$result = mysqli_query($connection, $query);

with

$stmt = $connection->prepare("UPDATE item_management SET item_quantity = @item_quantity := item_quantity + ? LIMIT 1");
$stmt->bind_param('s', $stock);
$stmt->execute();
$query = "SELECT @item_quantity AS item_quantity";
$result = mysqli_query($connection, $query);

But you have to pass an ID also for which item quantity you want to increase. when you have multiple products.

The best option for this kind of functionalities are to create a Stored Procedure and call it in your PHP Code.

CREATE PROCEDURE `return_item_quantity` ()
BEGIN
   UPDATE tbl_user SET
    item_quantity = @item_quantity := item_quantity+'$stock'
   WHERE id='$id' LIMIT 1;
   SELECT @item_quantity AS item_quantity;
END //

I fixed the code, but your stored procedure is still broken. — Dharman, May 05 '21 at 09:58

How to add or substract quantity then the database quantity will also change?

1 Answers1