Page 1 of 1

ordering a heirarchal list from a bunch of records

Posted: Thu Sep 14, 2006 6:25 pm
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...

Posted: Thu Sep 14, 2006 6:43 pm
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 :)

Posted: Thu Sep 14, 2006 6:55 pm
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. :?

Posted: Thu Sep 14, 2006 10:01 pm
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 :)