SitePoint Sponsor

User Tag List

Results 1 to 12 of 12

Hybrid View

  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    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,777
    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,273
    Mentioned
    60 Post(s)
    Tagged
    3 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
    rudy.ca | @rudydotca
    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,777
    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,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i did more than imply
    rudy.ca | @rudydotca
    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,777
    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,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Like this, right...
    what happened when you tested it? ™
    rudy.ca | @rudydotca
    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,777
    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,273
    Mentioned
    60 Post(s)
    Tagged
    3 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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •