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

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

Answer 2

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 

Answer 3

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)

Answer 4

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.

Answer 5

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.

Answer 6

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