MySQL group_concat and nest with another group_concat

Question

Not sure if the title explains the situation right but I will try to do my best explaining here.

I have a table with 3 fields linked to other tables and I want to get all the Rows grouped in the following way:

item_id, user_id, group_id
   1        2         3
   2        2         3
   3        4         5
   4        2         4

In my query i want in comma separated format all the items_id grouped by group_id i also have some extra conditions on the WHERE clause that's why the inner join

That i can do like with this query

 "SELECT 
    GROUP_CONCAT( DISTINCT A.item_id ) AS ids
    FROM tableA A
    INNER JOIN tableB B ON(tableA.id = tableB.id)
   WHERE xxxxx
  GROUP BY A.group_id

 "

Later i can loop the results and using the comma separated to inner loop every id within the result

But i also want to group it by user_id in order to do something like this

foreach( query_results.... ){
      foreach( group_id.... ){
           foreach( item_id.... ){
                 // Display info
           }
      }
 }

Any ideas on this?

Answer 1

Use 2 GROUP_CONCATS

SELECT GROUP_CONCAT( DISTINCT A.item_id ) AS ids, 
        GROUP_CONCAT( DISTINCT A.group_id ) AS groups

     FROM tableA A
     INNER JOIN tableB B ON(tableA.id = tableB.id)
     WHERE xxxxx
     GROUP BY A.group_id

Loop your resource then use Explode on the groups and ids value and loop them both as you want