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
      }
}