I have a "pm_recipient" table which contains a separate record for each Recipient of a Message.
Code:
id	pm_id	recipient_id	created_on
---	------	-------------	-----------
104	12	25		2013-12-28
105	12	39		2013-12-28
106	12	72		2013-12-28
107	12	16		2013-12-28

It is then joined with a "private_msg" and "member" table to yield a recordset used to display "Sent Messages".

If a Message is sent to 4 Recipients, I need to roll those 4 records up into one record, because only one Message was sent.

I did this by using the following magic in SQL...

Code MySQL:
SELECT pm.id, pm.sender_flag,
	CONCAT(
		SUBSTRING(
			GROUP_CONCAT(m_to.username ORDER BY m_to.username SEPARATOR ', '),
			 1, 40),
		'...') AS recipients,


The snippet above groups the multiple Recipient records into one record, plus concatenates the Recipients into one field, and truncates anything longer than 40 characters.

So in your "Sent Folder" view, you might see...
Code:
To		Subject			Date
---		--------		-----
username1,	Re: Your Voice-mail	2013-12-28 12:25:18
username2,
username3...

On to my question...


After testing my query and code, I noticed that my SQL is just concatenating the Recipients up to 40 characters and then slapping an ellipsis on the end.

This is not exactly what I wanted.

Is there a way - in my SQL - to make it so that IF the concatenated Recipients exceed 40 characters, THEN I add an ellipsis on the end, ELSE I do not??


(An Ellipsis implies continuation. So if there is only one Recipient, then having an Ellipsis on the end is *misleading*!!)

Can this be done, and if so, how would I modify my SQL above?

Sincerely,


Debbie