PHP MYSQL Error 1064

Question

I can't tell what is wrong with this because I've used similar queries the same way and there isn't an issue. It is giving me the following error:

Multi query failed: (1064) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE stock SET quantity='160' WHERE id='2'' at line 1

If someone could take a look and see if i'm missing something:

$root = $_SERVER['DOCUMENT_ROOT']."/wfo-inv";

require ($root.'/assets/config.php');

$id = $_GET['id'];

$link = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE);

// Check connection
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}

// Escape user inputs for security
$name = mysqli_real_escape_string($link, $_POST['name']);
$type = mysqli_real_escape_string($link, $_POST['type']);
$quantity = mysqli_real_escape_string($link, $_POST['quantity']);
$quantitysold = mysqli_real_escape_string($link, $_POST['quantitysold']);
$price = mysqli_real_escape_string($link, $_POST['price']);
$location = mysqli_real_escape_string($link, $_POST['location']);
$batch = mysqli_real_escape_string($link, $_POST['batch']);
$thc = mysqli_real_escape_string($link, $_POST['thc']);
$cbd = mysqli_real_escape_string($link, $_POST['cbd']);

$quantityfinal = $quantity - $quantitysold;

if($quantityfinal >= "1") {
        $sql = "INSERT INTO outgoing (name, type, quantity, price, location, batch, thc, cbd) VALUES ('$name', '$type', '$quantitysold', '$price', '$location', '$batch', '$thc', '$cbd')";
        $sql .= "UPDATE stock SET quantity='$quantityfinal' WHERE id='$id'";
} else {
        $sql = "INSERT INTO outgoing (name, type, quantity, price, location, batch, thc, cbd) VALUES ('$name', '$type', '$quantitysold', '$price', '$location', '$batch', '$thc', '$cbd')";
        $sql .= "DELETE FROM stock WHERE id='$id'";
}

if (!$link->multi_query($sql)) {
    echo "Multi query failed: (" . $link->errno . ") " . $link->error;
}

do {
    if ($res = $link->store_result()) {
        var_dump($res->fetch_all(MYSQLI_ASSOC));
        $res->free();
        header("Location: ../index.php");
    }
} while ($link->more_results() && $link->next_result());



// close connection
mysqli_close($link);

Answer 1

According to the docs, the queries passed to mysqli::multiquery() need to be concatenated with a semicolon, something like

$sql = "INSERT INTO outgoing (name, type, quantity, price, location, batch, thc, cbd) VALUES ('$name', '$type', '$quantitysold', '$price', '$location', '$batch', '$thc', '$cbd')";
$sql .= "; "; // <- Add this line here
$sql .= "UPDATE stock SET quantity='$quantityfinal' WHERE id='$id'";

(It doesn't have to be on a separate line, of course; I just did that to make it obvious.)