Hello.
I have private messaging system in my website, But I want to make an option to view the “sent” messages.
my table goes likt this:
ID | DATE | USER_GET(who gets the message | USER_SEND (who sends it) | MESSAGE | SUBJECT | ISREAD (boolean, 0 or 1)
If I make a query to show all messages the uer sent, it will work, but with a big problem. if the receiver will delete this message from the inbox, it will no longer show this message in the sender “sent” box.
How I can make make it all work?
What I would do here is alter your table to have an ISDELETED column (similar to your ISREAD column) and set the default value to 0. Then, instead of deleting the private message from the table when the user deletes it, simply update the ISDELETED field to 1 for that private message. Just remember to only show messages to the receiver if their ISDELETED value in the database is 0.
Here’s the query I’d execute to add the extra ISDELETED attribute:
ALTER TABLE tableNameHere ADD COLUMN ISDELETED BOOL NOT NULL DEFAULT 0;
And how I will know when to completely delete this message?
How often you prune deleted PMs is entirely up to you. It could be every 3 months, 6 months, 1 year, or even not at all (though depending upon your set up, you may wish to at least archive them elsewhere to prevent your DB from getting too large). If you do choose to prune them every so often (I personally would), then you can either set up a cron job/scheduled task or a MySQL event to run every so often to delete messages older than X months.
It may also be worth noting that hiding ‘deleted’ things from users isn’t just relevant to PMs. You’ll find most software will just hide the deleted items (including posts, threads, etc) with an extra flag in the database (like our ISDELETED one mentioned above). That way, you don’t lose any data from accidental deletions.
A nifty little trick with that in MySQL is to use NULL as the flag for a deleted item. This makes it easy to deal with duplicate entries for unique columns but for items that have been “deleted” since any column containing NULL that forms a unique key is unique. So in the case of an undeleted item the column would store a simple integer like 0 forcing uniqueness where as an number of the same duplicate rows can exists when the deleted column NULL. Not necessarily useful in this case but very useful for other cases where uniqueness is involved but items are just hidden in the database.