[thelist] more sql help
Paul Cowan
evolt at funkwit.com
Thu Nov 21 21:28:01 CST 2002
Tom wrote:
> So you are saying categories would only have subcategories under
> them? Problem is sometimes there *aren't* subcategories at all, and
> sometimes there are...
Aaah... so you mean:
Category1
MenuItem1.1x
SubCategory1.1
MenuItem1.1a
MenuItem1.1b
SubCategory1.2
MenuItem1.2a
MenuItem1.1y
Category2
...
(I think, anyway). MenuItems don't HAVE to be in a SubCategory?
Well, that's different. Do subcategories themselves behave as menu
items, or are they just convenient groupings?
If they're are MenuItems too (that is, MenuItems really live under
MenuItems), then you are trying to represent a true hierarchy in
a relational database. This is a problem that has, over the ages,
proven more likely to drive more people mad than, say, idly leaving
the Necronomicon of Abdul Alhazred sitting in your downstairs guest
lavatory during the summer holidays.
There are a lot of ways to do this: all of them have their flaws.
Some are detailed here:
http://www.evolt.org/article//17/4047/
I have used the last one to great effect, but by gum it seems like
overkill for you. Look around, one of these might please you.
If they're really just subcategory labels, and hold no other data,
you could possibly take what I gave you before, but either:
1) create a "dummy" subcategory under each category which is for
"homeless" menuItems. When displaying, don't display the name
for this dummy subcategory.
This way is at least moderately awful.
2) Add to the menuItem a 'CategoryID' as well as the 'SubCategoryID'.
So in the example above, MenuItem1.1x and MenuItem1.1a both have
the CategoryID of Category1. But 1.1a has the SubCategoryID of
SubCategory1.1, while 1.1x has a null SubCategoryID.
You could then use:
$result = @mysql_query("SELECT menuitem.*, category.*,
subcategory.* FROM menuitem, category, subcategory
WHERE (menuItem.subCategoryID *= subCategory.subCategoryID)
and (menuItem.categoryID = category.categoryID) ORDER BY category,
subcategory, item");
where *= is intended as a substitute for whatever mySQL uses
to do left outer joins (I use SQL Server, and I also use the
{INNER|LEFT|...} JOIN ... ON syntax, so I'm not sure what you
want there).
This is also at least moderately awful.
Without knowing if SubCategories behave like MenuItems, like Categories,
both, or neither, it's hard to know, but hopefully this might give you
some ideas. Simplest solution is probably the second one. If there
are ever going to be *4* layers, rather than 3, then you definitely need
to look at ways to implement hierarchies properly, as in the article
above.
Paul
More information about the thelist
mailing list