MySQL Subgroup?

Question

I'm still getting the hang of more advanced MySQL queries, so bear with me here...

I have a table (1 table) with the following columns: name, source, destination, count

I want to group my results by name, then subgroup them by the pairing between destination and source.

So far all I have is this query:

SELECT name, destination, source, COUNT(*) FROM clicks_today GROUP BY name, destination, source

Which gives me groups like this:

group 1
- name 1
- destination 1
- source 1

group 2
- name 1
- destination 2
- source 2

(etc)

What I'd really like is something like this:

group 1
- name 1
  - destination 1
  - source 1
  - destination 2
  - source 2

group 2
- name 2
  - destination 1
  - source 1
  - destination 2
  - source 2

I'm using PHP to query and display my results, pulling my results with mysql_fetch_assoc. I want an array inside an array, basically.

Does that make sense? How can I do this?

Answer 1

I don't think there's a (non-hackneyed) way to return results in exactly that way without implementing some kind of procedure/cursor (see again: hackneyed), so I'd recomment using PHP to get what you want, namely:

while ($row = mysql_fetch_assoc($q)) {
    $results[$row['name']][$row['destination']][] = $row['source'];
}

This will give you a jagged array that will look something like this:

array(
    name1 => array(
        destination1 => array(
            source1,
            source2,
            // ...
        ),
        destination2 => array(
            // ...
        ),
    ),
    name2 => array(
        // ...
    ),
    // ...
);