MYSQL multi query

Question

I'm trying to make this code work but there is something is wrong whit it.

<?php
    $server = "localhost";
    $username = "root";
    $password = "";
    $database = "db";
    $connection = mysqli_connect($server, $username, $password, $database);
    if(mysqli_connect_errno())
    {
        echo("Failed-Connect");
        exit();
    }
    else
    {
        if(mysqli_ping($connection))
        {
            $query =
            "
            SET @a = 5;
            SET @b = 2;
            SELECT @c := col FROM whatever WHERE doesn't matter;
            IF @a > @b THEN
            UPDATE table2 SET col1 = col1 + 1, col2 = @c WHERE somthing = 'thething';
            END IF;
            ";
            $result = mysqli_multi_query($connection, $query);
            if($result)
            {
                echo("Done");
            }
        }
        else
        {
            echo("Failed-Ping");
        }
    }
    mysqli_close($enter code hereconnection);
    ?>

This code has no error but it does not update the row. When I remove the IF statement it works perfectly but I need that IF statement to validate something. Any idea whats wrong? If the code has major problem can you provide me whit an alternative?

Answer 1

A multi query is not equal to a stored routine and a code block like:

IF @a > @b THEN
  UPDATE table2 SET col1 = col1 + 1, col2 = @c WHERE somthing = 'thething';
END IF;

Is only valid in the context of a stored routine. So the given code has an error, and MySQL would have informed you about that if you would have added error checking.

You could write something like that:

UPDATE table2 SET col1 = col1 + 1, col2 = @c WHERE somthing = 'thething' AND (@a > @b);

So the update would only be done if @a > @b and if somthing = 'thething'.