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 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:

sendmessage.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

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

outbox.php

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

delete.php

just delete the one that the 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

Answer 2

If you don't want to loose any data from your database and don't want to alter your database but still want your users to have the ability to "delete", their messages, just add a string in front of "sentby" or "sentto" field. This applies if sentBy and sentTo have string values

For example:
UserA sents a message to UserB.
in your table you have:

id       message    sentby   sentto    created
msgid      msg      UserA     UserB      date

In your "delete" action if this is done by UserB add a string "DeleteD---" in front of UserB in the db field sentto. So now your db gets like this:

id       message    sentby        sentto          created
msgid      msg      UserA     DeleteD---UserB      date

In your php code make some functions that would check if the string "DeleteD---" exists in the sentBy or sentTo fields before doing anything else:

function checkForDeleted($str) {
if (strpos($str,'DeleteD---') !== false) {
    return true;
}
}
function getUserIfDeleteD($str) {
$user=array();
if(checkForDeleted($str)===true) {
$arr=explode("---",$str);
$user=$arr[1];
return $user;
}
}

This way you "keep" the info in your database table to be available for both users making sure that if UserB delete the message sent by UserA, then UserA will still be able to see in his outbox the user that he sent the massage to. If both users do delete the same message sometime later on then you can completely delete from the database.

In case sentBy and sentTo have userid values (integers), then i propose adding just one field in the end of the table

id       message    sentby      sentto    created  deleted_message_flag
msgid      msg     UserA_id    UserB_id    date             00

ALTER TABLE `messages` ADD COLUMN `deleted_message_flag` bit(2) NOT NULL AFTER `created`;

how the above field works? As a binary indicator for sentby and sentto regarding who has deleted the message from his mailbox. If message is not deleted then value is 0 if it is deleted then value is 1. First bit is for sentBy and second bit is for sentTo. This way you store less data and create only one field in your current table. How you analyse the 2 bits?

deleted_message_flag        deleted_from_sentBy        deleted_from_sentTo
        00                           0                          0
        01                           0                          1
        10                           1                          0
        11                           1                          1

Answer 3

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.

Answer 4

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.

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.

delete.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();

Answer 5

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

$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_inbox with YES,So It wont show in inbox part.As we are not touching the deteled_from_outbox it will be showing in the outbox side.