Messaging system in php mysql

Question

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

Answer 1

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.