CODEWITHSUNDEEP
phpmysql
Amit Verma
Amit Verma

Reputation: 41219

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

Upvotes: 14

Views: 32865

Answers (10)

Sanal K
Sanal K

Reputation: 733

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.

Upvotes: 0

Mohneesh Agrawal
Mohneesh Agrawal

Reputation: 103

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.

Upvotes: 7

Riad
Riad

Reputation: 3850

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

Upvotes: -1

stepozer
stepozer

Reputation: 1181

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

Upvotes: 3

Josh
Josh

Reputation: 942

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.

Upvotes: 33

invisal
invisal

Reputation: 11171

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)

Upvotes: -2

Jarwain
Jarwain

Reputation: 95

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

Upvotes: -1

Kristian
Kristian

Reputation: 2505

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

Upvotes: -3

Dharmendra Jadon
Dharmendra Jadon

Reputation: 141

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

Upvotes: 1

vural
vural

Reputation: 391

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.

Upvotes: 1

Related Questions