How do I substract values from product table and order table?

orders table

products table

The pictures are order and product table. I am trying to change my stock quantity of products where admin confirm the order of the customer and the product quantity will change according to the quantity added from the order table.

What I have tried:

I tried the code as below but there are no changes. Nothing works.

function upOrder(){
global $db;
    $uid = $_SESSION['user_id'];
    $oid = intval($_GET['oid']);
    $pid = intval($_GET['pid']);
    $status = $_POST['status'];

            $sql=mysqli_query($db,"update orders set payment_status='$status', confirmDate = CURRENT_TIMESTAMP where id='$oid'");

            if($status == 'Completed'){

            $result =mysqli_query($db,"SELECT productQty FROM products WHERE id = '$pid'");
            $query =mysqli_query($db,"SELECT * FROM orders WHERE id = '$oid' AND user_id = '$uid' AND product_id = '$pid'");

            $productRow = $result->fetch_assoc();
            $orderRow = $query->fetch_assoc();

            $newqty = $productRow['productQty'] - $orderRow['quantity'];
            $wow =mysqli_query($db,"UPDATE products SET productQty = '$newqty' WHERE id = '$pid'");

            if($wow){

                echo "alert('Order updated sucessfully...');";

            }else{

                echo "alert('Failed!');";

            }
            }

}

Answer

I’ve tried your code in my local server creating a database, but setting some variables

$uid = 41;
$oid = 165;
$pid = 38;
$status = 'Completed';

and the code is working just fine, so I’ve tried some other values to reproduce your error, so changing the uid:

$uid = 411;
$oid = 165;
$pid = 38;
$status = 'Completed';

The order is updated but the product doesn’t.

This is because in the line

$query =mysqli_query($db,"SELECT * FROM orders WHERE id = '$oid' AND user_id = '$uid' AND product_id = '$pid'");

You are filtering the order by user_id, but the order may not have been created from the registered user.

You can verify this adding the following dump in your function:

var_dump(
    "product:".$productRow['productQty'], 
    "order:".$orderRow['quantity'], 
    "result:".($productRow['productQty'] - $orderRow['quantity'])
);

You expect to receive

string 'product:20' (length=10)
string 'order:1' (length=7)
string 'result:19' (length=9)

but you will receive:

string 'product:20' (length=10)
string 'order:' (length=6)
string 'result:20' (length=9)

Therefore, you must remove the user_id filter to allow the registered user to update the quantity in the product table

But if the user_id isn’t the order’s creator and correspond to the last modifier user, you must update the user_id value in the same statement line when you update the status and confirmation date

Hope this clears your doubts and solves your problem