[MYSQL] A recursive query?

[eluser]Clooner[/eluser]
[quote author="pistolPete" date="1236978934"]Could you give an example of
- what you mean by "mixed up" and
- what kind of data you are trying to categorize ?[/quote]

Al though I haven't tried it, with MPTT it seems you can't have the same category return at a different place in the tree.

Code:

- Teams
   Yellow
   Blue
   Cyan
   Magenta
   Green
   Red
   Violet
   Orange
- Colors
   - RYB
      Red
      Yellow
      Blue
   - RGB
      Red
      Green
      Blue
   - CMY
      Magenta
      Yellow
      Cyan
   - GVO
      Green
      Violet
      Orange

[eluser]Colin Williams[/eluser]
The benefit of MPTT is more optimal queries. The downside is all the code you need to keep it managed. With parent_ids, you have two options: Provide a $depth option and recur through that depth, which will start to rack up queries. Or, grab the entire table in one query and then filter through it with PHP code. I would toy with each option (maybe have two functions in your model that do each) and just test it out.