how do i group records in mysql table?

Question

I have a table in MySQL which has group_id and name columns (the table has multiple users divided into groups using group_id).

table design and data

I want to display these users in the html table using grouping of names.

grouping of names

I used the following MySQL query, but it does not do the job.

SELECT 
    * 
FROM `teams` 
WHERE 1
GROUP BY 
    group_id

What should I do to get the output using PHP and MySQL?

Here is the Possible [Duplicate](http://stackoverflow.com/questions/12668528/sql-server-group-by-clause-to-get-comma-separated-values) of your question. — Manjeet Barnala, Apr 14 '16 at 07:10

score 0 · Answer 1 · answered Apr 14 '16 at 07:01

0

You should use group_concat for the query

SELECT group_id, GROUP_CONCAT(name)
from teams 
group by group_id;

answered Apr 14 '16 at 07:01

ScaisEdge

131,976
10
91
107

score 0 · Answer 2 · answered Apr 14 '16 at 07:02

0

Use GROUP_CONCAT() function for that:

SELECT
    group_id,
    GROUP_CONCAT(`name`) AS teams
FROM
    teams
GROUP BY
    group_id

answered Apr 14 '16 at 07:02

mitkosoft

5,262
1
13
31

score 0 · Answer 3 · answered Apr 14 '16 at 07:03

0

Try below SQL query

select 
id,group_concat(concat(`name`) separator ',')
as Result from teams group by group_id

answered Apr 14 '16 at 07:03

Ajay

235
2
8

score 0 · Answer 4 · answered Apr 14 '16 at 07:18

When using GROUP_CONCAT(name) in the examples above and you get your query back you can do the following in php to loop through the results.

SELECT group_id, group_concat(name) AS team FROM your_table GROUP BY group_id

$html = [];
foreach ($results as $row) {
    $html []= "<div>";
        $html []= "GROUP ID:" . $row['group_id'];
        $html []= "TEAM:" . $row['team'];
    $html []= "</div>";
}
echo join("", $html);

Note: I am using a $html array here because cause its more memory efficient to use an array for large scale string concatenations than it is to use .= string operator.

how do i group records in mysql table?

4 Answers4