MySql query to find category path (breadcrumb) for osCommerce

Category Breadcrumb Query for osCommerce

In our recent project we implemented Sphinx Search for an osCommerce based site. We included the full path (breadcrumb) of category into search. I thought the breadcrumb query might be helpful for many developers. Note we had the nested categories up to 9 levels, you can easily adjust query for any level. You may or may not need the id or name so you can remove to make query simple

SQL Query

SELECT node.categories_id AS node_id, d_node.categories_name AS node_name,
u1.categories_id AS u1_id,d1.categories_name AS d1_name,
u2.categories_id AS u2_id,d2.categories_name AS d2_name,
u3.categories_id AS u3_id,d3.categories_name AS d3_name,
u4.categories_id AS u4_id,d4.categories_name AS d4_name,
u5.categories_id AS u5_id,d5.categories_name AS d5_name,
u6.categories_id AS u6_id,d6.categories_name AS d6_name,
u7.categories_id AS u7_id,d7.categories_name AS d7_name,
u8.categories_id AS u8_id,d8.categories_name AS d8_name,
u9.categories_id AS u9_id,d9.categories_name AS d9_name
FROM categories node
LEFT JOIN categories_description d_node ON d_node.categories_id = node.categories_id AND d_node.language_id = 1
LEFT JOIN categories u1 ON u1.categories_id = node.parent_id
LEFT JOIN categories_description d1 ON d1.categories_id = u1.categories_id AND d1.language_id = 1
LEFT JOIN categories u2 ON u2.categories_id = u1.parent_id
LEFT JOIN categories_description d2 ON d2.categories_id = u2.categories_id AND d2.language_id = 1
LEFT JOIN categories u3 ON u3.categories_id = u2.parent_id
LEFT JOIN categories_description d3 ON d3.categories_id = u3.categories_id AND d3.language_id = 1
LEFT JOIN categories u4 ON u4.categories_id = u3.parent_id
LEFT JOIN categories_description d4 ON d4.categories_id = u4.categories_id AND d4.language_id = 1
LEFT JOIN categories u5 ON u5.categories_id = u4.parent_id
LEFT JOIN categories_description d5 ON d5.categories_id = u5.categories_id AND d5.language_id = 1
LEFT JOIN categories u6 ON u6.categories_id = u5.parent_id
LEFT JOIN categories_description d6 ON d6.categories_id = u6.categories_id AND d6.language_id = 1
LEFT JOIN categories u7 ON u7.categories_id = u6.parent_id
LEFT JOIN categories_description d7 ON d7.categories_id = u7.categories_id AND d7.language_id = 1
LEFT JOIN categories u8 ON u8.categories_id = u7.parent_id
LEFT JOIN categories_description d8 ON d8.categories_id = u8.categories_id AND d8.language_id = 1
LEFT JOIN categories u9 ON u9.categories_id = u8.parent_id
LEFT JOIN categories_description d9 ON d9.categories_id = u9.categories_id AND d9.language_id = 1

Team Qavi Tech

More Blogs

Discover the latest insights and trends in technology with the Qavi Tech Blog. Stay updated with expert articles, industry news, and innovative ideas.