Question

I am making a chatbox IM.
I have a table where I want to update if the user closes the window, meaning he has viewed the messages his friend has sent him.
When this happens, each conversation has a unique id so I find messages with that id and set the viewed column equal to the users name.

However if the other user also closes the window I would like to change the viewed column to both for all the messages so none of those messages pops up for either user. (by default the viewed column is equal to 0).
What I want is a query that just finds all the messages with the convo_id and if that message is already viewed by another user meaning viewed!=0 then it is set to both else it is set to the user closing the box.

我有一个查询，但它不起作用，因为我没有完全理解语法。

只是想知道是否有人能纠正它或提出一些建议

$sql = "UPDATE chatbox IF (viewed!=?) THEN
        SET viewed=? WHERE convo_id=?
        ELSE 
        SET viewed=? WHERE convo_id=?";
$stmt18 = $conn->prepare($sql);
$result = $stmt18->execute(array(0,$receiver,$convo_id, both ,$convo_id));

Answer 1

您将需要使用case语句：

update catbox
set viewed = case when viewed = ? THEN ? else ? end
where convo_id = ?

Answer 2

我建议您创建第二个表（也许称为视图）来记录这些信息。当关闭事件发生时，在表中插入一行，其中包含消息id和用户名。

这样可以简化查询，避免在消息表中创建混淆的数据。

友情链接