IPhone Cafe

This SQL will return an indented list of Categories from Wordpress version 2.0.2.

SELECT t1.cat_name AS lev1, t2.cat_name as lev2, t3.cat_name as lev3, t4.cat_name as lev4
FROM wp_categories AS t1
LEFT JOIN wp_categories AS t2 ON t2.category_parent = t1.cat_ID
LEFT JOIN wp_categories AS t3 ON t3.category_parent = t2.cat_ID
LEFT JOIN wp_categories AS t4 ON t4.category_parent = t3.cat_ID
WHERE t1.cat_name = ‘top-level-category’

The technique comes from Managing Hierarchical Data in MySQL by Mike Hillyer.  If more than four levels are needed, use additional LEFT JOIN statements.

—

IPhone Cafe’s Category page shows the query in action.  The Wordpress category structure is shown along with checkboxes. Picking categories and selecting “View Selected Categories” saves categories into the web session and then displays only those posts with matching categories.