Query Doesn't work correctly in php using mysqli

Question

 **DELETE.php**

<?php
include("db.php");
$id = $_GET['id'];
$check=mysqli_query($conn,"SELECT * FROM details WHERE id='$id'");
while($row = mysqli_fetch_array($check))
{
   $sub=$row['sub'];
   $quan=$row['quan'];
}
$check1=mysqli_query($conn,"SELECT * FROM instock WHERE s_brand=$sub");
while($row1 = mysqli_fetch_array($check1))
{
    $qty1=$row1['qty'];
}
$check2=mysqli_query($conn,"SELECT * FROM outstock WHERE s_brand=$sub");
while($row2 = mysqli_fetch_array($check2))
{
    $qty2=$row2['qty'];
}
$test2=mysqli_query($conn,"UPDATE instock SET qty =$qty1+$quan WHERE 
s_brand='$sub'");
$test3=mysqli_query($conn,"UPDATE  outstock SET qty =$quan-$qty2 WHERE 
s_brand='$sub'");
$result = mysqli_query($conn, "DELETE FROM details WHERE id=$id");
header("Location:add_sale.php");
?>

This is my delete.php file deleting the request is working correctly. But two update requests do not work correctly. It just installs qty from $ quan. I need to immediately Thank you in advance.

Answer 1

In your second and third SELECT statements, I think you may need to add single quotes around $ sub, so they will be:

$check1=mysqli_query($conn,"SELECT * FROM instock WHERE s_brand='$sub'");

and

$check2=mysqli_query($conn,"SELECT * FROM outstock WHERE s_brand='$sub'");

Answer 2

You can do all the updates in one request without SELECTjoining the requests.

$update_stmt = mysqli_prepare($conn, "
    UPDATE details AS d
    JOIN outstock AS o ON o.brand = d.sub
    JOIN instock AS i ON i.brand = d.sub
    SET o.qty = o.qty - d.quan,
        i.qty = i.qty + d.quan
    WHERE d.id = ?") or die(mysqli_error($conn));
mysqli_stmt_bind_param($update_stmt, "i", $id);
mysqli_stmt_execute($update_stmt);
$delete_stmt = mysqli_prepare($conn, "DELETE FROM details WHERE id = ?");
mysqli_stmt_bind_param($delete_stmt, "i", $id);
mysqli_stmt_execute($delete_stmt);