0

I have a table :

id  type_name               type_reportToId 
1   Administrator               0   
2   Guest   GUEST               1   
3   Landlord                    1   
4   Property Manager            3   
13  Manager Manager             3   
14  Asst. Property Manager     13

I need a result of "report to hierarchy" for id No 14. eg: id 14 has to report 13,13 has to report 3,3 has to report 1 ...like wise. I need a query for that in mysql. Can anybody help me ?

Philip Sams
  • 29
  • 8
  • possible duplicate of [Recursive categories with a single query?](http://stackoverflow.com/questions/3116330/recursive-categories-with-a-single-query) – DCoder Nov 13 '12 at 06:36
  • Without any php manipulation there is no other way to do this ? – Philip Sams Nov 13 '12 at 06:44
  • If only could people give an example of what they wanted, we might actually be able help them. And now they slam some words instead of making an example of the expected results based on the data sample. Why even waste time if you cannot be bothered explaining properly? – Andrius Naruševičius Nov 13 '12 at 07:16
  • Mr Andrius R u not able to undersstand what i have given above ? – Philip Sams Nov 14 '12 at 07:23

1 Answers1

0

You preferably do that in your application logic rather than in your query.

Pseudo code

query=SELECT id, type_name, type_reportToId FROM table ORDER BY id ASC, report_toId ASC

// loop through rows
currentParent = 0
processed = 0
hierarachyLevel = 0
while ( processed < foundrows ) 
{
     while ( row = fetchrow( dbResult ) )
    {
         if ( row[type_reportToId] == currentParent )
         {
             resultHierarchy[hierarchyLevel] = row
             currentParent = row[id]
             processed++
             hierarchyLevel++
             dataseek( dbResult, 0 )
             break
         }
   }

}

Michel Feldheim
  • 17,625
  • 5
  • 60
  • 77