PHP MySQL中的消息系统 [英] Messaging system in php mysql

问题描述

我在我的网站上创建了一个简单的消息传递系统，新的注册用户可以在其中相互发送消息.以下mysql语句在我的网站上运行良好，但是 我的问题是-，当UserA向UserB发送消息时，该消息显示在其收件箱中的UserB中，并且该消息显示在其发件箱中的UserA中，现在由于某些原因，UserB从他的收件箱中删除了该邮件，然后从两侧删除了该邮件，我将所有邮件存储在一张表中，现在我要实现的目标是从收件箱中它应该仍然保留在发件箱中，非常感谢您的帮助！谢谢！

I created a simple messaging system on my website where new registered users can send message to one another. the following mysql statement works well on my site,but my problem is- when UserA sends a message to UserB, The message is shown to UserB in his Inbox, And The message is shown to UserA in his Outbox, now if for some reasons UserB deleted the message from his Inbox, then the message is deleted from both sides, I am storing all message in 1 table, now what I want to achieve is when the message is deleted from inbox it should still remain in Outbox, any help is much appreciated! thanks!

表结构如下

id   message    sentby   sentto    created

Inbox.php

$you=$_COOKIE['username'];     
$st= "SELECT* FROM mbox WHERE sentto='$you' ORDER BY ID DESC LIMIT 10";

outbox.php

$you=$_COOKIE['username'];
$st= "SELECT*FROM mbox WHERE sentby='$you' ORDER BY ID DESC LIMIT 10";

推荐答案

我认为您可以保留消息内容的当前表结构.与其在单独的列上添加或删除标志，不如在邮箱上使用单独的表.

I think you can keep your current table structure for the message content. Rather than adding on separate columns or deleted flags, you'd be better off having a separate table for mailboxes.

因此，您当前的mbox表:

So your current mbox table:

id   message    sentby   sentto    created

然后再创建一个用于user_mailboxes的表

Then another table for user_mailboxes

id   user    mailbox    message_id

在写一条消息时，您必须对user_mailboxes表中的每个用户执行三个插入操作，一个插入到消息表中.

You'd have to do three total inserts when writing a message, one to the message table, on for each user in the user_mailboxes table.

因此，您的mbox数据如下所示:

So your mbox data looks like this:

id   message     sentby    sentto    created 
1    Hi There    UserA     UserB     2015-01-26
2    Hello Back  UserB     UserA     2015-01-26

user_mailboxes数据如下:

And user_mailboxes data would look like this:

id   user        mailbox   message_id
1    UserA       Out       1
2    UserB       In        1
3    UserB       Out       2
4    UserA       In        2

这使您可以删除user_mailboxes表的各个行.通过允许您同时向多个用户发送消息(每个用户一个新行)，并允许您添加多个邮箱(如果需要)(进，出，废纸,、重要)，这也将允许将来使用附加组件.等).

This allows you to delete individual rows for the user_mailboxes table. This would also allow for future add-ons by allowing you to send messages to multiple users at the same time (A new row for each user), and allow you to add more than one mailbox if needed (In, Out, Trash, Important, etc).

要为特定邮箱的用户查找邮件，只需使用联接

To look up the mail for a user for a particular mailbox, you'd just use a join

SELECT * FROM user_mailboxes LEFT JOIN mbox ON mbox.id = user_mailboxes.message_id WHERE user_mailboxes.user = "$user" AND user_mailboxes.mailbox = "Out";

在删除时，您需要一个清理脚本以确保在user_mailboxes表中不存在孤立消息.

You'd need a clean up script as you delete to make sure there are no orphaned messages that do not exist in the user_mailboxes table.

这篇关于PHP MySQL中的消息系统的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！