0

I have a table like so that stores private messages between players

id              int(11)
userid          int(11)
type            int(1)
global          tinyint(1)
subject         varchar(55)
content         text
receiver_id     int(11)
allianceID      int(11)
create_date     datetime
read_date       datetime

Now when a user chooses to delete a message from their inbox, I don't actually want to delete the message from the database. I have a separate table called messages_hidden with the following structure

userid      int(11)
message_id  int(11)

This messages_hidden table stores their userid and the corresponding message that they "deleted" from their inbox so it no longer shows up.

Now here is my query that, upon login, extracts all their messages from the database to display to them (parts of the query have been snipped due to length).

SELECT 
        msg.id,msg.type, msg.subject, msg.content, DATE_FORMAT(msg.create_date, '%b %D, %Y') as create_date,
        IF(read_date > create_date, 1, 0) AS hasRead, sender.id AS sender_id, sender.username AS sender_username, receiver.id AS receiver_id, receiver.username AS receiver_username
FROM messages AS msg
    LEFT JOIN users AS sender ON msg.userid = sender.id
    LEFT JOIN users AS receiver ON msg.receiver_id = receiver.id
WHERE (msg.receiver_id = ? OR msg.userid = ? OR msg.global = 1) ORDER BY create_date DESC

This query currently works fine, but how do I join messages_hidden and only extract messages that have not been "deleted" or hidden yet by the user?

I hope I explained my problem well enough. If you are unclear about a certain part of my question, please feel free to ask. Thanks!

josh
  • 135
  • 1
  • 9
  • Why don't you just add a `deleted` column to the `messages` table? – Barmar Nov 14 '15 at 01:07
  • @Barmar That would delete it for both the sender and receiver. Or what if the `global` field is set to 1, which is a mass message to all players by the game or admins, if one player deletes it from their inbox it will delete it for everyone else too. – josh Nov 14 '15 at 01:08

0 Answers0