php mysql 消息功能,PHP MySQL中的消息系统

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!

Table structure is as follows

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.

So your current mbox table:

id message sentby sentto created

Then another table for 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.

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

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

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";

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.