If you do any custom development for Turnkey Webtools’ Sunshop shopping cart software, sooner or later you come across this challenge. Categories in Sunshop are organized very simply. Every sub-category has a parent category id (stored in the “subof” field of the categories table). But what happens if you have many layers of subcategories and need to determine the top level parent category of a subcategory that is several levels deep?
For example, let’s say we have categories set up as follows:
id title subof 1 Fishing Tackle 2 Lures 1 3 Hooks 1 4 Salt Water 3 5 Fresh Water 3 6 Worm 5
We’d like to be able to quickly determine that Worm hooks found all the way down at the fourth level of this hierarchy are a part of the top level category known as Fishing Tackle. But we have to traverse several levels of subcategories to get to that.
This is a classic recursion problem. Writing recursive queries in MySQL for situations like this are challenging. However, using PHP and MySQL, we can do this quickly and easily.
In the code snippet below, the function “GetTopParentCategory” takes any category id as a parameter, and returns the id of the top level parent category. If the category id parameter is already a top level category, that category id is the one returned by the function.
An example use based on the data in the example categories table:
would output “1”, telling us that Fishing Tackle (id 1) is the top level parent category of the Worm category (id 6).