title: Using MySQL query to traverse rows to make a recursive tree
body: <p>I have a bill of materials table that is set up like this:<br>
  item - parent</p>
  
  <p>The end result when I display the bill of materials is that it is displayed like this:    </p>
  
  <pre><code>item 1  - parent 0    
      item 2 - parent 1    
      item 3 - parent 1    
  </code></pre>
  
  <p>The final result could also be multi level like this:    </p>
  
  <pre><code>item 3 - parent 0    
      item 4 - parent 3    
      item 76 - parent 3    
  </code></pre>
  
  <p>And it can go on ad infinitum:    </p>
  
  <pre><code>item 76 - parent 0    
      item 46 - parent 76    
  
  item 46 - parent 0     
      item 25 - parent 46
  </code></pre>
  
  <p>Right now, I either just get 1 level from the database:</p>
  
  <p><code>SELECT * FROM bom WHERE parentId = $itemId (shorthand)</code> </p>
  
  <p>Or pull every row from the table and use my recursive function to sort out just the ones I need, but this is obviously inefficient as I may only need 10 rows, but I pull 10,000 records.  The output of the recursive function will just create a tree like this:</p>
  
  <pre><code>item 1
     item 2
     item 3
        item 4
        item 76
           item 46
              item 25
  </code></pre>
  
  <p>All I know is that I am starting at item 1.  Item 5 could have a parent of 11; they do not have to go sequential.  I want to get all of the child branches in the tree.  How could I do this query in mysql?</p>