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
