[thelist] Multiple columns instead of rows w/ LEFT JOIN

[Max Schwanekamp]

In MySQL I have a master table of products with a one-to-many 
relationship to product options.  Right now I do something like this:
SELECT p.id, p.name, o.id AS opt_id, o.desc AS color
FROM products p
LEFT JOIN options o
ON p.id = o.product_id

I get a result consisting of one row for each option row found, like this:
+----+----------+--------+-------+
| id | name     | opt_id | color |
+----+----------+--------+-------+
| 1  | widget   | 1      | red   |
| 1  | widget   | 2      | green |
| 1  | widget   | 3      | blue  |
+----+----------+--------+-------+

In PHP I like to put my db results into an array or object, so when I do 
this I usually end up pulling the first row and then iterating over the 
remaining rows to get a child array of the options.  When there is more 
than one left-joined table, the loops can get pretty spaghetti-ish -- 
e.g. if I add a "size" option to the above, there would be a row for 
every available size in each color.  It does avoid the overhead of 
multiple database queries for populating a single object's properties, 
but I'm thinking there must be a better way.  Suggestions on how to do 
this more efficiently using PHP and MySQL?

Ideally I'd like to use a single query, and end up with a structure 
something like:

Array
(
    [id] => 1
    [name] => widget
    [options] => Array
        (
            [0] => Array
                (
                    [opt_id] => 1
                    [color] => red
                )

            [1] => Array
                (
                    [opt_id] => 2
                    [color] => green
                )

            [2] => Array
                (
                    [opt_id] => 3
                    [color] => blue
                )

        )

)

I'm using a "homegrown" db abstraction layer, but I'm open to migration if another option exists.

TIA!

-- 
Max Schwanekamp
http://www.neptunewebworks.com/