recursive sql

[theS70RM]

Hi, I have a table thats laid out something like the following....

cat_id, cat_name, cat_parent
1		furniture		  null
2		electrical		  null
3		tvs		   2
4		desks				  1
5		corner desks   4       
6		sony tvs		3
7		wardrobe		1

my question is, what's the best way of finding the full category list given 1 category id. I really want to return the result as an array of category names, with the category id's being the array keys.

currently im trying to make a recursive function, but i thought maybe there is a better (more efficient?) way using a do while() loop. Or even a way of recursing with an sql statement?


Thanks for any help!


Andy

[r035198x]

Hi, I have a table thats laid out something like the following....

cat_id, cat_name, cat_parent
1		furniture		  null
2		electrical		  null
3		tvs		   2
4		desks				  1
5		corner desks   4       
6		sony tvs		3
7		wardrobe		1

my question is, what's the best way of finding the full category list given 1 category id. I really want to return the result as an array of category names, with the category id's being the array keys.

currently im trying to make a recursive function, but i thought maybe there is a better (more efficient?) way using a do while() loop. Or even a way of recursing with an sql statement?


Thanks for any help!


Andy

Could you post an example? e.g What should the result be when the id given is, say, 3?

[theS70RM]

Could you post an example? e.g What should the result be when the id given is, say, 3?

sure,

if given the value 6, it should return an array of...

myarr['6'] => "sony tvs"
myarr['3'] => "tv's"
myarr[2] => "electrical "


you follow?

[r035198x]

sure,

if given the value 6, it should return an array of...

myarr['6'] => "sony tvs"
myarr['3'] => "tv's"
myarr[2] => "electrical "


you follow?

So you want to do this using a PHP function?
I'm not good with PHP but your agorighm will be something like

getTreeArray(int cat_id) {
		select  cat_id, cat_parent,cat_name  from tableName into cat, parent,name
		 array[cat_id] = name
		while(parent != null) {
				select  cat_id, cat_parent,cat_name  from tableName) into cat, parent,name
			   array[cat_id] = name
	  }
}