mysqli_multi_query and visiable of result on website

Question

When I'm using mysqli_query, website after submitting the form reloads with visible change, but if I use mysqli_multi_query to change more than one table, page display "No result" (or whatever I have set to display if there's no result), but if I then reload website manually, everything has been changed, which means the records are updated in the database. I need to change the page and get back again or refresh to see the result. For example:

$query = "UPDATE tools SET quantity=quantity+$quantity WHERE id = $tools;";
$query .= "UPDATE tools SET quantity=quantity-$quantity_edit WHERE id = $tools;";
$query .= "UPDATE rent SET quantity=$quantity_edit WHERE id=$rent_id;";
mysqli_multi_query($db, $query);

If I submit the form with this code, the result will be "No result", but If I reload the page, I will see result e.g. changed value. But if I do this:

$query = "UPDATE tools SET quantity=quantity+$quantity WHERE id = $tools;";
$query2 = "UPDATE tools SET quantity=quantity-$quantity_edit WHERE id = $tools;";
$query3 = "UPDATE rent SET quantity=$quantity_edit WHERE id=$rent_id;";
mysqli_multi_query($db, $query);
mysqli_multi_query($db, $query2);
mysqli_multi_query($db, $query3);

It will work as I expect it, after submitting the form, page will show result.

Answer 1

Don't use mysqli_multi_query(). It is a special function, which has a very narrow use case that doesn't apply in your case.

You must use prepared statements instead.

You have 3 separate queries, so you need to prepare and execute 3 statements. This is the correct way to execute your SQL statements:

$stmt = $db->prepare('UPDATE tools SET quantity=quantity + ? WHERE id = ?');
$stmt->bind_param('ss', $quantity, $tools);
$stmt->execute();

$stmt = $db->prepare('UPDATE tools SET quantity=quantity - ? WHERE id = ?');
$stmt->bind_param('ss', $quantity_edit, $tools);
$stmt->execute();

$stmt = $db->prepare('UPDATE rent SET quantity = ? WHERE id = ?');
$stmt->bind_param('ss', $quantity_edit, $rent_id);
$stmt->execute();