php mysql 中的消息系统
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27752062/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Messaging system in php mysql
提问by starkeen
I created a simple messaging system on my website where new registered users can send message to one another. the following mysqlstatement works well on my site,but
my problem is-when UserAsends a message to UserB, The message is shown to UserBin his Inbox, And The message is shown to UserAin his Outbox, now if for some reasons UserBdeleted 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 iswhen the message is deleted from inbox it should still remain in Outbox, any help is much appreciated! thanks!
我在我的网站上创建了一个简单的消息系统，新注册用户可以在其中相互发送消息。以下mysql语句在我的网站上运行良好，但
我的问题是 -当UserA向 发送消息时UserB，该消息显示UserB在他的收件箱中，并且该消息显示UserA在他的发件箱中，现在如果出于某种原因UserB从他的收件箱中删除了该消息，然后从双方删除邮件，我将所有邮件存储在 1 个表中，现在我想要实现的是，当邮件从收件箱中删除时，它仍应保留在发件箱中，非常感谢任何帮助！谢谢！
Table structure is as follows
表结构如下
id message sentby sentto created
Inbox.php
收件箱.php
$you=$_COOKIE['username'];
$st= "SELECT* FROM mbox WHERE sentto='$you' ORDER BY ID DESC LIMIT 10";
outbox.php
发件箱.php
$you=$_COOKIE['username'];
$st= "SELECT*FROM mbox WHERE sentby='$you' ORDER BY ID DESC LIMIT 10";
回答by Josh
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.
我认为您可以保留消息内容的当前表结构。与其添加单独的列或删除的标志，不如为邮箱设置一个单独的表。
So your current mbox table:
所以您当前的 mbox 表：
id message sentby sentto created
Then another table for user_mailboxes
然后是 user_mailboxes 的另一个表
id user mailbox message_id
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.
在为 user_mailboxes 表中的每个用户编写消息时，您必须总共执行三个插入操作，一个插入到消息表中。
So your mbox data looks like this:
因此，您的 mbox 数据如下所示：
id message sentby sentto created
1 Hi There UserA UserB 2015-01-26
2 Hello Back UserB UserA 2015-01-26
And user_mailboxes data would look like this:
user_mailboxes 数据如下所示：
id user mailbox message_id
1 UserA Out 1
2 UserB In 1
3 UserB Out 2
4 UserA In 2
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).
这允许您删除 user_mailboxes 表的各个行。这也将允许您同时向多个用户发送消息（每个用户的新行），并允许您在需要时添加多个邮箱（输入、输出、垃圾箱、重要， 等等）。
To look up the mail for a user for a particular mailbox, you'd just use a join
要为特定邮箱的用户查找邮件，您只需使用 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";
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.
您在删除时需要一个清理脚本，以确保 user_mailboxes 表中不存在不存在的孤立邮件。
回答by Mohneesh Agrawal
Just do one thing add two new fields in your existing table
只需做一件事在现有表中添加两个新字段
- is_sender_deleted
- is_receiver_deleted
- is_sender_deleted
- is_receiver_deleted
If someone delete it from outbox then make is_sender_deleted value to 1. So when you show data in outbox you just list all the records whose having is_sender_deleted field value 0.
如果有人从发件箱中删除它，则将 is_sender_deleted 值设为 1。因此，当您在发件箱中显示数据时，您只需列出所有 is_sender_deleted 字段值为 0 的记录。
Same situation ff someone delete it from inbox then make is_receiver_deleted value 1. So when show data in inbox you just list all the records whose having is_receiver_deleted value is 0.
同样的情况是有人从收件箱中删除它然后使 is_receiver_deleted 值为 1。因此，当在收件箱中显示数据时，您只需列出 is_receiver_deleted 值为 0 的所有记录。
Hope this solution helps you out.
希望这个解决方案能帮到你。
回答by stepozer
I also solved this task. I think one table it is not useful in this case. So, i suggest use 2 tables:
我也解决了这个任务。我认为一张表在这种情况下没有用。所以，我建议使用 2 个表：
CREATE TABLE `message` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`subject` varchar(255) NOT NULL,
`body` text NOT NULL,
`date` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `message_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`message_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`interlocutor` int(11) DEFAULT NULL,
`folder` enum('inbox','sent') NOT NULL,
`starmark` tinyint(1) NOT NULL DEFAULT '0',
`unread` tinyint(1) NOT NULL DEFAULT '1',
`deleted` enum('none','trash','deleted') NOT NULL DEFAULT 'none',
PRIMARY KEY (`id`),
CONSTRAINT `message_user_user_fk_1` FOREIGN KEY (`message_id`) REFERENCES `message` (`id`) ON UPDATE CASCADE,
CONSTRAINT `message_user_user_fk_2` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON UPDATE CASCADE,
CONSTRAINT `message_user_user_fk_3` FOREIGN KEY (`interlocutor`) REFERENCES `user` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
I think it can fix all of your issues, because message users separated from each other
我认为它可以解决您的所有问题，因为消息用户彼此分离
So, for one message we must create 3 inserts like this:
因此，对于一条消息，我们必须像这样创建 3 个插入：
public static function createMessage($subject, $body, $source, $sender_id, $receiver_id)
{
// save DATA to message table ($subject, $body, $source)
// save DATA to message_user table ($message_id, $sender_id, $receiver_id, 'sent')
// save DATA to message_user table ($message_id, $receiver_id, $sender_id, 'inbox')
}
In this case for every user we create separated row in table message_user. So, when user_1 delete message in this inbox folder we mark it as 'deleted' and has no effect on the second user.
在这种情况下，我们为每个用户在 table 中创建单独的行message_user。因此，当 user_1 删除此收件箱文件夹中的邮件时，我们会将其标记为“已删除”并且对第二个用户没有影响。
So, that get all user messages we must run only simple SELECT like this:
因此，要获取所有用户消息，我们必须像这样运行简单的 SELECT：
SELECT *
FROM message m
JOIN message_user mu
ON m.id = mu.message_id
WHERE mu.deleted = 'none'
AND mu.user_id = :user_id
回答by Sanal K
id message sentby sentto created deteled_from_inbox deteled_from_outbox
To Your table I added 2 fields,both will be having YES and NO as values.At first both the fields will be NO
我在你的表中添加了 2 个字段，两个字段都将是 YES 和 NO 作为值。首先这两个字段都是 NO
$you=$_COOKIE['username'];
$st= "SELECT* FROM mbox WHERE sentto='$you' AND deteled_from_inbox='NO' ORDER BY ID DESC LIMIT 10";
$you=$_COOKIE['username'];
$st= "SELECT* FROM mbox WHERE sentto='$you' AND deteled_from_outbox='NO' ORDER BY ID DESC LIMIT 10";
When user deletes data from inbox you will be actually updating the deteled_from_inboxwith YES,So It wont show in inbox part.As we are not touching the deteled_from_outboxit will be showing in the outbox side.
当用户从收件箱中删除数据时，您实际上会更新deteled_from_inboxwith YES，因此它不会显示在收件箱部分。因为我们没有触摸deteled_from_outbox它，它将显示在发件箱一侧。
回答by vural
You can add a column like "status" into mbox table,
您可以在 mbox 表中添加像“状态”这样的列，
Then; if UserB delete the message you can change status as 1 or UserA delete the message you can change status as 2.
然后; 如果用户 B 删除消息，您可以将状态更改为 1，或者用户 A 删除消息，您可以将状态更改为 2。
For inbox :
对于收件箱：
$you=$_COOKIE['username'];
$st= "SELECT* FROM mbox WHERE sentto='$you' AND status <> '1' ORDER BY ID DESC LIMIT 10";
For outbox :
对于发件箱：
$you=$_COOKIE['username'];
$st= "SELECT* FROM mbox WHERE sentby='$you' AND status <> '2' ORDER BY ID DESC LIMIT 10";
Good luck.
祝你好运。
回答by Dharmendra Jadon
Instead of deleting messages from database, use status of that particular message
As SHOWtoSender,SHOWtoReciver,SHOWtoBothor SHOWtoNONE
(use data type ENUM and default as SHOWtoBoth).
Make changes in your table as:
id sender receiver status time
不要从数据库中删除消息，而是使用该特定消息的状态
作为SHOWtoSender、SHOWtoReciver、SHOWtoBoth或SHOWtoNONE
（使用数据类型 ENUM 并默认为 SHOWtoBoth）。
在您的表中进行更改：
id 发送者接收者状态时间
回答by Riad
Add a column like has_mailwhich have default value like ABwhich means both the users have the mail. Now if anyone deleted from their in/out box then particular A/B will be removed.
添加一个像has_mail这样具有默认值的列，AB这意味着两个用户都有邮件。现在，如果有人从他们的输入/输出框中删除，那么特定的 A/B 将被删除。
$st= "SELECT* FROM mbox
WHERE sentto='$you' and has_mail LIKE '%". $you . "' ORDER BY ID DESC LIMIT 10";
$st= "SELECT* FROM mbox
WHERE sentby='$you' and has_mail LIKE '". $you . "%' ORDER BY ID DESC LIMIT 10";
Now you can delete the message from db when both fields are empty:
现在，当两个字段都为空时，您可以从 db 中删除消息：
DELETE FROM mbox WHERE LENGTH(has_mail) < 1
回答by Kristian
I think it might be the best option to use multiple table -- one for each user -- in order to archive that. If you use only one table, then overtime it will become really big.
我认为最好的选择是使用多个表——每个用户一个——来存档。如果你只使用一张桌子，那么加班就会变得非常大。
The solution I propose is that you edit your table structure into:
我建议的解决方案是将表结构编辑为：
id owner message sentby sentto created
This way, when a user create a message, two records will be created: the sender's copy, and the recivier's copy
这样，当用户创建消息时，将创建两条记录：发送者的副本和接收者的副本
When UserA send UserB message "Good Job", the query will be:
当 UserA 向 UserB 发送消息“Good Job”时，查询将是：
sendmessage.php
发送消息.php
$you=$_COOKIE['username'];
$recipient="UserB";
$st1="INSERT INTO tbl_msg VALUES ($id,'$you','Good Job','$you','$recipient','$time)";
$st2="INSERT INTO tbl_msg VALUES ($id,'$recipient','Good Job','$you','$recipient','$time)";
inbox.php
收件箱.php
$you=$_COOKIE['username'];
$st= "SELECT * FROM mbox WHERE sentto='$you' AND owner='$you' ORDER BY ID DESC LIMIT 10";
outbox.php
发件箱.php
$you=$_COOKIE['username'];
$st= "SELECT * FROM mbox WHERE sentby='$you' AND owner='$you' ORDER BY ID DESC LIMIT 10";
delete.php
删除.php
just delete the one that the owner='$you' DELETE FROM mbox WHERE condition1=value1 AND owner='$you'
只需删除 owner='$you' DELETE FROM mbox WHERE condition1=value1 AND owner='$you' 的那个
Basically, my workaround is like: when a user send message, then we insert two message to the database (one copy for the recipient's inbox, and the other copy for the sender's outbox)
基本上，我的解决方法是：当用户发送消息时，我们将两条消息插入数据库（一个副本用于收件人的收件箱，另一份副本用于发件人的发件箱）
When a user deleted his/her message, it will not be deleted from the other's inbox/outbox because each user have their own copy of the message
当用户删除他/她的消息时，它不会从对方的收件箱/发件箱中删除，因为每个用户都有自己的消息副本
回答by Jarwain
This may not be the most robust solution, but it is a fairly functional one, and doesn't require you to make any changes to your DB structure.
这可能不是最健壮的解决方案，但它是一个相当实用的解决方案，并且不需要您对数据库结构进行任何更改。
Change your delete function. Instead of deleting the row in the database, do a few checks. Figure out whether or not it is the sender or the recipient who is doing the deleting. If the sender is deleting, check if sentto == null. If it is, Delete the row. Else, set sentby = null. And vice versa.
更改您的删除功能。与其删除数据库中的行，不如做一些检查。弄清楚是发件人还是收件人正在执行删除操作。如果发件人正在删除，请检查sentto == null. 如果是，请删除该行。否则，设置sentby = null。反之亦然。
I'll be assuming you post the message ID when the user presses delete. Also assuming you are using PDO. Let me know if that assumption is wrong.
我假设您在用户按下删除键时发布消息 ID。还假设您使用的是 PDO。如果这个假设是错误的，请告诉我。
delete.php
删除.php
$link = new \PDO... // blah blah connection stuff
$id = $_POST['id'];
$messageSELECT = $link->prepare("SELECT `sentby`,`sentto` FROM `mbox` WHERE ID = :id");
$messageSELECT->bindValue(':id',$id,\PDO::PARAM_INT);
$messageSELECT->execute();
$msgInfo = $messageSELECT->fetchAll();
$msgDELETE = null;
if($you == $msgInfo['sentby'] && empty($msgInfo['sentto'])){
$msgDELETE = $link->prepare("DELETE FROM `mbox` WHERE ID = :id");
} elseif($you == $msgInfo['sentby'] && !empty($msgInfo['sentto'])){
$msgDELETE = $link->prepare("UPDATE `mbox` SET `sentby` = NULL WHERE ID = :id");
} elseif($you == $msgInfo['sentto'] && empty($msgInfo['sentby'])){
$msgDELETE = $link->prepare("DELETE FROM `mbox` WHERE ID = :id");
} elseif($you == $msgInfo['sentto'] && !empty($msgInfo['sentby'])){
$msgDELETE = $link->prepare("UPDATE `mbox` SET `sentto` = NULL WHERE ID = :id");
} else {
// Shouldn't happen
}
$msgDELETE->bindValue(':id',$id,\PDO::PARAM_INT);
$msgDelete->execute();
回答by invisal
With your current database structure, no, you cannot do that. Let start by making change to some of your structure to achieve what you want.
使用您当前的数据库结构，不，您不能这样做。让我们首先更改您的某些结构以实现您想要的。
1. Add deleted field
1.添加删除字段
The first thing to do is to add one deleted field where it is ENUM(Y, N). The structure of your table will look like this.
首先要做的是添加一个已删除的字段，它是 ENUM(Y, N)。您的表的结构将如下所示。
tblMessage(id, message, sentby, sentto, created, deleted)
Now, with deleted field, it allows the receiver to delete their message and the sender still keep their message. The problem is that it does not allow the sender to delete their message from their Outbox.
现在，有了删除字段，它允许接收者删除他们的消息，而发送者仍然保留他们的消息。问题是它不允许发件人从他们的发件箱中删除他们的邮件。
Outbox
发件箱
SELECT * FROM message WHERE sentby = $you
Inbox
收件箱
SELECT * FROM message WHERE sentto = $you AND deleted = 'N'
2. Make two tables
2.制作两张桌子
In order to make it more flexible (1) so that sender can delete the message from their outbox, but receive still can retain the message in their inbox (2) so that receiver can delete the message from their inbox, and sender still got it in their outbox.
为了使其更灵活（1）以便发送者可以从发件箱中删除消息，但接收仍然可以将消息保留在其收件箱中（2）以便接收者可以从其收件箱中删除该消息，而发送者仍然可以收到在他们的发件箱中。
tblInbox(id, sendby, message, created, deleted)
tblOutbox(id, sendto, message, created, deleted)
