ordering a heirarchal list from a bunch of records

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

ordering a heirarchal list from a bunch of records

Post by Luke »

I have a table with columns like this:

Code: Select all

id => 333
               parent_id => 2
               agrpcount => 0
               disp_order => 334
               code => PL-OD
               name => Outdoors
               active => 1
What I want to do is display these in a heirarchal list... I need some sort of functionality that can reorganize this one-dimensional array into a multi-dimentional array. I know this isn't difficult... in fact I've done it before, but for some reason I can't remember how, and my brain is exhausted due to lack of sleep. I bet as soon as I post this I'll try again and then have to post that I'm a moron again, but here goes...
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Post by alex.barylski »

DB table:
=======

pkid, parentid, name

Code: Select all

$catid = (int)$_GET['catid'];

$res = mysql_query('SELECT * FROM table WHERE catid = '.$catid);

// TODO: Display results for selected category
Did you mean display it in a structure fashion???

Code: Select all

define('ROOT', 0);
function showAll($catid=ROOT)
{
  static $depth = 0;
  $catid = (int)$catid;

  $res = mysql_query('SELECT * FROM table WHERE catid = '.$catid);

  for($i=0; $i<count($res); $i++){

    $row = mysql_fetch_array($res);
    echo '<div style="padding-left: '.$depth.'px">'.$row['name'].'</div>';

    $depth++; 
    showAll($row['catid']);
    $depth--; 
  }
}
Or something to that effect...I hope that helps get you started anyways...you could use a stack counter and avoid recursion as well as return the results as an array which is passed to a template engine, but this nails the idea home nicely and easily IMHO.

HTH

Cheers :)
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

I would prefer not to query the database on every iteration... for some reason i just can't work this out in my head. It seems simple enough... but my brain just isn't doing it. :?
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Post by alex.barylski »

Not query the database on every iteration...

Well...you would then need to use the SQL "IN" statement I think...although I'm not sure that would actually be faster than iteratively callingtrivial SELECT's

Using this approach still requires you to build a list of children and parent relationships...sure I dunno how you did it in the past...did you use MySQL???

http://www.evolt.org/article/Four_ways_ ... index.html
http://www.sitepoint.com/article/hierar ... a-database

MySQL:
http://www.xaprb.com/blog/2006/04/30/ho ... -in-mysql/

Perhaps the latter of the listing would meet your criteria???

Cheers :)
Post Reply