SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,529
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    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
    Code:
    id	username
    ---	---------
    11	username1
    12	username2
    13	username3
    19	DoubleDee
    20	LilDucky
    25	CrazyCathy

    PRIVATE_MSG
    Code:
    id	sender		subject		body
    ---	-------		--------	-----
    12	25		Re: Party	My New Year's Eve Party will start at...

    PRIVATE_MSG_RECIPIENT
    Code:
    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...
    Code MySQL:
    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

  2. #2
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,529
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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...
    Code:
    FROM: CrazyCathy
    TO: username1, username2, username3
    SUBJECT: Re: Party
    BODY: My New Year's Eve Party will start at...

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


    Debbie

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    since everybody gets to see the "to" list, then part of your WHERE clause should be...
    Code:
    bcc = 0
    now all you have to add is a compound OR...
    Code:
    recipient_id = $me AND bcc = 1
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,529
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    since everybody gets to see the "to" list, then part of your WHERE clause should be...
    Code:
    bcc = 0
    now all you have to add is a compound OR...
    Code:
    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

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i did more than imply
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,529
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    since everybody gets to see the "to" list, then part of your WHERE clause should be...
    Code:
    bcc = 0
    Like this, right...
    Code:
    WHERE pm.id = 12
    AND r.bcc = 0

    Quote Originally Posted by r937 View Post
    now all you have to add is a compound OR...
    Code:
    recipient_id = $me AND bcc = 1
    Like this, right...
    Code:
    WHERE pm.id = 12
    AND r.bcc = 0
    OR(r.recipient_id = 20 AND r.bcc = 1)
    Sincerely,


    Debbie

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Like this, right...
    what happened when you tested it? ™
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,529
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    what happened when you tested it?
    It appears to be working. Thanks!!

    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...
    Code:
    http://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?!

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

    As mentioned above, for the "Sent View", my query ends with...
    Code:
    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...
    Code:
    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

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Follow me?
    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?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,529
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    nope, sorry
    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

  11. #11
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,529
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    If the RecipientID = 13 and is on the "To:" list, then this query...

    Code MySQL:
    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...

    Code:
    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...

    Code:
    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

  12. #12
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,529
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I think I have the problem isolated and a question formulated if anyone is still listening...


    Debbie


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •