array, nested query ??

[ido3dfx]

I am new to this but here goes.

table has 3 fields:

ID  - auto increment record id

mID - used to identify someones mentor (if you are a mentor for someone their mID is your record ID)

Name - someones name

 

I need to display records based on a users id and their "mentors id" . The desired results would display all the people I am mentoring (lets call them students) as well as my students "students".

 

 

I can return the information with a simple select statement:

 

SELECT * where `id` = '1';

 

so since I am the first record  I am:   

 

  1 |  0

 

now lets suppose I have 5 students their records would be

 

1 | 0

    2 | 1

    3 | 1

    4 | 1

    5 | 1

    6 | 1

 

 

My need is to also get the students, students.

 

Lets say student 3 has 2 students and student 5 has 3 students.

 

I want to be able to have is display this:

 

2 | 1

3 | 1

    7 | 3

    9 | 3

4 | 1

5 | 1

      8 | 5

    10 | 5

    11 | 5

6 | 1

 

so I need to be able to display the entire lineage not just my students but all students. This way if student 3 views his record it display his record and his students:

 

3 | 1

    7 | 3

    9 | 3

 

 

I am staggering to show that these are students of the mentor.

 

I really look forward to any help and will be VERY appreciative.

 

 

 

[rhodesa]

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

[ido3dfx]

I currently use that theory with the whole left and right thing but it is too cumbersome to move someone.

 

that is why I want to base the hierarchy on just record id and mentorid.

 

 

If I move a mentor...all his students will move to and I dont have to renumber the entire database left and right numbers.

 

I have tried this but it fails...

 

function printLevel($id, $indent = '') {

 

  $result = mysql_query('SELECT * FROM t1m_users where `idspr` = ' . (int)$id);

  while ($row = fetch_row_assoc($result)) {

echo $row['username'] . "<br>\n";

printLevel($row['id'], $indend . '  ');

  }

}

printLevel(0);