I have to list products with its category or categories, I have only products' SKU by it I need to find which category it belongs, so I want to know in which magento table this information stay.
ie: for sku 52429, it is categorized into 3 categories. the report would show all 3 category trees:
Bl > Hair Care > Styling products
Bl > Natural & Organic > Hair Care > Styling Products
Bl > Our Brands > Pureology > Stylers
Thanks! Richa
Magento categories are stored in catalog_category_entity
(pk is entity_id
). To find the relationship between a product and a category, use catalog_category_product
. Its structure is simple:
+-------------+------------+----------+
| category_id | product_id | position |
+-------------+------------+----------+
| 3 | 5 | 1 |
| 3 | 6 | 1 |
| 3 | 7 | 1 |
+-------------+------------+----------+
So, to get all categories for a product:
select cc.* from catalog_category_entity cc
join catalog_category_product cp on cc.entity_id = cp.category_id
where cp.product_id = {{your product id}};
EDIT to note that the info you are looking for (to display category trees) is in the category table itself. An excerpt of the columns (some omitted):
+-----------+-----------+-------+----------+-------+----------------+
| entity_id | parent_id | path | position | level | children_count |
+-----------+-----------+-------+----------+-------+----------------+
| 1 | 0 | 1 | 0 | 0 | 65 |
| 2 | 1 | 1/2 | 1 | 1 | 64 |
| 3 | 2 | 1/2/3 | 1 | 2 | 9 |
| 4 | 2 | 1/2/4 | 2 | 2 | 18 |
| 5 | 2 | 1/2/5 | 3 | 2 | 9 |
+-----------+-----------+-------+----------+-------+----------------+
You can use split on that path
column to get the category IDs of all the categories in the path, and load their names for the report.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With