[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