[thelist] PHP, MySQL - category counter puzzle

[Joshua OIson]

Quinn,

Rudy wrote,
: an alternative to a counter might be a simple flag -- yes/no this category
: has products (somewhere) under it, and if yes, you will count them as you
: go down exploding the tree on display anyway
:
: this will make updating a bit faster, and another way to do that is to
also
: keep an extra column "topcategory" in each category record which points to
: the top category in that branch, so that rather than recursively going up
: the tree to update the flag, you can do them all in one update staement,
: e.g.
:
:     update category
:        set productflag=1
:          where topcategory=[thisbranch]

If this technique is not going to work with you because you are interested
in the total subcategory item counter at every level, and not just from the
top level, then you may want to store the category lineage with every
record.  That is, assume that you have a unique number that identifies every
category.

001 Hats
002 Shoes
003 Top Hats
004 Berrets
005 Cowboy Hats
006 Loafers
007 Penny Loafers
008 Black Penny Loafers
009 Brown Penny Loafers

 From the list, we know that categories 003 and 004 and 005 fall under 001.
006 falls under 002.  007 falls under 006; and 008 and 009 fall under 007.
All quite logical thus far.

Now, at each category entry you store the parent_id, perhaps like you do
already, but you also store the parent lineage to get to that category.  In
this manner, the Brown Penny Loafer category would have these two fields.

PARENT_ID = 007
LINEAGE = 002-006-007

Once you have all the categories set up in this manner (hopefully I have
been clear thus far), it is easy to check whether or not a category is
subcategory of a known category.  First, find the lineage for the known
category:

select
  LINEAGE
from CATEGORY
where CATEGORYID = #

Then, you can find all subcategories of it, no matter how much deeper they
are, in one query:

select
  CATEGORYID
from CATEGORY
where LINEAGE like '[the one we just read]%'

That will give you a list of all categories under a known category!  If you
need to sum up the products in each subcategory, you now have a category
list (by id) to go from.

HTH,
-joshua