Array sorting / MYSQL problem

[magpie]

Hi guys, this is my first post so be nice!

 

I have an SQL select which returns the following

 

cat_id

cat_name

parent_id

1

cat 1

0

2

cat 2

0

3

cat 3

0

4

cat 4

0

5

cat 5

1

6

cat 6

1

7

cat 7

0

 

The above shows that category 1 has 2 children.

 

I have a script in place that basically filters the child entries into a new array, then I use two foreach loops to build a list of items with parent > child relationship.

 

My code works, but is a mess and doesn't 'feel' as concise as it should be.

 

What would be the best / most efficient way to build a child > parent list. (ideally i would want it to work on up to 4 tiers)

 

Any help would be greatly appreciated.

 

Cheers

 

Dave

[magpie]

Anyone... ???

[monkeytooth]

Find your highest "cat_id"

then run it through a foreach style statement. For each "cat_id" count "parent_id" that matches. Or as I assume where parent_id is Greater Than 0. Have a conditional in the the foreach where if it is 0 then display "none" next to the cat_name

[magpie]

Thats it... I'll have to paste my laughable code in here, the following 'works' but im sure it is far from best practice.

 

$sectorList = array();

$subSectorList = array();

$specificSubSectors = array();

 

foreach($query->result as $row) {

if ($row['parent_id'] == 0 ) {

$sectorList[] = array('sector' => $row['cat_name'], 'id' => $row['cat_id'], 'parent' => $row['parent_id']); // Build sector list

} else {

$subSectorList[] = array('sector' => $row['cat_name'], 'id' => $row['cat_id'], 'parent' => $row['parent_id']); // Build sub-sector list

}

}

 

foreach ($sectorList as $sector) { // Loop sector list

echo $li."<a href=\"".$template."/".$queryString."\">".$sector['sector']."</a>";

if ($subSectorList) {

foreach ($subSectorList as $subSector) {

if ($subSector['parent'] == $sector['id']) {

$specificSubSectors[] = array('sector' => $subSector['sector'], 'id' => $subSector['id'], 'parent' => $subSector['parent']);

}

}

}

if ($specificSubSectors) {

echo "<ul class=\"subSectors\">";

foreach ($specificSubSectors as $specificSubSector) {

echo $li."<a href=\"".$template."/".$queryString."\">".$specificSubSector['sector']."</a>";

}

echo "</ul>";

}

$specificSubSectors = array();

echo "</li>";

}