Can I output this table of categories (id and parent_id) with only one query?

Question

I have a table that looks like:

What I want to do is output the main categories (those with null category_id) and under each one, list the categories that have that category_id as the parent. That way, I create a little hierarchy output.

I could do this simply by doing a query that grabs all of the categories with null parent. Then doing another query that finds the categories with that as its parent. However, I am confident there's a way to do this in PHP/MySQL with only one query.

Any help, even conceptually, would be greatly appreciated!

Answer 1

You can get all categories at once from the database and build your hierarchical structure with a recursive function, see: Convert a series of parent-child relationships into a hierarchical tree?

Answer 2

SELECT COALESCE( c2.category_order, c1.category_order ) as top_order,
             c1.*
FROM categories c1
LEFT JOIN categories c2
ON c1.parent_category_id = c2.category_id
ORDER BY top_order, c1.category_order