After signing up for Commission Junction, I wanted to be able to store their category tree in a MySQL database. CJ has a method in its Support Services API called getCategories that allows you to retrieve a category list; however, the category list is flat (not in a tree format), and I wasn’t ever able to make the getCategories method work. CJ describes how it should work here, but I didn’t have any luck.

So, I ended up manually building a category tree based on the categories listed in the CJ console. Download it here:

CJ Categories.sql

The category tree is set up in a simple adjacency list (parent-based category) structure. Read this article for more information on tree structures in MySQL.

Use this query to retrieve a complete list of all categories in list format:

SELECT COALESCE(c2.id, c1.id) id, c1.name, COALESCE(c2.name, '') FROM category_map_cj c1

LEFT JOIN category_map_cj c2 ON c2.parent_id = c1.id AND c2.parent_id != c2.id

WHERE c1.parent_id = c1.id;

Leave a comment if you encounter any issues with this or if you have any questions.