I've tried using nested sets, and they become very difficult to maintain when dealing with multiple trees and lots of other complications.. I'd like to give PHP's SPL library a stab at this (btw, we are PHP 5.3, MySQL 5.1).
Given two datasets:
The Groups:
+-------+--------+---------------------+---------------+
| id | parent | Category Name | child_key |
+-------+--------+---------------------+---------------+
| 11133 | 7707 | Really Cool Products| 47054 |
| 7709 | 7708 | 24" Monitors | 57910 |
| 7713 | 7710 | Hot Tubs | 35585 |
| 7716 | 7710 | Hot Dogs | 00395 |
| 11133 | 7707 | Really Cool Products| 66647 |
| 7715 | 7710 | Suction Cups | 08396 |
+-------+--------+---------------------+---------------+
The Items
+------------+------------+-----------+----------+---------+
| child_key | totalprice | totalcost | totalqty | onorder | (jan, feb, mar..)
+------------+------------+-----------+----------+---------+
| 24171 | 10.50 | 20.10 | 200 | 100 |
| 35685 | 10.50 | 20.10 | 200 | 100 |
| 76505 | 10.50 | 20.10 | 200 | 100 |
| 04365 | 10.50 | 20.10 | 200 | 100 |
| 01975 | 10.50 | 20.10 | 200 | 100 |
| 12150 | 10.50 | 20.10 | 200 | 100 |
| 40060 | 10.50 | 20.10 | 200 | 100 |
| 08396 | 10.50 | 20.10 | 200 | 100 |
+------------+------------+-----------+----------+---------+
The figures are actually much more complicated than this (I am actually aggregating a variable amount of months or years over the past 15yrs, so there may need to be 20 columns of aggregated results).
I have been trying to figure out RecursiveIterator and IteratorAggregate, but I am having a difficult time finding real world examples that are generic enough to really wrap my head around these classes.
Can someone give me a head start?
