[thelist] Multiple columns instead of rows w/ LEFT JOIN
Max Schwanekamp
lists at neptunewebworks.com
Thu Jan 12 15:22:29 CST 2006
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/
More information about the thelist
mailing list