Messaging system in 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";

回答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.

回答2:

Just do one thing add two new fields in your existing table

1. is_sender_deleted
2. 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.

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.

Hope this solution helps you out.

回答3:

I also solved this task. I think one table it is not useful in this case. So, i suggest use 2 tables:

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:

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.

So, that get all user messages we must run only simple SELECT like this:

SELECT *
FROM message m
    JOIN message_user mu
    ON m.id = mu.message_id
WHERE mu.deleted = 'none'
    AND mu.user_id = :user_id

回答4:

You can add a column like "status" into mbox table,

Then; if UserB delete the message you can change status as 1 or UserA delete the message you can change status as 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.

回答5:

Instead of deleting messages from database, use status of that particular message
As SHOWtoSender,SHOWtoReciver,SHOWtoBoth or SHOWtoNONE
(use data type ENUM and default as SHOWtoBoth).
Make changes in your table as:
id sender receiver status time

回答6:

Add a column like has_mail which have default value like AB which means both the users have the mail. Now if anyone deleted from their in/out box then particular A/B will be removed.

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

DELETE FROM mbox WHERE LENGTH(has_mail) < 1 

回答7:

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.

回答8:

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

回答9:

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

回答10:

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

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.

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

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.

 tblInbox(id, sendby, message, created, deleted)
 tblOutbox(id, sendto, message, created, deleted)

回答11:

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