I am trying to build a page which displays a Private Message, and getting the right data for the page is turning out to be trickier than one would expect.
The problem is that I have to restrict what a “BCC Recipient” sees in the Distribution List…
If you are the Sender, you see All Recipients. (Easy)
If you are a “To:” Recipient, you see All “To:” Recipients. (Trickier)
If you are a “Bcc:” Recipient, you see All “To:” Recipients plus Yourself. (Trickiest)
Here are my tables…
MEMBER
id username
--- ---------
11 username1
12 username2
13 username3
19 DoubleDee
20 LilDucky
25 CrazyCathy
PRIVATE_MSG
id sender subject body
--- ------- -------- -----
12 25 Re: Party My New Year's Eve Party will start at...
PRIVATE_MSG_RECIPIENT
id private_msg_id recipient_id bcc
--- --------------- ------------- ----
104 12 11 0
105 12 12 0
106 12 13 0
107 12 19 1
108 12 20 1
Originally I was going to use this query…
SELECT pm.id, m_from.username AS sender,
GROUP_CONCAT(m_to.username ORDER BY IF (r.recipient_id IN ('13'), 0, 1) SEPARATOR ', ') AS recipients,
pm.created_on, pm.subject, pm.body
FROM private_msg_recipient AS r...
…but I don’t know how to control what gets returned in the GROUP_CONCAT. (As far as I know, you can only dictate the ORDER in that Aggregate Function.)
As is usually the case, I would prefer to return everything I need to display a Private Message in ONE QUERY versus having to piece a whole bunch of stuff together, but that seems tricky in this case?!
Can someone please help me figure out how to do this?
Sincerely,
Debbie