如何删除会员A从聊天中收到和发送的消息，而不将其删除在会员B的帐户中

Question

I have a table where i store the chat informations between 2 members (Member A and Member B). Now when Member A delete his messages i throw an sql request like this

$deleting = mysqli_query($connection, "DELETE FROM `mychattable` WHERE (`from`='$id_of_member_a' AND `to`='$id_of_member_b') OR (`from`='$id_of_member_b' AND `to`='$id_of_member_a') ") or die(mysqli_error($connection));

When i do that the problem is that it deletes the messages from both Member A and Member B's account.

I have also tried

$deleting = mysqli_query($connection, "DELETE FROM `mychattable` WHERE `from`='$id_of_member_b' AND `to`='$id_of_member_a' ") or die(mysqli_error($connection));

But when i do that it deletes the messages of Member A from the database so Member B no longer has access to it

How to delete the message received and sent by Member A from the chat without deleting it in member B's account ?

This is the structure of my table

CREATE TABLE IF NOT EXISTS `mychattable` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `from` int(10) unsigned NOT NULL,
  `to` int(10) unsigned NOT NULL,
  `message` text NOT NULL,
  `sent` int(10) unsigned NOT NULL DEFAULT '0',
  `read` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `direction` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `to` (`to`),
  KEY `from` (`from`),
  KEY `direction` (`direction`),
  KEY `read` (`read`),
  KEY `sent` (`sent`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=29 ;

Thanks

Answer 1

There is a way of doing this but it implies that you add 1 column to your table like

is_deleted_by_member

Now when member A delete the message, since your member's id can not be negative you do this

$verif_deletion= mysqli_query($connection, "SELECT id FROM mychattable WHERE ((`from`='$id_of_member_a' AND `to`='$id_of_member_b') OR (`from`='$id_of_member_b' AND `to`='$id_of_member_a')) AND is_deleted_by_member='0' ") or die(mysqli_error($connection));

Now if you get a result then it means that nobody has deleted it yet then you put in the id of the member who just deleted it

    if(mysqli_num_rows($verif_deletion)>0)
    {
    $my_deletion = mysqli_query($connection, "UPDATE mychattable SET is_deleted_by_member='$id_of_member_a' WHERE ((`from`='$id_of_member_a' AND `to`='$id_of_member_b') OR (`from`='$id_of_member_b' AND `to`='$id_of_member_a'))  ") OR die(mysqli_error($connection));
    //
    echo "Message Deleted";
    }
    //In case there is no record then it means that the  message has already been deleted 
//by a member then you will set the field to minus 1 (-1)
    else
    {
    //Before setting it to minus on 
//or not i have to check to see whether the user that is 
    //Connected is the one who has deleted the thing before
    $check_deleted = mysqli_query($connection, "SELECT `id` FROM `mychattable ` WHERE ((`from`='$id_of_member_a' AND `to`='$id_of_member_b') OR (`from`='$id_of_member_b' AND `to`='$id_of_member_a')) AND `is_deleted_by_member`='$id_of_member_a' ") or die(mysqli_error($connection));

    //If there is a no record then this member had not attempted to delete his before
    //So the person who deleted this was the other person then we set it to minus 1
    if(mysqli_num_rows($check_deleted)==0)
    {
        $my_deletion = mysqli_query($connection, "UPDATE `mychattable` SET `is_deleted_by_member`='-1' WHERE ((`from`='$id_of_member_a' AND `to`='$id_of_member_b') OR (`from`='$id_of_member_b' AND `to`='$id_of_member_a'))  ") OR die(mysqli_error($connection));
    }

    echo "Message Deleted";
    }

Finally, before displaying the message for a member all you have to do is to check whether that member's id is in the field of deletion OR whether the field of deletion is inferior to 0

Example for your member A

$verif_check= mysqli_query($connection, "SELECT id FROM mychattable WHERE ((`from`='$id_of_member_a' AND `to`='$id_of_member_b') OR (`from`='$id_of_member_b' AND `to`='$id_of_member_a')) AND (is_deleted_by_member<'0' OR is_deleted_by_member='$id_of_member_a' ") or die(mysqli_error($connection));

Note: Here $id_of_member_a is the member who is connected and operating now.AND your is_deleted_by_member must be an integer BUT NOT an UNSIGNED Int

Hope that helps