i have a table with 3 columns id,name and parent_id
representing categories. The root categories(categories with no parents) have parent_id 0. All other categories have parent_id as the id of their immediate parent. There is no limit on the depth of categories i mean that a category can be 3,4 or even 10 levels down from a root category. What i need now is a PHP multi-dimensional array that contains all the root categories at the first level and then their immediate sub-categories at the next level,each sub-category under its parent category,and their sub-categories under them 1 level down. So its a tree like structure.
there can be many levels in the tree
I dont need the exact code but need an idea. one such idea is get all the root categories with a select query and then fire a select query for each root query to get its subcategoies and so on recursively but that would be too much select queries.
Or if i know that my table is going to contain say 300 rows at the maximum, how can i do something like
$categories=GetResultAsArray(select * from categories);
and now manipulate the $categories array in memory to get the desired tree.