Page 1 of 1

MySQL Hierarchy Recursion Help

Posted: Fri Sep 29, 2006 1:03 pm
by MrPotatoes
i have 1 table with this information in it:

Code: Select all

group_id 	group_parent 	group_name 
1	0	test_Adminys
2	0	test_moderat
3	0	test_editors
4	0	test_authors
5	0	test_members
6	0	test_vistors
7	1	__ForumsAdmin
8	1	__PluginsAdmin
9	2	__ForumsModerator
10	2	__PluginModerator
11	3	__BlogEditor
12	3	__NewsEditor
13	3	__ArticleEditor
14	3	__ReviewsEditor
15	4	__BlogAuthor
16	4	__NewsAuthor
17	4	__ArticleAuthor
18	4	__ReviewsAuthor
19	5	__Subscribed
20	5	__FREE
21	6	__Regular
22	6	__NoProxy
23	6	__Hacker
24	23	Test Depth1
25	24	Test Depth2
26	25	Test Depth3
27	26	Test Depth4
28	27	Test Depth5
i'm using this code to try to recurse thru it to get the information:

Code: Select all

function showgroup($gnum, $lev)
	{
		$q = mysql_query(
					"SELECT * FROM groups 
					WHERE group_parent=$gnum");
		
		while ($q2 = mysql_fetch_array($q)) 
		{
			$space = "   ";
			$ttl = '';
			
			for ($i = 0; $i < $lev; $i++) 
				$ttl .= $space;

			echo $ttl. .$q2['group_name'];
			echo "<br />";
			$ttl .= $space . $space;
			
			$links = mysql_query(	'SELECT * FROM groups 
									WHERE group_parent=' . 
									$q2['group_parent']);

			while ($links2 = mysql_fetch_array($links)) 
			{
				echo $q2['group_name'];
				echo "<br />";
			}
			showgroup($q2['id'], $lev+1);//*/
		}

		if (mysql_num_rows($q) <= 0)
			return(0);
	}
i got that from a zend tutorial but it's not going to work for me it's for 2 tables, i have one table and keeping it that way. basically this is just about the code that i had going thru my stuff originay and what i planned to do but i still can't seem to figure it out well enough

any ideas as to howt o fix this?

thanx :D :D :D

Posted: Fri Sep 29, 2006 4:33 pm
by feyd
There's a link in one of the stickies (it doesn't exactly make sense where it is, don't ask) that hits an article on Sitepoint about hierarchical strucutures in a relational database. There's also an article on MySQL's developer site regarding the same material.

Posted: Fri Sep 29, 2006 4:34 pm
by MrPotatoes
haha, i just about figured it all out. once i've got it down i was gonna put in "solved" and then post my code for anyone else. i'll check out those links tho