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.

why not `SELECT id as ID name as NAME age as AGE FROM table WHERE id = 1` — Ayaz Ali Shah, Mar 08 '18 at 10:21
because that query is only subquery and might have multiple result. im wonder how to do it in php using foreach — i-faith, Mar 08 '18 at 10:23
@i-faith I'm not really sure I'm following you, if `id` is a primary key then you should only ever get back one result, and as @Mr. Developer said executing a select that pulls out the data with the headers you need will then get you the format you need in PHP. If your `id` isn't a PK, then you can always try limiting the query. — MCMXCII, Mar 08 '18 at 10:30
Please update your question with all the query some data and the expected result — Daniel E., Mar 08 '18 at 10:39

score 1 · Accepted Answer · answered Mar 08 '18 at 13:03

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.

Thanks! i did not do the query provided but you gave me idea :) — i-faith, Mar 08 '18 at 14:46

MYsql GROUP_CONCAT with 3 columns execute in PHP

1 Answers1