Umair Umair - 1 year ago 108
PHP Question

Get all Magento Categories using SQL query

I have this SQL to get all Magento Categories.

cc.entity_id AS id,
cc.`value` AS path,
cc1.`value` AS `NAME`,
catalog_category_entity_varchar cc
JOIN catalog_category_entity_varchar cc1 ON cc.entity_id = cc1.entity_id
JOIN eav_entity_type ee ON cc.entity_type_id = ee.entity_type_id
JOIN catalog_category_entity cce ON cc.entity_id = cce.entity_id
cc.attribute_id = '57'
AND cc1.attribute_id = '41'
AND ee.entity_model = 'catalog/category'

This returns all categories, but I created a new category from Magento backend but that is not showing.

That category is published and has no products in it.
Following image is from

enter image description here

entity_id = 449
is showing when I run that query, because it has
attribute_id = 57 and 41

But I am talking about
entity_id = 452
that is not showing, because it does not have
attribute_id = 57

I want to ask Magento experts, what does
attribute_id = 57
belong to? and how can I fix this query to fetch all categories?

Answer Source

You are selecting categories from EAV category model which has attributes 57 and 41 of varchar type :

cc.attribute_id = '57'
cc1.attribute_id = '41'

According to my 1.9 magento installation this are name and path attributes of catalog/catagory:

select distinct ea.attribute_code from eav_attribute as ea inner join catalog_category_entity_varchar as vc on ea.attribute_id=vc.attribute_id where vc.attribute_id in (57,41);

To get all raw categories use this sql:

SELECT `e`.* FROM `catalog_category_entity` AS `e` WHERE (`e`.`entity_type_id` = '3')'

or to get categories with names use this:

SELECT `e`.*,
       IF(at_name.value_id > 0, at_name.value, at_name_default.value) AS `name`
FROM `catalog_category_entity` AS `e`
INNER JOIN `catalog_category_entity_varchar` AS `at_name_default` ON (`at_name_default`.`entity_id` = `e`.`entity_id`)
AND (`at_name_default`.`attribute_id` = '41')
LEFT JOIN `catalog_category_entity_varchar` AS `at_name` ON (`at_name`.`entity_id` = `e`.`entity_id`)
AND (`at_name`.`attribute_id` = '41')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download