CODEWITHSUNDEEP
phpmysql
Chintan Parekh
Chintan Parekh

Reputation: 1101

PHP MySQL group by similar values in a column

i am try to develop a functionality to group messages stored in mysql table. Every column here references the all the parents. It looks something like this:

 Subject        Message Id    References  
 ----------------------------------------
 Message 1          1             1
 Message 2          2             1,2
 Message 3          3             1,2,3
 Message 4          4             4

What I would like to do here is group all the messages based on their references. This table structure is a result of php imap mails. The references point to the parent. And hence I would like to group the messages based on references. I would like to show grouped messages in the frontend instead of showing individual messages. I would like to iterate through groups and show the frontend users something like this.

 Groups      Subject               Count       
 ---------------------------------------------
 Group 1     Message 1               3
             Message 2
             Message 3
 Group 2     Message 4               1

How can i do this ?

Upvotes: 0

Views: 646

Answers (1)

Kickstart
Kickstart

Reputation: 21513

Nearest I can get to what I think you want would be this:-

SELECT CONCAT('Group ', Sub1.group_order), GROUP_CONCAT(Subject), COUNT(*)
FROM Messages
INNER JOIN
(
    SELECT DISTINCT SUBSTRING_INDEX(References, ',', 1) AS iRef, @Order:=@Order+1 as group_order
    FROM Messages, (SELECT @Order:=0)
    ORDER BY iRef
) Sub1
ON SUBSTRING_INDEX(Messages.References, ',', 1) = Sub1.iRef
GROUP BY CONCAT('Group ', Sub1.group_order)

Although this is producing one row per group (concatenating all the subjects together - easy enough to split in your script).

This assumes that you are only interested in the first element in the reference column (which seems to be the case in the example data you gave) and that the group number is just a sequential number.

Upvotes: 1

Related Questions