MySQL (or PHP?) group results by field data

Question

I have a MySQL database that looks similar to this:

ID    Group   Name

1       1       John
2       1       Andrea
3       1       Jack
4       2       Mike
5       2       Kurt
6       3       Alice

I need to sort the results in a html table that looks like this:

Group       Name
-----------------------
  1         John
            Andrea
            Jack
-----------------------
  2         Mike
            Kurt
-----------------------
  3         Alice
-----------------------

I don't know if this should be done with a SQL query (concat_group, or something similar) or with PHP, can somebody please help me out?

Guys, thanks for the help below, but I also need to accomplish something similar, like this:

ID    meta_key  meta_value   name

1       group     1          John
2       group     1          Andrea
3       group     1          Jack
4       group     2          Mike
5       group     2          Kurt
6       group     3          Alice

and I need to sort / display the same as the example above, something like this:

group       name
-----------------------
  1         John
            Andrea
            Jack
-----------------------
  2         Mike
            Kurt
-----------------------
  3         Alice
-----------------------

Now my problem has taken new dimensions. My database looks like:

b.ID    b.meta_key  b.meta_value   a.title   

1       group       1                Title 1
2       group       1                Title 2
3       group       1                Title 3
4       group       2                Title 4
5       group       2                Title 5
6       group       3                Title 6
7       coef        6                Title 1
8       coef        4                Title 2
9       coef        12               Title 3
9       coef        2                Title 4
9       coef        3                Title 5
9       coef        7                Title 6

(I'm working with to tables)

And I need to achieve:

group       title         coef
---------------------------------
  1         Title 1        6 
            Title 2        2
            Title 3        12
--------------------------------
  2         Title 4        2
            Title 5        3
--------------------------------
  3         Title 6        7
--------------------------------

¿Is this even possible?

Solution 1

This would my solution, althoug is not elegant at all

<?php
$dbc = new MySQLI(DBHOST,DBUSER,DBPASS,DB);
$result = $dbc->query("
SELECT
p.Group as 'group',
GROUP_CONCAT(name) as names
FROM prueba p
GROUP BY p.Group
");
?>
<table>
<tr>
    <th>Group</th>
    <th>Name</th>
</tr>
<?php while($row = $result->fetch_assoc()){
    $names = split(",",$row["names"]);
?>
    <tr>
        <td><?php echo $row["group"] ?> </td>
        <td><?php echo $names[0]; array_shift($names) ?></td>
    </tr>
    <?php foreach( $names as $name){ ?>
        <tr>
            <td></td>
            <td><?php echo $name ?></td>
        </tr>
    <?php } ?>
<?php } ?>
</table>

Solution 2

Try this:

// SQL stuff

$group = null;

while($row = mysql_fetch_array($result))
{
    if($row['group'] != $group)
    {
        echo $row['group'];
        $group = $row['group'];
    }

    $row['name'];
}

Comments

[OMG Ponies]

This sort of thing can be done via SQL query, but it's the rare sort of presentation stuff that I wouldn't often handle in SQL

[jigfox]

+1 That's exactly how I would do this, no Grouping at all, but you missed the else block to set the $group variable

[Imre L]

No,you dont need the else group, just move the contents from else into if ($group=$row['group']; echo $group;), then it works as intended. And the sql query has to have ORDER BY group

[MacMac]

Indeed, you don't really need the else statement for this type of grouping really...

[Alberto]

Thanks for the help Guys, i'm amazed by the quickness. I have just tried your code (YouBook) and the result was: 1John1Mike1Jose1Pedro2Archie2Peter3Alberto I need it to be: 1John Mike Jose Pedro 2Archie Peter 3 Alberto I'm doing something wrong? Thanks again! p.d: if I try using GROUP BY (number) it show me 1 result per number: 1 John 2 Archie 3 Alberto

[Alberto]

I'v edited de question, Armonage thanks again, my final query resulted as: SELECT p.meta_key as 'meta_key', p.meta_value as 'meta_value', GROUP_CONCAT(name) as names FROM prueba p WHERE p.meta_key = '_numero' GROUP BY p.meta_value Now everything is working just fine, thanks again!