SQL query for category ID

[georg_filler]

Hello,

in the table "catalog_category_entity" are the path of the categories. In column "path" I have the ids:

Example:

1/2/3/16

Now I need the ID from the category "trousers":

"Default Categorie/clothes/men/trousers"

In the above example it is "16" ... but how can I get this with an SQL query?

Where are the name are stored?

Maybe someone can help?

Thank you!

[Damian Culotta]

Hi @georg_filler,

Maybe this query can help you a little bit:

SELECT cev.*
FROM catalog_category_entity_varchar cev
WHERE cev.attribute_id = (SELECT ea.attribute_id FROM eav_attribute ea  WHERE ea.entity_type_id = 3 AND attribute_code = 'name')
AND cev.value = 'Root Catalog'

Some tips/clues:

• entity_type_id = 3 means Category
• attribute_code is the attribute to seach for into the category attributes. If the data type is different you should check which catalog_cateogry_entity_? table you will need.
• cev.value = 'Root Catalog' is the category name I'm looking for. If you change the attribute code, you should use a valid or possible value there.

[georg_filler]

Hello,

many thanks to your answer ... but not sure if I unterstood irt correctly:

My given categorie path text is:

"Default Category/clothes/men/trousers"

I need the category ID from "trousers"

This don´t work:

SELECT cev.*
FROM catalog_category_entity_varchar cev
WHERE cev.attribute_id = (SELECT ea.attribute_id FROM eav_attribute ea  WHERE ea.entity_type_id = 3 AND attribute_code = 'trousers')
AND cev.value like 'Default Category'

Thank you!!

[Damian Culotta]

Hi @georg_filler,

That's becasue you changed the attribute code insted the value you're looking for. Try again with:

SELECT cev.*
FROM catalog_category_entity_varchar cev
WHERE cev.attribute_id = (SELECT ea.attribute_id FROM eav_attribute ea 
WHERE ea.entity_type_id = 3 AND attribute_code = 'name')
AND cev.value like 'trousers'

[georg_filler]

Hello,

ok, this work.

But I have the complete path:

"Default Category/clothes/men/trousers"

trousers is not unique so I have to give the complete path:

SELECT cev.*
FROM catalog_category_entity_varchar cev
WHERE cev.attribute_id = (SELECT ea.attribute_id FROM eav_attribute ea 
WHERE ea.entity_type_id = 3 AND attribute_code = 'name')
AND cev.value like 'Default Category/clothes/men/trousers'

Maybe you have a solution for this?

Thank you!