MySQL Hierarchy Recursion Help

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
MrPotatoes
Forum Regular
Posts: 617
Joined: Wed May 24, 2006 6:42 am

MySQL Hierarchy Recursion Help

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
MrPotatoes
Forum Regular
Posts: 617
Joined: Wed May 24, 2006 6:42 am

Post 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
Post Reply