Just to add my two cents, I will both agree and disagree with sdeming's proposed solution.
First the agreement: for small enough volumes of data, this approach should work fine. It's an understandable solution (which is always nice to have!).
Now the disagreement: using successive queries to the database to walk the tree is very inefficient. The proposed solution, assuming you just print the results, also does not preserve the parent/child relationship. I'm guessing that you will eventually want to do more than just display an indented list.
I would suggest keeping the data as you had it in a single table: ID, PARENT_ID, DESCRIPTION
sdeming's solution is more normalized, but it only matters if you plan on allowing a child to have more than one parent (which I believe is a bad idea)
The first step is to read all of the data from the table:
Code:
select id, parent_id, description from hierarchy
order by parent_id
The next step is to loop through each row and create an in-memory tree structure. This would start with an imaginary root node at level 0. The root is the parent to all level 1 objects. The class defining your tree structure would have an id, parent_id, description, level, and an array of children. Level is optional, but could be used by a print method to specify the number of tabs to use for indenting. The array would only store the id values for each of the children.
I specified in the SQL that the rows be ordered by parent_id so that all children are grouped together (not strictly necessary). For each row, check to see if an object exists with that id. If not, create a new object. Check to see if the parent exists. If not create a new object. Add an array element to the parent for this id. Go to the next element and repeat.
As each object is created, an element is added to an associative array that uses the id as the key, and a pointer to the object as the value. In Java, I'd use a Hashtable. I know PHP allows this type of structure, but I'm not sure what it is called. This allows you to take an id from the array of child ids and immediately reference the object that it belongs to.
To print the tree in order, you have to traverse it. You start by retrieving the pointer to the object whose id = 0 (the root node). You then loop through the array of child ids (your top level of categories -- level 1), retrieving the objects along the way and calling the print method. If children exist, you perform the same function on the next level (looping through child ids...calling print methods). This would work nicely as a recursive function call that terminates when an object has no children. When this happens, the program goes to the next level 1 category and repeats until all level 1s are depleted.
This solution will work for any depth tree, executes a single SQL query, and preserves the parent/child relationship.
One last thing. If the description "Cameras" appears under multiple categories, I would create unique ids for each entry. This preserves the single parent rule and keeps your tree traversal consistent. If the description has to change across the board, you can change it with a single update statement.