0

I have 3 table

package,item,items

table item use to store item information

--------------------------------------
item id|item name|item stock|item used
--------------------------------------
1       | item1    | 100        |    0
2       | item2    | 100        |    0

table package is use to store group item with same package?

--------------------------
package id | package name
--------------------------
1            |    package 1

table items is use to store item quantity for each package

----------------------------------------------------
items id|item name|items quantity|item id|package id
---------------------------------------------------- 
1        | item1     | 20             |   1    |     1
2        | item2     | 40             |   2    |     1

Then if package 1 selected ,I need to Update the item stock and item used in table item

In PHP SELECT query I try this query :

$sql1 = "SELECT items.items_id, items.item_name, items.items_quantity, 
                item.item_stock, item.item_used 
        FROM items
            INNER JOIN item ON item.item_name = items.item_name
        WHERE items.package_id = '1' ";
$txtquery = mysqli_query($con,$sql1);

while($row = mysqli_fetch_assoc($txtquery)){
    //this for single item only
    $items_quantity=$row['items_quantity'];
    $item_name=$row['item_name'];
    $sql2="Update item
          SET item_stock=item_stock-'$items_quantity',
              item_used=item_used+'$items_quantity'
          WHERE item_name='$item_name'";
    $query = mysqli_query($con,$sql2);
}

When sql1 query run, will show for this table

----------------------------------------------------
items id|item name|items quantity|item stock|item used
---------------------------------------------------- 
1        | item1     | 20             |   100     |    0
2        | item2     | 40             |   100     |    0

*when update i need get the result like this :

------------------------------------------------------------
items id|item name   |items quantity  |item stock |item used
------------------------------------------------------------ 
1        | item1     | 20             |   80      |    20
2        | item2     | 40             |   60      |    40
item stock = item stock - items quantity
item used = item used + item_quantity

But I no idea for how to update the Item stock and item used with multiple item in the row

xxx
  • 11
  • 1
  • 1
    **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/32391315) – Dharman Oct 26 '22 at 17:32

0 Answers0