Sorting MySQL Results

Hello! I have a mysql table which holds a list of categories and sub categories. The level of sub-categories is not limited.
What I'd like to do is display a category tree inside a select box like this for example:

Web Servers
-- Apache
-- IIS
Web Dev
-- PHP
--- PHP & Databases
--- PHP for Windows

The mysql table structure is like this:

category_id
parent_id
category_name

Would I use a mysql query for this or let PHP sort the data ?
Help would be appreciated!
LVL 21
Julian MatzJoint ChairpersonAsked:
Who is Participating?
 
John KawakamiConnect With a Mentor Commented:
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>';

?>
0
 
sajuksCommented:
I would be using MySQl to sort the data in the hierarchial format that i need.
PHP i would be using only for  display.
0
 
Julian MatzJoint ChairpersonAuthor Commented:
Any tips on how to do it ?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
sajuksCommented:
can u post the table data. Do you have more than one hierarchy ?
0
 
sajuksConnect With a Mentor Commented:
Theres a good example at
http://www.sql-server-helper.com/functions/get-tree-path.aspx
Have a look at it.
0
 
John KawakamiConnect With a Mentor Commented:
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
0
 
ictinusCommented:
how are the tables put together?  

do each of the tables have unique ids?  if so then you can simply use a couple of select statements and loops through with php to get the desired list.  seems like if you built a serious hierarchy query you would still have to display it with php.  there might be a few extra lines if you do it in php but...

can you post an example of what columns are in the tables?  so the parentid table has what? just a name or a name and a uinique id?  
0
 
John KawakamiCommented:
here's a technique.  Read the entire table into an array, and do all the work in PHP.

<?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 menuToHtml( $id )
{
   $ar = selectWhereParentIdIs( $id );
   if ( count($ar) > 0 )
   {
      $out .= '<ol>';
      reset( $ar );
      foreach( $ar as $value )
      {
         $out .= '<li>'.$value['name'];
         $out .= menuToHtml( $value['id'] );
         $out .= '</li>';
      }
      $out .= '</ol>';
      return $out;
   }
   else
   {
      return '';
   }

}

echo menuToHtml( 0 );

?>
0
 
Julian MatzJoint ChairpersonAuthor Commented:
Sorry - had to put this project on hold for a while, but thanks for the information so far!
I will try this again after Christmas :)
0
All Courses

From novice to tech pro — start learning today.