Hi, I am developing a navigation system using the nested set methods (in PHP / MySQL) based on the probably infamous MySql article.
I have stumbled upon a problem that I can't think round at the moment! How do I add a flag to disable a category and it's sub categories?
The select needs to only select categories where active=1 but still in the hierarchy, this is the bit I am stuck on.
The current query is:
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;
So I need to join on another status table and then carry out the query, do I need to do a query on the table for active rows (how do I disable all children if a parent is disabled?) and use it as a sub query or visa versa?
Any help would be greatly appreciated, thank you.