(Nested MySQL statement) Is it possible to implement with one SQL query? Any improvements?

Question

Is it possible to implement with one SQL query? Any improvements?

$query = mysql_query("SELECT id, name FROM fruits WHERE `group`=''");

if ($query) {
    while ($row = mysql_fetch_array($query, MYSQL_ASSOC)) {
        echo $row['name'];

        $query2   = mysql_query("SELECT name FROM fruits WHERE `group`='{$row['id']}'");

        if (mysql_num_rows($query2)) {
            echo ':';

            while ($row2 = mysql_fetch_array($query2, MYSQL_ASSOC)) {
                echo ' '. $row2['name'] . ',';
            }
            mysql_free_result($query2);

            echo '<br>';
        }
    }
    mysql_free_result($query);
}

Result:

Berries: blueberry, raspberry, strawberry,
Citrus: grapefruit, lime,
Pear

Database structure SELECT * FROM fruits:

id   | group | name
-------------------------
03E7 |       | Berries
0618 | 03E7  | blueberry
051B | 03E7  | raspberry
02AA | 03E7  | strawberry
035F |       | Citrus
07A5 | 035F  | grapefruit
0633 | 035F  | lime
05E1 |       | Pear

Answer 1

Fully working example:

<?php

header("Content-Type: text/plain");

$con = mysql_connect("localhost", "root", "");
mysql_select_db("test");

$query = mysql_query("SELECT a.name as `group`, GROUP_CONCAT(b.name ORDER BY b.name SEPARATOR ', ') as name FROM fruits a LEFT JOIN fruits b ON b.`group` = a.id WHERE a.`group` = '' GROUP BY a.name");

if ($query) {
    while ($row = mysql_fetch_array($query)) {
        $group = $row['group'];
        $name  = $row['name'];

        if (isset($name)) {
            echo $group . ': ' . $name . "\n";
        } else  {
            echo $group . "\n";
        }
    }
    mysql_free_result($query);
}
mysql_close($con);

?>

Result:

Berries: blueberry, raspberry, strawberry
Citrus: grapefruit, lime
Pear

Answer 2

SELECT a.name as group_name, b.name as item_name
FROM fruits a
LEFT JOIN fruits b
ON b.`group` = a.id
WHERE a.`group` = ''

will get you a set of result rows that looks like...

group_name   item_name
----------------------
Berries      blueberry
Berries      raspberry
...          ...
Citrus       grapefruit
Citrus       lime
...          ...
Pear         NULL

You can then process this in your code to generate whatever final output you want.