Solved

Sorting MySQL Results

Posted on 2006-11-24
9
2,791 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
Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
This article discusses how to implement server side field validation and display customized error messages to the client.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

626 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