Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sorting MySQL Results

Posted on 2006-11-24
9
Medium Priority
?
2,797 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
Are You Using the Best Web Development Editor?

The worlds of web hosting and web development are constantly evolving. Every year we see design trends change, coding standards adapt and new frameworks/CMS created. With such a quick pace of change it’s easy to get lost trying to keep up.

See if your editor made the list.

 
LVL 33

Assisted Solution

by:sajuks
sajuks earned 800 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 1200 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 1200 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

7 Extremely Useful Linux Commands for Beginners

Just getting started with Linux? Here's a quick start guide that has 7 commands that we believe will come in handy.

Question has a verified solution.

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

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
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…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

730 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