Outputting full hierarchy for child/parent relationshop table

[Jarrod1937]

Need help developing some code that will retrieve a full tree and leaf nodes with correct indentation based on the level, using a child parent relationship table setup. Currently i already have a script that does this, but i know it is probably the least efficient method out there. Here is a sample of the data:

----- categories  table------
categories_id | parent_id
----------------------------
      790         |     0
      1           |     0
      63          |     1
      64          |     790
      71          |     1
      72          |     790
      80          |     790
      83          |     80 

To start, 0 means the top level, the start of the tree. So what this means is that categories 790 and 1 are main categories, and categories 64/72/80 are subcategories of 790 with category 83 being a sub category of 80... and so on.
So the kind of output i'd like is that if you're in say, category 83, the output would look like (though of course the id's swapped out for the category names):

790
  64
  72
  80
   83 

As you can see, the output gives indentation to visually see the hierarchy.
Any help would be appreciated, thanks!

[Mindzai]

You'll find this (and other such tasks) much easier if you switch from the Adjacency Model to the Nested Set Model. More info here: http://dev.mysql.com/tech-resources/...ical-data.html

[Jarrod1937]

Well, unfortunately that is not an option at the moment. Currently i don't have enough time to attempt that, especially since it will only be to make the categories easier to program for, although i do agree.
So, any idea on how to do this with the existing adjacency model?

[Jarrod1937]

Bump!
Figured i'd try one more time to see if anyone has any nice methods before i hack away at a solution.

p.s. And as said above, replacing the hierarchy design is not an option, that would require a ton of reprogramming for which my deadline will not allow.