Breadcrumbs from hierarchical data?

by Vick   Last Updated January 14, 2018 12:26 PM

I am stuck, please help. I am using MySQL adjacency model to store hierarchical data.

I found good explanation here on how to get the results. But I could not think a way to turn this into actual html breadcrumbs. What I want to do is, when a user clicks and views a product, the category breadcrumbs are shown at the top.

Here's my query:

SELECT
  root.cat_name AS root,
  level1.cat_name AS sub_cat,
  level2.cat_name AS sub_sub_cat
FROM
  categories AS root
LEFT JOIN
  categories AS level1 ON level1.parent_id = root.cat_id
LEFT JOIN
  categories AS level2 ON level2.parent_id = level1.cat_id
WHERE
  root.parent_id = 0
ORDER BY
  root,
  sub_cat,
  sub_sub_cat

This gives the following output

root               sub_cat           sub_sub_cat
Clothing           Women Clothing         Dresses
Clothing           Women Clothing         Blouses
Clothing           Women Clothing         T-shirts
Electronics        TVs & DVD              DVD
Electronics        TVs & DVD              TV

For example, Women Dress Vintage Mini Winter Long is in Dresses category (cat_id = 5 & parent_id(Women Clothing) = 3). So what should be the right PHP code to generate the following breadcrumbs when the item is viewed >Clothing >> Women Clothing >> Dresses

Thank you in advance.

Tags : php mysql


Related Questions