Messaging system in php mysql

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.

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

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.

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

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.

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

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 

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)