显示消息状态:PHP/MySQL消息系统中的已读和未读(与其他用户不同) [英] Displaying message status: read and unread (differs from other users) in PHP/MySQL messaging system

问题描述

我显示的消息状态不同于每个用户.假设user1将消息发送到user2，然后user1的消息状态设置为read，而user2的消息默认设置为unread. user2单击邮件后，将对其进行更新.

I am displaying the message status that differs from each user. Let's say user1 sends a message to user2, user1's message status then sets to read, while user2's message is set to unread by default. It will be updated after user2 clicks the message.

因此，在这种情况下，user1的消息(来自收件箱)将具有灰色字体，表明该消息已设置为read(因为user1是发送者).另一方面，user2具有粗体字体，指示消息为unread.

So in these scenario, the message of user1 (from the inbox) will have a gray-colored font which indicates that the message is set to read (since user1 is the one who is sending). On the other side, user2 have a bold font that indicates that the message is unread.

这是表格的第一个结构:

Here is the first structure of the table:

message(messageid, fromid, toid, message, timestamp, status)

这里的问题是，如果我将消息状态更新为read，则会影响到另一端(user2).因此，我添加了另一列，其状态设置与user1和user2不同:

The problem here is that if I update the message status to read, it affects the other side (user2). So I add another column that will set the status differently from user1 and user2:

message(messageid, fromid, toid, message, timestamp, from_status, to_status)

在这里，from_status用于fromid，to_status用于toid.但是我在使用这些值显示状态时遇到问题.

Here, from_status is for the fromid and to_status is for toid. But I'm having a problem on how to use these values to display the status.

我第一次尝试时使用的PHP代码如下:

The PHP code of that I use during my first attempt is these:

<?php 
$id = $_SESSION['id'];
$query = mysql_query("SELECT m.* FROM message m
                                  LEFT JOIN message m2 ON (
                                  (m.fromid=m2.fromid AND m.toid=m2.toid) OR
                                  (m.fromid=m2.toid AND m.toid=m2.fromid)
                                  ) AND m.timestamp<m2.timestamp
                                  WHERE (m.fromid='$id' OR m.toid='$id') AND m2.toid IS NULL ORDER BY timestamp DESC");

while ($message = mysql_fetch_array($query)) {
  if ($message['status'] === 'unread') {
    // bold font style will be applied
  }
  else {
    // gray-colored font will be applied
  }
}
?>

(查询从具有最新对话的每个用户中获取每个对话.)

(The query fetches each conversation from every user with the latest conversation.)

这些代码对于主要用户user1正常工作，但会影响另一端，该另一端认为从user2接收的消息设置为read或unread.

These code works fine for the main user, which is user1, but affects the other side, which views that the message received from user2 is set to read instead or unread.

因此，在修改表上执行操作时遇到了一些麻烦，每个用户分别有2个单独的status.我该怎么办?

So, I'm having some trouble on what to do on the modified table, having 2 separate status each for each user. How can I get these done?

推荐答案

@andrewsi注释非常不错，例如，当您有许多接收者时.在您的情况下，它只是一个附加字段，因此，我认为仅使用一个表并不是一个溢出. 根据您的情况，您可以使用一个简单的sql来做到这一点:

@andrewsi comment is quite nice, when you'll have for example many receivers. In your case it's only one additional field, so in my opinion it's not an overflow to use just one table. Regarding your case you can do this in one simple sql:

SELECT m.*,
    CASE 
        WHEN m.fromid = $id THEN m.from_status 
        WHEN m.toid = $id THEN m.to_status
    END as read_status
FROM message m
WHERE
    m.fromid = $id OR m.toid = $id
ORDER BY timestamp DESC;

在您看来，您只需要检查 read_status 字段

And in your view you are only checking the read_status field

这篇关于显示消息状态:PHP/MySQL消息系统中的已读和未读(与其他用户不同)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！