[thelist] PHP, MySQL - category counter puzzle
Quinn Comendant
quinn at mac.com
Mon May 28 04:09:27 CDT 2001
Hi guys,
This is a PHP and MySQL question. I'm working on a catalog for a web
store with products organized in categories. I'm trying to find the best
way of listing the total number of items within a category. This will
also allow me to hide a category link if it contains no products or
subcategories with products. One way would be to recursively drill
through the category tree counting all the products found, but this
massive recursion, occurring as many as 15 times per page, is
unacceptable.
I would like your opinion on using the following method.
- A column in the categories table that would be responsible for
containing a total count of all products contained in all the categories
under it.
- When a product is added in its various categories, each of those
category's counters would be incremented, as would their parent category,
and their parent's parent category, etc.
- When a product is modified (the categories to which it is associated
are changed), all the previous category counters are decremented, the old
categories are dereferenced, then the new categories are referenced and
their counters incremented.
- When a product is deleted, all the category counters are decremented
and the categories are dereferenced.
- To find how many items a category contains, I need only query the
counter for that category.
Does this make sense? Can anybody think of a better way?
I have the tables set up in 3rd normal form with: categories, products,
and products_categories.
Thanks,
Quinn
More information about the thelist
mailing list