if else mysql语法

Question

有人能在这个语法上帮我吗？我不知道我做错了什么！

当我尝试执行这段代码时：

IF EXISTS(SELECT * FROM user WHERE user_username = '$user_username') 
THEN
UPDATE user SET user_name='$name',user_profession='$profession',user_address='$address',user_avatar='$NewImageName'
ELSE 
INSERT INTO user (user_name,user_profession,user_address,user_avatar) VALUES ('$name','$profession','$address','$NewImageName';

我得到以下错误：

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 'IF EXISTS(SELECT * FROM user WHERE user_username = '111') THEN UPDATE user SE' at line 1

我不知道这段代码出了什么问题！

Answer 1

您是否尝试过在INSERT INTO...语句后添加END IF？

Answer 2

您需要ON DUPLICATE KEY

INSERT INTO user (user_name,user_profession,user_address,user_avatar)
VALUES ('$name','$profession','$address','$NewImageName'
   ON DUPLICATE KEY
UPDATE user SET user_name='$name',
                user_profession='$profession',
                user_address='$address',
                user_avatar='$NewImageName'

必须将列user_name设置为UNIQUE

如果它不是唯一的，请执行以下操作

ALTER IGNORE TABLE user ADD UNIQUE (user_name);

Answer 3

下面这段代码帮我解决了这个问题：

​

$result = mysql_query("SELECT * FROM user WHERE user_username = '$user_username'");
if( mysql_num_rows($result) > 0) {
    mysql_query("UPDATE user SET user_name='$name',user_profession='$profession',user_address='$address',user_avatar='$NewImageName' WHERE user_username = '$user_username'");
}
else
{
    mysql_query("INSERT INTO user (user_name,user_profession,user_address,user_avatar) VALUES ('$name','$profession','$address','$NewImageName') WHERE user_username = '$user_username'");
}

​