function help

mmaserati

I want to display the users in a table only sorted by their mentor. What is a mentor ? In my application every person must have a mentor and anyone can be someone else's mentor. I need to display a person and all the people they are mentoring as well as those people all the way down line.

So each record has 3 fields
Quote:
Name - text
ID - auto increment/generated number (ALA recordID)
mID - This is your mentors id

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

with 1 being my record id and 0 being my mentors id record (or my mID)

now lets suppose I have 5 students the records would be

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.

any and all help will be greatly appreciated

here is my code but it only displays the name of the current logged in person mentor instead of all the student as well as the students / students.

I am so close and so frustrated. thanks in advance for the help :o)


session_start();
	if(!session_is_registered(id)){
	header("location:/login.php");
	}
	include("condb.php");


$mentor_id = $_SESSION['id'];


//	echo "I am the mentor and my id is: $mentor_id ";

function displayStudents($mentor_id, $indent)
{
    $sql = "SELECT * from users WHERE mID = '.$mentor_id.' ORDER BY name ";
    $res = mysql_query($sql) or die(mysql_error());

while($row = mysql_fetch_assoc($res))
{
    echo str_repeat('&nbsp;',$indent).$row['name'].'<br />'; 
    displayStudents($row['ID'],$indent+1);
}
}

displayStudents($current_user_id,0);

NogDog

I think you could do a UNION and get everything in one query:

SELECT * FROM users WHERE ID = $id UNION SELECT * FROM users WHERE mID = $id

mmaserati

NogDog wrote:
I think you could do a UNION and get everything in one query:
SELECT * FROM users WHERE ID = $id UNION SELECT * FROM users WHERE mID = $id

Thank you SO much for a reply...

I ran that with my ID hard coded '5' in MySQL and
That only gets My Students though.

If one of my students has a student I need to display that as well. That is what the indent was supposed to do.

I am sorry if I am confusing, I am just at a loss here. :bemused:

NogDog

Ah, so you want to parse the entire hierarchy tree for a given users. Check out this article for a popular method of organizing your data table to facilitate this. (Otherwise, with the current database implementation, you'd have to write some inefficient recursive function to do the parsing in your application code.)

mmaserati

NogDog wrote:
Ah, so you want to parse the entire hierarchy tree for a given users. Check out this article for a popular method of organizing your data table to facilitate this. (Otherwise, with the current database implementation, you'd have to write some inefficient recursive function to do the parsing in your application code.)

yes I already visited that article as well as the others that compliment it and wrote a system that uses the left / right node designation and while this is good, the problem lies in moving people around in the 'tree'. It totally messes things up when moving someone from the bottom to the middle. This would be if someone dropped out of the program. The renumbering gets totally whacked out. Must be my crappy code LOL :queasy: