Database and ASP loops and hierarachal Data

[whitmarshdaniel]

I have a employee table that includes there username and the username of there manager.

In one section of the site i need to be able to grant access to only people within certain chains. for example

at the moment when a user is logged on i do an sql call that says select * from ... where line_man_id = session (myId) that will then find all my staff.

i need to take this one step futher and work out who is there staff if any. for example i am incharge of robert and andrew, and andrew is in change of craig and pete.

I need to see all the data for all these people, andrew needs to see data for craig and pete and so on.....

Any Help, much appreciated..

Dan

[zippee]

Seem you are working on multi-level marketing chart!! :cheesy:
You can use looping function to call database repeatedly:

' user A login 
userid = Request("userid")
' query database for user A
counter = 1
select username from ... where user_id = "&userid&"' "
...
While Not RS.EOF
  downline = RS("username")
  response.write "staff: " & downline
  counter = counter +1
RS.MoveNext
WEND

For x = 1 to counter
' repeat the database query here
' ... 
Next

'if you are going down further, repeat the looping

Alternatively you may use array function to store "downline" name and query the database using
user_id = "&staff[0]&"' OR user_id = "&staff[1]&"' OR ...

[whitmarshdaniel]

how do i kno how many loops i need...

for example....

There is one table that has 6 fields one of which is Userid, another deptid and another is linemanid

so dan could be

userid=134
deptid=2
linemangerid=123

dan could have staff...

dave

userid=158
deptid=2
linemangerid=134

rob

userid=162
deptid=2
linemangerid=134

... rob could have staff

jon

userid=198
deptid=2
linemangerid=162

Andy

userid=205
deptid=2
linemangerid=162

Dan needs to see all these people, Rob can see jon and andy, and andy and jon can just see their own

so the start point would be

select * from table where linemanagerid=session("userid")

so.. for dan this would return dave and rob. so i now need to work out how dan can see jon and andy through rob.. and if jon and andy were line managers thier people, so the loop could be very long of very short...

does that make any more sense?