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?

t.niese · Accepted Answer · 2018-03-04 12:45:47Z

2

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'.

edited Mar 4, 2018 at 12:45

answered Mar 4, 2018 at 12:40

t.niese

41k9 gold badges78 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Raymond Nijland Over a year ago

the IF block not only valid within a stored procedure but also in a function or trigger.

t.niese Over a year ago

@RaymondNijland I changed it to stored routine if i'm not wrong this is the common name for all of them.

Collectives™ on Stack Overflow

MYSQL multi query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related