[thelist] Multiple columns instead of rows w/ LEFT JOIN
Max Schwanekamp
lists at neptunewebworks.com
Sat Jan 14 14:17:35 CST 2006
Matt Warden wrote:
> 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.)
Thanks for the reply Matt.
In most cases, there is no set number. To expand my example, red
widgets might only be available in size small, blue widgets in
small-med-large, etc -- a common scenario on retail sites. My actual
code is slightly more abstract, where I have educational courses which
are assigned to one or more categories, are delivered via one or more
media, have zero or more author names etc.
Rudy L. (r397.com) replied offlist with my solution (btw he says "matt
warden is doing an absolutely fantastic job handling all the database
questions" :). Turns out MySQL and PostgreSQL have an aggregating
function I was unaware of, GROUP_CONCAT()[0,1] which allows
concatenating those multiple left-join matches into a comma-delimited
string, which can then be exploded into an array in PHP. This allows me
to use a single query to retrieve the data, and simplify the PHP code as
well.
Of course now MySQL is working harder to aggregate those rows. But in
combination with caching, GROUP_CONCAT() is a godsend! The biggest
caveat is that it's specific to MySQL 4.1+ and PostgreSQL. In my case
that's acceptable.
[0] http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html
[1] http://db4free.blogspot.com/2006/01/hail-to-groupconcat.html
--
Max Schwanekamp
http://www.neptunewebworks.com/
More information about the thelist
mailing list