MYsql GROUP_CONCAT with 3 columns execute in PHP

Question

i have query that has subselect that may return more than 1 result:

   SELECT
    t1.group,
        (SELECT GROUP_CONCAT(t3.id,','t3.name,','t3.age SEPARATOR('---') FROM table t3 WHERE t3.id= t2.id GROUP BY t3.id )) AS names,
    FROM
        table1 AS t1
        JOIN table2 AS t2 ON t1.id = t2.id
    WHERE
        t1.id = 1

I managed to show the result using nested foreach but the results kinda mixed up. Im thinking how to have this. Possibile results

GROUP   | ID | NAME | AGE |
--------|----|------|-----|
        | 1  | John | 20  |
GROUP1  | 2  | Phil | 22  |
        | 3  | Dave | 24  |
--------|----|------|-----|
        | 4  | Jan  | 20  |
GROUP2  | 5  | Luke | 22  |

the above will show up in html table using php.

Answer 1

The simplest way in my opinion would be to do a query order by group and then see how to manage it in php :

SELECT
    t1.group,
    t3.id, 
    t3.name,
    t3.age
    FROM
        table1 AS t1
    LEFT JOIN table2 AS t2 ON t1.id = t2.id
    LEFT JOIN table3 t3 ON t3.id= t2.id
    WHERE
        t1.id = 1
    ORDER BY t1.group

then in php when you display the data, you display it only when the groupe name change.

Example code :

if ($result->num_rows > 0) {
    $lastGroup = "";
    // output data of each row
    while($row = $result->fetch_assoc()) {
        if($lastGroup == $row["group"]){
          $row["group"]= "";
        }
        else{
          $lastGroup = $row["group"] ;
        } 
        echo $row["group"]. " || "
             . $row["id"]. " || " . $row["name"]. " || " . $row["age"]."<br>";
    }
} else {
    echo "0 results";
}

This is of course an example depending on how you want to display the data but I think you get the general idea here.