Alternative to GROUP_CONCAT?

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?! :frowning:

Can someone please help me figure out how to do this?

Sincerely,

Debbie

Here are some Test Scenarios…

CrazyCathy would see…

FROM: CrazyCathy
TO: username1, username2, username3, DoubleDee
SUBJECT: Re: Party
BODY: My New Year’s Eve Party will start at…

username1 would see…


FROM: CrazyCathy
TO: username1, username2, username3
SUBJECT: Re: Party
BODY: My New Year's Eve Party will start at...

DoubleDee would see…


FROM: CrazyCathy
TO: username1, username2, username3, DoubleDee
SUBJECT: Re: Party
BODY: My New Year's Eve Party will start at...

Sincerely,

Debbie

since everybody gets to see the “to” list, then part of your WHERE clause should be…

bcc = 0

now all you have to add is a compound OR…

recipient_id = $me AND bcc = 1

You didn’t say anything about my GROUP_CONCAT.

I guess that means that all I have to do is control what records appear via the WHERE clause in my query?

Is that what you are implying?

Sincerely,

Debbie

i did more than imply :smiley:

Like this, right…


WHERE pm.id = 12
AND r.bcc = 0

Like this, right…


WHERE pm.id = 12
AND r.bcc = 0
OR(r.recipient_id = 20 AND r.bcc = 1)

Sincerely,

Debbie

what happened when you tested it? ™

It appears to be working. Thanks!! :tup:

There is only one lingering problem…

Hmm, how to describe this in under a thousand words?! :-/

So since we are talking about Private Messages, it is extremely important that only the correct people can read a PM.

You view a PM by going to a URL like this…


h[b][/b]ttp://local.debbie/account/view-pm/14

Obviously I don’t want Jane-user to be able to just type random numbers in the URL and pull up anybody’s Private Message?! :eek:

So, back to the query you were helping me out on…

As mentioned above, for the “Sent View”, my query ends with…


WHERE pm.id = 12
AND r.blind_copy = 0

Nowhere does my query check that the person logged in is the “Sender” and thus allowed to see that query.

Likewise, in the "Incoming View, my query ends with…


WHERE pm.id = 12
AND r.blind_copy = 0
OR(r.recipient_id = 20 AND r.blind_copy = 1)

Nowhere does my query check that the person logged in is one of the “To:” Recipients and thus allowed to see that query.

The way I programmed my PHP script was that BEFORE I get to the queries in this thread, I run one query to see if the logged in Member was the “Sender”, and then a second query to see if the logged in Member was a “Recipient” (of any kind).

If they were neither, my script displays an Error Message.

So, in theory, by the time a user gets to my “Sent View” and “Incoming View” queries, they have been “vetted”.

Even so, is it sufficient to rely on those earlier checks in my script and they basically “open things up” in the queries we discussed here?

I’m not seeing a way to take the $sessionMemberID and incorporate it with my queries above and still get ONE RECORD?!

Follow me? :-/

Sincerely,

Debbie

nope, sorry

i suppose i could, if i spent a lot of time digesting what you said, but i don’t have that kinda time

you, however, do… so how would you answer this question –

is it sufficient to rely on those earlier checks in my script and they basically “open things up” in the queries we discussed here?

In other words, the query you helped me out with isn’t working entirely correctly…

you, however, do… so how would you answer this question –

I am testing different scenarios now, but am stuck…

Debbie

If the RecipientID = 13 and is on the “To:” list, then 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), m_to.username SEPARATOR ', ') AS recipients,
pm.created_on, pm.subject, pm.body
FROM private_msg_recipient AS r
INNER JOIN private_msg AS pm
ON pm.id = r.private_msg_id
INNER JOIN member AS m_from
ON m_from.id = pm.sender_id
INNER JOIN member AS m_to
ON m_to.id = r.recipient_id
WHERE pm.id = 12
AND r.blind_copy = 0
OR(r.recipient_id = 13 AND r.blind_copy = 1)
AND r.recipient_deleted_on IS NULL
AND r.recipient_purged_on IS NULL
GROUP BY (pm.id)

Is producing a duplicate record…


id	sender		recipients
11	BigBob		username3
12	BigBob		username3, username1, username2

(I just want to see the 2nd record.)

If the RecipientID = 20 and is on the “Bcc:” list, then the above query correctly returns…


id	sender		recipients
12	BigBob		SillySam, username3, username1, username2

(This is correct, because “SillySam” (Bcc:) and “username3”, “username2”, “username1” (all To:) appear as one returned record.)

I need to get that problem fixed before I can address what I tried to explain above.

Sincerely,

Debbie

I think I have the problem isolated and a question formulated if anyone is still listening…

Debbie