This is the third SOPW on this topic in the past few days, please bare with me. I've asked here and on a MySQL forum and have tried piecing things together to get something to click in my mind how it all works.

In short, I'm looking for a way to create an ininite-depth category system with as many sub categories as the admin wants. I've now begun using the parentid field to link back to the upper next category but now I'm confused on how to pull back the entire path/tree.

This is a print out of my table 

                  id   parentid   name
      Edit       Delete     1      0      Animals
    Edit     Delete     2     1     Mammals
    Edit     Delete     3     1     Fish
    Edit     Delete     4     2     Dog
    Edit     Delete     5     2     Cat
    Edit     Delete     6     3     Fresh Water
    Edit     Delete     7     3     Salt Water
    Edit     Delete     8     8     Sharks
    Edit     Delete     9     8     Small Critters
    Edit     Delete     10     9     Shrimp
    Edit     Delete     11     9     Crab
    Edit     Delete     12     9     Lobster

[download]
My structure is something like.. 
               Animals
             /         \
           Mammals      Fish
          /       \    /          \  
         Dog      Cat Fresh Water  Salt Water
                      /           /         \         \
                                 Sharks     Dolphins   Critters
                                                       /     |    \
                                                     Shrimp  Crab  Lob
+ster

(sorry about my crappy ascii art

[download]
How would I go about printing this out in such a way I can see all the paths of each category in an assorted way?

I need it to be alphabetized by the root-most categories first, then the next, and next but all have to be a-z order.

Specifically I'm trying to add this to an HTML form field where each of them would display as <options> with each sub category bulleted/indented a little more than the last.

I saw some example code on dev mysql to go through it using OUTTER and INNER JOINS but they're assuming you know exactly how many categories you have in your table. Assume you have no idea because there could be an ininite depth in real life.

I appreciate all your help on this. I think I'm getting a little closer to what I need

In reply to Database categories by coldfingertips

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.