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…


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…


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…


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

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/showthread.php?1179892-Normalization-and-PM-s


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… :rolleyes:

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…

*******************************
**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…


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

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…


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)


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

:irock:

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

Sincerely,

Debbie