Solved

Sorting MySQL Results

Posted on 2006-11-24
9
2,779 Views
Last Modified: 2012-06-27
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!
0
Comment
Question by:Julian Matz
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 33

Expert Comment

by:sajuks
ID: 18010247
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
 
LVL 21

Author Comment

by:Julian Matz
ID: 18010265
Any tips on how to do it ?
0
 
LVL 33

Expert Comment

by:sajuks
ID: 18010310
can u post the table data. Do you have more than one hierarchy ?
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 33

Assisted Solution

by:sajuks
sajuks earned 200 total points
ID: 18010318
Theres a good example at
http://www.sql-server-helper.com/functions/get-tree-path.aspx
Have a look at it.
0
 
LVL 8

Assisted Solution

by:jk2001
jk2001 earned 300 total points
ID: 18012950
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
 
LVL 1

Expert Comment

by:ictinus
ID: 18013625
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
 
LVL 8

Expert Comment

by:jk2001
ID: 18013694
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
 
LVL 8

Accepted Solution

by:
jk2001 earned 300 total points
ID: 18013707
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
 
LVL 21

Author Comment

by:Julian Matz
ID: 18109524
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

Featured Post

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question