MySQL – Magento categories export the right way – bottom level category path split and category ID value lookup

After some research i found that it’s best to approach it like this:

1. find bottom level category for each product and return it’s path
2. delimit the path into individual categories. That gives you IDs of individual categories
3. find values for each category
4. concat into one cell

Preferred method as it might be easier

OR

1. find bottom level category for each product and return it’s path
2. find parent of that category, then find next parent and so on until you reach top level category. That gives you IDs of individual categories
3. find values for each category
4. concat into one cell

also a good method but may be unnecessarily long winded.

Either of this approaches will guarantee you have full category path every time as in Magento you don’t have to select all categories in a path. Also works if product belongs to multiple categories.

So, at the moment i am stuck at steps two and 3.

• I don’t know how to split category path with a delimiter.
• I don’t know how to lookup values on already outputed results.

My current query looks like this:

SELECT 
    ccp.product_id,
    -- pe.sku,
    -- pe.type_id,
    cce.path,
    ccp.category_id,
    ccev.value,
    cce.parent_id
FROM
    mage_catalog_category_product AS ccp
        JOIN
    mage_catalog_category_entity AS cce ON ccp.category_id = cce.entity_id
        JOIN
    mage_catalog_category_entity_varchar AS ccev ON ccev.entity_id = cce.entity_id
        JOIN
    mage_catalog_product_entity AS pe ON pe.entity_id = ccp.product_id
WHERE
    cce.children_count = '0'
        AND cce.path LIKE '1/14%'
        AND cce.path NOT LIKE '1/14/207%'
        AND ccev.attribute_id = '33'
        AND ccev.store_id = '0'
ORDER BY pe.sku

output looks like this

The aim is to have my path contain category names, rather than IDs. so it looks like mydomain.com/books/kids/ rather than 1/2/3.

How do i now find a parent of a parent?

OR

How do i split paths into individual categories IDs and find their values not the value of my bottom level category?

Then how do i concat the results into one cell?