Tree Structure Question.

[marcobr]

Hi!

Currently I'm using a tree like sections structure, e.g.

Table: sections
+--------+------------+----------+
| sec_id | sec_parent | sec_name |
+--------+------------+----------+
|      1 |          1 |     Home |
|      2 |          1 |		News |
|      3 |          2 |   Sports |
|      4 |          3 |   Soccer |
|      5 |          4 |	   Teams |
|      6 |          5 |	 Arsenal |
|      7 |          1 |	   World |
|      8 |          7 |	 England |
|      9 |          8 |	 Economy |
+--------+------------+----------+

Also I'm using the function below to cover the tree:

	function cover_tree(){

	$query = "select * from sections";

	$result = mysql_query($query);

	$i=1;

	while($row = mysql_fetch_array($result)){
		$sec_id[$i] = $row['sec_id'];
		$sec_parent[$i] = $row['sec_parent'];
		$sec_name[$i] = $row['sec_name'];
		while($sec_parent[$i] > 1) {      
			$query = "select * FROM sections where sec_id = $sec_parent[$i]";
			$subresult = mysql_query($query);
			$subrow = mysql_fetch_array($subresult);
			$subsec_parent[$i] = $subrow['sec_parent'];
			$subsec_name = $subrow['sec_name'];
			$sec_name[$i] = $subsec_name." > ".$sec_name[$i];
		}
	$i++;
	}

	if($sec_name){
		for(reset($sec_name); $key=key($sec_name); next($sec_name)) {
			$sections_tree[$sec_id[$key]] = $sec_name[$key];
		}
	}

	return $sections_tree;
}

The above function will return:

Home
News
News > Sports
News > Sports > Soccer
News > Sports > Soccer > Teams
News > Sports > Soccer > Teams > Arsenal
World
World > England
World > England

World > England > Economy

I would like to see only subsections below the section Sports, (sec_id = 3), thus my sections_tree should be represented,

---

Sports
Sports > Soccer
Sports > Soccer > Teams

Sports > Soccer > Teams > Arsenal

The question is:

How could I improve this function to show only subsections below a pre-determined section???

Any ideas!?!?

Kindest Regards,

marcoBR

BTW, sorry 4 my english ...

[Weedpacket]

I'd guess by inserting a WHERE clause in your first query.

[marcobr]

Hum... like:

$query = "select * from sections where sec_id = '3'";

Nope! I already did it, but the results is a single line,

---

Sports

Another idea???

[Weedpacket]

Oh, you'll need to get those whose parents have id=3 as well! And the same for grandparents, greatgrandparents... And so forth.

Well, that would be a pain to do. Suggest adding another column to the table, sec_ancestor say, which contains the id of the top-level ancestor (or rather, the second level, since the top level is "Home"), then search on that.

+--------+------------+--------------+----------+
| sec_id | sec_parent | sec_ancestor | sec_name |
+--------+------------+--------------+----------+
|      1 |          1 |            1 |     Home |
|      2 |          1 |            2 |     News |
|      3 |          2 |            2 |   Sports |
|      4 |          3 |            2 |   Soccer |
|      5 |          4 |            2 |    Teams |
|      6 |          5 |            2 |  Arsenal |
|      7 |          1 |            7 |    World |
|      8 |          7 |            7 |  England |
|      9 |          8 |            7 |  Economy |
+--------+------------+--------------+----------+

If that's not an option, off-hand I can't think of any better approach than to read the whole tree in and traverse the appropriate bits.