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

[Matt Warden]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Max Schwanekamp wrote:
> 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?

Well, it depends. If every color can come in every size, then there is
no need to get all size-color combinations. However, if that rule does
not hold, then you have to do it like you are suggesting, because you
must maintain a relationship between sizes and colors. (Although, you
could always allow the user to select all sizes and all colors, and then
check later whether that combo is available; but that can be frustrating
for the user.)

- --
Matt Warden
Miami University
Oxford, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFDyBSPrI3LObhzHRMRAtPvAKDf5w/jZwhxSoKpBO6P5eDlW/iGbACeLq3q
i11fXoH6izVqy5Xfcx2mld0=
=rCMA
-----END PGP SIGNATURE-----