Category>Sub category>Sub sub category>Sub sub subcategory>product 1

Question

I working on category and sub category website with products, and the number of sub categories that can be created is unlimited, meaning we can have sub categories of the sub categories them selves. Everything is working fine, I just have a question: Let suppose that "PRODUCT 1" belongs to the "SUB SUB CATEGORY 1", and the "SUB SUB CATEGORY 1" is the sub category of "SUB CATEGORY 1" and this latter is the sub category of "CATEGORY 1", how can I display the following at the top of the page "PRODUCT 1": Category 1>Sub category 1>Sub sub category 1> product 1

My category table is: id nameofcategory image parent

My product table is: id nameofproduct category

Thanks in advance

I'd say you are looking for recursive tree traversal / Wikipedia. — Thorsten

Tibor B. Tibor B. · Accepted Answer · 2014-07-19T12:09:08

I assume, that in case a category doesn't have a parent, then its parent is 0 in the database. First, we get the product's name, and its category's name, and the category's parent ID. Then we check, if the parent ID is 0. If it's not, then we look for its parent. It should continue, until the parent ID becomes 0. So, this should do the trick:

$query = mysql_query("SELECT c.parent Parent, p.nameofproduct ProductName, c.nameofcategory CategoryName FROM product p INNER JOIN category c ON p.category = c.id WHERE p.id = " . $your_product_id . " LIMIT 1");
$result = mysql_fetch_array($query);
$parent = $result['Parent'];
$product_path = $result['CategoryName'] . ">" . $result['ProductName'];
while ($parent != 0) {
  $parent_query = mysql_query("SELECT parent, nameofcategory FROM category WHERE id = " . $parent);
  $parent_result = mysql_fetch_array($parent_query);
  $parent = $parent_result['parent'];
  $product_path = $parent_result['nameofcategory'] . ">" . $product_path;
}

Edit: Also, I would recommend you to change your column names to something like this:

product table - product_id - name - category_id category table - category_id - name - image - parent_id

This would make it more obvious on what is the link between the two tables. It would make constructing INNER JOIN queries much easier and more apperent.

So in this case the code look like this:

$query = mysql_query("SELECT c.parent_id Parent, p.name ProductName, c.name CategoryName FROM product p INNER JOIN category c ON p.category_id = c.category_id WHERE p.product_id = " . $your_product_id . " LIMIT 1");
$result = mysql_fetch_array($query);
$parent = $result['Parent'];
$product_path = $result['CategoryName'] . ">" . $result['ProductName'];
while ($parent != 0) {
  $parent_query = mysql_query("SELECT parent_id, name FROM category WHERE category_id = " . $parent);
  $parent_result = mysql_fetch_array($parent_query);
  $parent = $parent_result['parent_id'];
  $product_path = $parent_result['name'] . ">" . $product_path;
}

It does the same as the code above, but it's easier in my opinion.

Category>Sub category>Sub sub category>Sub sub subcategory>product 1

1 Answers