如何更改此SQL以执行以下操作:


如果用户不在表中,请添加它们。 [这部分工作]
如果用户在表AND favourite_active = 0中,则将其更改为favourite_active = 1
如果用户在表AND favourite_active = 1中,请将其更改为favourite_active = 0


我需要添加我认为的WHERE子句。

$result = mysql_query("SELECT * FROM tbl_favourites WHERE (user_id = '$who' AND favourite_id = '$usernum' AND favourite_active = '1')");

// if user does not exist in favourites, add them
    if(mysql_num_rows($result) == 0)
    {
         mysql_query("INSERT INTO tbl_favourites (user_id, favourite_id, favourite_active) VALUES ('$who', '$usernum', '1')");
        echo"You have added this user as a favourite";
    }

// if user does exist and favourite_active = 1 change to 0
    else {
        mysql_query("UPDATE tbl_favourites SET favourite_active='0' WHERE user_id='$who' AND favourite_id='$usernum'");
        echo"You have removed this user as a favourite";
    }

// if user does NOT exist and favourite_active = 0 change to 1
    else {
        mysql_query("UPDATE tbl_favourites SET favourite_active='1' WHERE user_id='$who' AND favourite_id='$usernum'");
        echo"You have removed this user as a favourite";
    }

最佳答案

IF中不能有两个else子句。而且您不能在两个单独的查询中执行UPDATE,因为第二个查询只会撤消第一个查询。使用以下查询:

UPDATE tbl_favourites SET favourite_active = NOT favourite_active WHERE user_id = '$who' and `favourite_id = '$usernum'


我也不确定您所说的第一部分是否真的有效。您的SELECT查询专门针对favourite_active = 1。如果该用户已经存在favourite_active = 0,则尝试INSERT他们。那真的是你想要的吗?也许您应该删除AND favourite_active = '1'

如果user_idfavourite_id在表上形成唯一索引,则可以在单个查询中完成全部操作:

INSERT INTO tbl_favourites (user_id, favourite_id, favourite_active)
VALUES ('$who', '$usernum', '1')
ON DUPLICATE KEY UPDATE favourite_active = NOT favourite_active

关于mysql - SQL部分有效,但需要一些WHERE子句-我认为,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/18572068/

10-12 12:52
查看更多