Magento product's category

7
votes

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

2
mysqlmagento

2 Answers

8
votes

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.

7
votes

First load up the product model

Either by ID

$product = Mage::getModel('catalog/product')->load($id);

or by attribute (SKU)

$product = Mage::getModel('catalog/product')->loadByAttribute('sku', '52429');

Now you can load the category IDs

$categoryIds = $product->getCategoryIds();

Then to get the full category objects 

foreach($categoryIds as $categoryId) { 
  $categories[] = Mage::getModel(’catalog/category’) 
    ->setStoreId(Mage::app()->getStore()->getId()) 
    ->load($categoryId);
}

Now to get the parent of each category

foreach($categories as $category) {
  $category->getParentCategory();
}

This is all you need i think.