Looping on two column data to create structure

[chero]

Hi,

I've been trying to figure this out all day now, and haven't had any luck. Here's what I'm trying to do:

I have a 'folders' table in a database like this:

+-----------------+---------------+
| id | parent_folder |
+-----------------+---------------+
| Scotland | |
| Argyll | Scotland |
| Ayr | Scotland |
| Lanark | Scotland |
| Wigtown | Scotland |
| Test | Lanark |
| Test_Sub_Folder | Test |
+-----------------+---------------+

This is supposed to represent something like this:

Scotland
Argyll
Ayr
Lanark
Test
Test_Sub_Folder
*Wigtown

I'd like to be able to retrieve the data from the database, and basically create a tree structure based on the data.

Can anyone give me any clues as to how to code this? I'm about at the end of the road with this.

(My thought on how to do this has been like this: For each id, display any rows where parent_folder = id... but this has to be able to traverse, and then get back out again...)

James

[Phodetheus]

I had to do exactly the same thing with a job I am working on now. Solved it by using a self replicating class to output each level.

Phodetheus

[chero]

I'm not sure what a self replicating class is but I'll try to read up on it. Thank you for your response!

[Phodetheus]

It is a class that calls iteself however many times it needs to. Here is the code I have used, hopefully, reading it will put you on the right track, its probably a bit more complicated than you need because it is dealing with restricted menu items but you should get the idea from it.

Let me know how you get on.

<?php

class shownav {
	var $curlevel;

	function disp($level,$navarray,$db,$indent,$sess){
		// get the access groups
		$accessarray=explode(",",$sess->accessgroup);
		$this->curlevel=$level;
		// get the menu items for this level
		if($items=$db->get_results("SELECT id, title, calledfrom, displaypos, page, accessgroup FROM navigation WHERE calledfrom='$this->curlevel' AND demo<='$sess->demo' ORDER BY displaypos")){
			foreach($items as $item){
				// check this is not a restricted item
				if($item->accessgroup==0 OR in_array($item->accessgroup,$accessarray)){
					//and if not display it
					echo "<tr><td NOWRAP>";
					if($indent>0){
						//indent this menu item if necessary
						for($f=0;$f<$indent;$f++){
							echo "<img src=\"images/spacer.png\">";
						}
					}
					// update our session variables ready for the link
					$sess->page=$item->page;
					$sess->nav=$item->id;
					$sess->updatesess();
					echo "<a href=\"goto.php?sess=$sess->sess\" title=\"$item->title\"";
					//if we are shwoing a restricted item change its colour
					if($item->accessgroup!=0 AND $item->id!=$sess->curnav) echo " style=color:#FF0000; ";
					//if we are shwoing the current menu item change its colour
					if($item->id==$sess->curnav) echo " style=color:#000000; ";
					echo ">$item->title";
					echo "</a></td></tr>";
					if(in_array($item->id,$navarray)){
						// if this is in our list of display levels display it
						$newset=new shownav;
						$newset->disp($item->id,$navarray,$db,$indent+1,$sess);
					}
				}
			}
		}
	}	
}

$curlevel=0;
// create array for each sublevel back to 0 so we know which sub menue to show
$navarry=array();
$curlevel=$sess->nav;
while($curlevel!=0){
	$navarray[]=$curlevel;
	// get the previous level
	$curlevel=$db->get_var("SELECT calledfrom FROM navigation WHERE id='$curlevel'");
}
?>
<table cellpadding=0 cellspacing=0>
	<?php
	//start at level 0
	$navarray[]=0;
	$newset=new shownav;
	// show the menu
	$newset->disp(0,$navarray,$db,0,$sess);
	?>
</table>
		

Phodetheus