I'm working on a light-weight CMS and I have a table of menu items that I want to administer. The relevant fields for this question are menu_item_id (PRIMARY KEY) menu_item_parent menu_item_status(PUBLISHED, UNPUBLISHED, TRASH) If there are sub-menus, their menu_item_parent is set to the menu_item_id of the parent menu item. If I change the menu_item_status of a parent, I want the effect to cascade down through all the child menus, however many levels deep. I tried doing this with a recursive function in PHP but the process would hang on me. I'd rather do it on the level of MYSQL if that's possible. Is there a MYSQL solution for this?
A well structured php program will require resources of the same order as the mysql. Since we are talking about a branched structure it definitely is exponential growth. A good algorithm may be the depth first. Use a stack to keep track of parent ids and go till the bottom, change the status and move up by popping from the stack. If it takes too much time you may need to set the max execution time to a higher value. You can perform the same in SQL as well using a procedure and a similar or the recursive algorithms