We were looking for a way to create a product setup form for our Magento-based eCommerce site. One of the hangups was getting a list of all our categories so that we could create a dropdown for users to select the category name from, but that we could pull the category ID from then as well. There are some posts that I found which tell you how to export all the categories with the ID value, but I didn't see anything that also told you how to get the parent category info so I had to dig into it myself. Luckily it wasn't too difficult so if you're looking to do something similar or just need a list of your category tree, you're in luck. There may be a way to do this in PHP using the some built-in functions of Magento, but I'm not familiar enough with them so I had to turn directly to MySQL. If you have an easier way please leave a comment to let everyone else know.
If you're looking at this then I'm going to guess you're a developer, or at least know someone who is that has access to the database running your site and can help you. If not then I wouldn't suggest doing this without some assistance unless it's on a test site or something other than your live site.
Within the Magento database there are two tables we need to join to get the information we're after: catalog_category_entity and catalog_category_entity_varchar. The entity table has the id values and the varchar table has the name. Now, there is a magic number used in the query that seems to vary database to database. That value is the attribute_id from the catalog_category_entity_varchar table. What I did to find out what value to use is look at the contents of the table and pay attention to the attribute_id value given to the Root Catalog entry that should be first. In my case it was 33 on one site, and 35 on another, which is why I say it varies. However, that attribute_id appears to identify the primary value field we want in order to get the correct category name, so mark it down.
The query I used to create my category list for the site where I found the attribute_id value to be 33 looked like this:
SELECT cc.entity_id,cv.value,cc.parent_id,cc.path FROM dbname.catalog_category_entity cc INNER JOIN dbname.catalog_category_entity_varchar cv ON cc.entity_id=cv.entity_id WHERE cv.attribute_id=33;
Make sure to change the attribute_id=XX; at the end to match whatever attribute_id value you found it to be within your own database. You also need to substitute your own database name in for dbname, but I'm hoping that you already knew that if you have access to the site's MySQL backend.
In this the entity_id is the ID of the category, value is the name of the category, parent_id is the ID of the parent category directly above in the hierarchy, and path is the full path in the hierarchy tree to the category based on the ID values.
I ran this query and had it export to a CSV file, and from that was able to build a drop down list to be used for selecting categories. Then we'll take products that get input into our form and upload them to the site through the use of Magmi. Hopefully this has been useful and helps you figure out whatever you're attempting to do with the categories on your Magento site.
