Solved

Sorting MySQL Results

Posted on 2006-11-24
9
2,787 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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:John Kawakami
John Kawakami 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:John Kawakami
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:
John Kawakami 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article discusses four methods for overlaying images in a container on a web page
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
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 create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

710 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