php - 如果两个列都不为空，则更新列 a，否则更新列 b，否则不执行任何操作

Question

这是我的表:叫做房间

+---------+---------+-----------+-------------+-------------+-------------+---------+
| room_id | room_no | room_stat | room_name   | player_a_id | player_b_id | turn_of |
+---------+---------+-----------+-------------+-------------+-------------+---------+
|       1 |       1 |         0 | blah        |           0 |           0 |       0 |
|       2 |       5 |         0 | second room |           0 |           0 |       0 |
|       3 |       3 |         0 | 3rd room    |           0 |           0 |       0 |
|       4 |       4 |         0 | 4th room    |           0 |           0 |       0 |
+---------+---------+-----------+-------------+-------------+-------------+---------+

$player_id //contains the id of the player who wants to join

#room 表:

1. 如果 player_a_id !null 和 player_b_id !null 那么什么都不更新；如果
2. player_a_id = null and player_b_id !null 然后更新房间设置
3. player_a_id = $player_id;如果 player_b_id = null 和 player_a_id !null 然后更新房间 set player_b_id = $player_id;

我当前的查询(感谢 JW)(我尝试编辑它但无济于事，我失败了)。

     UPDATE  room
             SET     player_a_id = IF(player_a_id IS NULL OR player_a_id  = 0 AND player_b_id != :chara_id, :chara_id, player_a_id),
                     player_b_id = IF(player_a_id != :chara_id AND player_b_id IS NOT NULL, :chara_id, player_b_id)
             WHERE   room_id  = :room_id

这很好，但是如果两列都为空或 = 0，它会更新两列；我只想更新 1。

编辑:

这是示例结果:player_id 1 加入 room_id 4 后:

+---------+---------+-----------+-------------+-------------+-------------+---------+
| room_id | room_no | room_stat | room_name   | player_a_id | player_b_id | turn_of |
+---------+---------+-----------+-------------+-------------+-------------+---------+
|       1 |       1 |         0 | blah        |           0 |           0 |       0 |
|       2 |       5 |         0 | second room |           0 |           0 |       0 |
|       3 |       3 |         0 | 3rd room    |           0 |           0 |       0 |
|       4 |       4 |         0 | 4th room    |           1 |           1 |       0 |
+---------+---------+-----------+-------------+-------------+-------------+---------+

因为两列都是空的，所以它更新了两列，我只想更新 1 列。

Answer 1

回顾您的逻辑，您想将 player_a_id 更新为 $player_id IF player_a_id = 0 而 player_b_id 不 = 0。

并且您想将 player_b_id 更新为 $player_id IF player_b_id = 0 而 player_a_id 不为 0。

编辑 -- 如果 player_a_id 都等于 0，您还想更新它们。

UPDATE  room
SET     player_a_id = IF(player_a_id=0, :player_id, player_a_id),
    player_b_id = IF(player_a_id!=0 AND player_b_id=0, :player_id, player_b_id)
WHERE   room_id  = :room_id