Get list of Category & Category Product from Magento using Mysql Query

If you want to get magento category list using  mysql.

Use following query to get Categories List


SELECT DISTINCT cc.entity_id as category_id, cc.value as name, cc1.value as
url_path ,cce.parent_id as parent_id ,0 as top,level as `column`,position as
sort_order,1 as status,created_at as date_added,updated_at as date_modified
FROM catalog_category_entity_varchar cc
JOIN catalog_category_entity_varchar cc1 ON cc.entity_id=cc1.entity_id
JOIN catalog_category_entity_int cc_int ON cc1.entity_id=cc_int.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
WHERE cc.attribute_id in (select attribute_id from eav_attribute where attribute_code ='name') AND cc1.attribute_id in (select attribute_id from eav_attribute where attribute_code ='url_path')
and cc_int.attribute_id in (select attribute_id from eav_attribute where attribute_code ='is_active')
and cc_int.value = 1
and ((cce.parent_id = 2 and cce.children_count > 1) or cce.parent_id > 2)
AND ee.entity_model = 'catalog/category' order by cce.parent_id asc,
cce.position asc;


 

 

Following query use to get Assigned Product categories.

 


SELECT cc.entity_id as category_id, cc.value as name, cc1.value as
url_path ,cce.parent_id as parent_id ,0 as top,cpe.sku

FROM catalog_category_entity_varchar cc
JOIN catalog_category_entity_varchar cc1 ON cc.entity_id=cc1.entity_id
JOIN catalog_category_entity_int cc_int ON cc1.entity_id=cc_int.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
Left JOIN
catalog_category_product ccp on cc.entity_id = ccp.category_id
Left JOIN
catalog_product_entity cpe on ccp.product_id = cpe.entity_id
WHERE cc.attribute_id in (select attribute_id from eav_attribute where attribute_code =’name’) AND cc1.attribute_id in (select attribute_id from eav_attribute where attribute_code =’url_path’)
and cc_int.attribute_id in (select attribute_id from eav_attribute where attribute_code =’is_active’)
and cc_int.value = 1
and ((cce.parent_id = 2 and cce.children_count > 1) or cce.parent_id > 2)
AND ee.entity_model = ‘catalog/category’ order by cce.parent_id asc,
cce.position asc

 

Thanks.

#sajjucode