SitePoint Sponsor

User Tag List

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

    Create One "Sent Message" Entry

    I am trying to figure out how to take several records returned from a many-to-many relationship and create a single row.

    Here is what I mean...

    In my database, I have this relationship...
    Code:
    member -||----|<- private_msg ->|----||- private message
    When a Sender sends a PM to multiple Recipients, there is one private_msg record, and there are several private_msg_recipient records.

    So in my query, I would get something like this...

    Code:
    pm_id	username_to	subject		created_on
    ------	------------	--------	-----------
    3	JustAmy		Baby Pics!!	2013-12-06
    3	LisaLisa	Baby Pics!!	2013-12-06
    3	GoGina		Baby Pics!!	2013-12-06
    3	MissMarcy	Baby Pics!!	2013-12-06

    But since the Sender only sent *one* Private Message, in the Sender's "Sent" folder, they should see something like this...

    Code:
    To				Subject		Date
    ---				--------	-----
    JustyAmy, LisaLisa, GoGina...	Baby Pics!!	2013-12-06

    For some reason, I'm drawing a blank on how to take multiple returned records and collapse them down into a single entry...

    Sincerely,


    Debbie

  2. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,079
    Mentioned
    53 Post(s)
    Tagged
    0 Thread(s)
    I gave you the example to do this on the other post. Do an inner join between the two tables where the sender id = the user id whos logged in

    EDIT: pulled from other post - http://www.sitepoint.com/forums/show...ation-and-PM-s

    Code:
    select * from private_msg pm inner join private_msg_recipient pmr on pm.id = pmr.private_msg_id where pm.sender_id = $userId

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,531
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    I gave you the example to do this on the other post. Do an inner join between the two tables where the sender id = the user id whos logged in

    EDIT: pulled from other post - http://www.sitepoint.com/forums/show...ation-and-PM-s

    Code:
    select * from private_msg pm inner join private_msg_recipient pmr on pm.id = pmr.private_msg_id where pm.sender_id = $userId
    You're killing me!!

    I know how to do an Inner Join...

    And, as I stated in my OP, my Inner Join does not work when a Sender PM's Multiple Recipients, because I get multiple records instead of one...


    Quoting myself...

    Quote Originally Posted by doubledee
    When a Sender sends a PM to multiple Recipients, there is one private_msg record, and there are several private_msg_recipient records.

    So in my query, I would get something like this...

    Code:
    pm_id	username_to	subject		created_on
    ------	------------	--------	-----------
    3	JustAmy		Baby Pics!!	2013-12-06
    3	LisaLisa	Baby Pics!!	2013-12-06
    3	GoGina		Baby Pics!!	2013-12-06
    3	MissMarcy	Baby Pics!!	2013-12-06

    *******************************
    **Note: To be clearer... The query *is* working, however, it is not producing the data in the format I want!

    When I join MEMBER to PRIVATE_MSG_RECIPIENT to PRIVATE_MSG, I don't want things AND'ed with a result of 4 records, because there is only ONE PM that was sent!!!

    Yes, in my junction table, there are 4 records, but that is a Logical Representation.

    I need the Physical Representation of what is happening, which is the Sender sent ONE PM with 4 Recipients.

    *******************************


    So, as stated in my OP, the Sender's Sent folder should show the One PM to 4 Recipients like this...
    Code:
    To				Subject		Date
    ---				--------	-----
    JustyAmy, LisaLisa, GoGina...	Baby Pics!!	2013-12-06

    I'm not sure if I want to do this uisng PHP or SQL, however the more I think about it, the more it seems like being able to produce this format in a query qould be much more effiecient.


    Follow me now?


    Would seeing the details of my tables or the SQL that produces the results that I do NOT want help?

    Sincerely,


    Debbie
    Last edited by DoubleDee; Dec 6, 2013 at 21:08. Reason: Rewrote

  4. #4
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,079
    Mentioned
    53 Post(s)
    Tagged
    0 Thread(s)
    Oh, my apologies. You would also need criteria on recipient_id. So throw 'AND recipient_id = $id' at the end of that.

  5. #5
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,531
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    Oh, my apologies. You would also need criteria on recipient_id. So throw 'AND recipient_id = $id' at the end of that.
    Nope.

    Here, is what I figured out...

    Code MySQL:
    SELECT pm.id, pm.sender_flag,
    CONCAT(
    	SUBSTRING(
    		GROUP_CONCAT(m_to.username ORDER BY m_to.username SEPARATOR ', '),
    	 1, 20),
    '...') AS recipients, 
    pm.subject, pm.created_on
    FROM private_msg_recipient AS r
    INNER JOIN private_msg AS pm 
    ON pm.id = r.private_msg_id
    INNER JOIN member AS m_to 
    ON m_to.id = r.recipient_id
    WHERE pm.sender_id = 11
    AND pm.sender_deleted_on IS NULL
    AND pm.sender_purged_on IS NULL
    GROUP BY (pm.id)


    Code:
    id	recipients		subject			created_on
    ---	-----------		--------		-----------
    3	JustAmy, LisaLisa...	Re: Party Invite	2013-12-06



    Turns out this was better solved by MySQL than by PHP...

    Sincerely,


    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
  •