insert...on duplicate key update does not work with php, but works directly on MySQL

Question

$sql = "SET @tag_name = '$tag_value',
    @tag_link = '$tag_link',
    @user_value = '$user_value';
INSERT INTO urls_unis
    (tag_name, tag_link, user_data)
VALUES
    (@tag_name, @tag_link, @user_value)
ON DUPLICATE KEY UPDATE
    tag_name = @tag_name,
    tag_link = @tag_link,
    user_data = @user_value;
    ";

    if(mysqli_query($link, $sql)){
        echo "Records inserted successfully.";
    } else{
        echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
    }
    }

The above code is returning this:

ERROR: Could not able to execute
SET @tag_name = 'View history', @tag_link = 'zNIL', @user_value = '/w/index.php?title=Non-volatile_random-access_memory&action=history'; INSERT INTO urls_unis (tag_name, tag_link, user_data) VALUES (@tag_name, @tag_link, @user_value) ON DUPLICATE KEY UPDATE tag_name = @tag_name, tag_link = @tag_link, user_data = @user_value; .

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO urls_unis (tag_name, tag_link, user_data) VALUES (@tag_name,' at line 4

When I copy and paste the mysql query to phpmyadmin to execute it, it works fine. No errors were returned.

How come, and how can I solve this?

Answer 1

You can't run multiple querylines in a single query. You need to run them separately or use mysqli_multi_query(). You can avoid all that by just using your variables, without going through the route of setting the MySQL variables first.

Better yet, you would use a prepared statement instead of injecting your variables directly into the query. Because you're using the values twice, and that MySQLi doesn't support named placeholders, we have to bind each variable twice.

$sql = "INSERT INTO urls_unis (tag_name, tag_link, user_data)
    VALUES
        (?, ?, ?)
    ON DUPLICATE KEY UPDATE
        tag_name = ?,
        tag_link = ?,
        user_data = ?;";

$stmt = $link->prepare($sql);
$stmt->bind_param("ssssss", $tag_value, $tag_link, $user_value, 
                            $tag_value, $tag_link, $user_value);
if ($stmt->execute()){
    echo "Records inserted successfully.";
} else{
    echo "ERROR: Could not able to execute $sql<br />";
    echo $stmt->error;
}
$stmt->close();

• When should I use prepared statements?