Group by and display grouped by row info, and rows with its ID in other table below it

Let's say I have 2 tables

In the first table, I have the following fields:

STRUCTURE for `FRUITS`

`id` - int
`name` - varchar

ROWS in `FRUITS`

开发者_如何学Python1 | apples
2 | grapes

in the second table, I have:

STRUCTURE for `COLORS`

`id` - int
`id_fruit` - int
`name` - varchar

ROWS in `COLORS`

1 | 1 | red
2 | 1 | green
3 | 1 | yellow
4 | 2 | purple
5 | 2 | green

in a single query, I want it to output the results like this:

APPLES (#1)

- red (#1)
- green (#2)
- yellow (#3)

GRAPES (#2)

- purple (#4)
- green (#5)

basically I'm just having trouble with it grouping by rows in 1 table, and then outputting all rows from another table with the ID of the grouped by row.

Something like this might do:

SELECT
  `name`
FROM (
  SELECT
    `id_fruit`,
    `id` AS `id_color`,
    CONCAT('- ', `name`) AS `name`
  FROM `COLORS`
  UNION ALL
  SELECT
    `id`,
    0,
    `name`
  FROM `FRUITS`
) s
ORDER BY
  `id_fruit`,
  `id_color`

Both fruit names and colour names are combined together. Colour names receive their `id`s as their `id_color` values, and fruit names are assigned a fictitious `id_color` value of 0. That way, when sorted by `id_fruit`, `id_color`, fruits always appear before their respective colours. Additionally, a 'bullet' ('- ') is attached before colour names.

Select f.id,f.name, group_concat(c.name SEPARATOR ',') as colors from FRUITS f LEFT JOIN COLORS c on f.id=c.id_fruit group by f.id;

and then

$colors = explode(',', $row->colors);