Sorting MySQL Results

Theres a good example at
http://www.sql-server-helper.com/functions/get-tree-path.aspx
Have a look at it.

IMHO, tables are the wrong data structure for this.  If you have the skills, use XML.

What I'd use is a very simple table structure, with a field, parent_id, that identifies the parent.  Select all the topmost nodes.  For each, select the children.  Repeat until all depths are plumbed, building up a tree as you go.  Convert the tree to HTML or XML, emit it to a file, and then load in that file instead of doing the query.

The initial query is very slow, but straightforward.  The subsequent "include" or file read is very fast.

Here are some other possible solutions:
http://www.sqlteam.com/item.asp?ItemID=8866  (I like this one)
http://www.sitepoint.com/article/hierarchical-data-database  (This is also good)

This one is scary, but Celko is a good author to read:
http://www.sqlsummit.com/AdjacencyList.htm

A scary one to skim:
http://rpbouman.blogspot.com/2005/08/yet-another-way-to-model-hierarchies.html

But, even knowing the above (and really studying the first two), I think that my original suggestion is still the best solution, for this reason:

 - Most hierarchical data structures don't change that often.  (If they do, then, the only proper model for storing them is in a hierarchical data structure.  A db table is the wrong storage.)

 - If data is written infrequently, but read frequently, then you should cache the output.

Why confuse the next programmer by doing something fancy?

I can't write the full code today - so maybe someone else can.  Here's some code that's in transition right now (meaning it doesn't really work), but used to create hierarchical menus.  It did not use a db table for storage, however -- I just wrote the menus up by hand.  http://riceball.com/drupal/?q=node/412

Update.  Here's a version that puts it into a select: http://riceball.com/drupal/?q=node/414

<?php
$menu = array();
$menu[] = array( 'id' => 1, 'parent_id' => 0, 'name' => 'a' );
$menu[] = array( 'id' => 2, 'parent_id' => 1, 'name' => 'a.1' );
$menu[] = array( 'id' => 3, 'parent_id' => 0, 'name' => 'b' );
$menu[] = array( 'id' => 4, 'parent_id' => 3, 'name' => 'b.1' );
$menu[] = array( 'id' => 5, 'parent_id' => 3, 'name' => 'b.2' );
$menu[] = array( 'id' => 6, 'parent_id' => 5, 'name' => 'b.2.1' );

// acts like a SELECT statement
function selectWhereParentIdIs( $id )
{
   global $menu;
   $out = array();

   for( $i=0; $i<count($menu); $i++ )
   {
      if ($menu[$i]['parent_id']==$id)
         $out[] = $menu[$i];
   }
   return $out;
}


function menuToSelect( $id, $depth )
{
   $ar = selectWhereParentIdIs( $id );
   if ( count($ar) > 0 )
   {
      reset( $ar );
      foreach( $ar as $value )
      {
         $out .= '<option>';
         if ($depth>0) $out .= '-';
         $out .= str_repeat( '-', $depth ) . $value['name'];
         $out .= '</option>';
         $out .= menuToSelect( $value['id'], $depth+1 );
      }
      return $out;
   }
   else
   {
      return '';
   }

}

echo '<form><select>';
echo menuToSelect( 0, 0 );
echo '</select>';

?>