I am having a horrible dilemma coding a downline for a mutli-level marketing system and I wanted to know if someone here could give me any suggestions or ideas I should follow. Basically what there is a MySQL database composed of hundreds of representatives and the database is based on an Agent Code. Each representative has a "sponsor_code" field which points to the parent's code. So basically an entry could look like this:
Code | Name | Sponsor_Code
C0101 | Person1 | A0203
H1122 | Person2 | C0101

So, I want to be able to put like a downline list that shows a tree format of the downline. So you would pick from where to start by putting in that person's code and it will generate a tree under him. What are your suggestions on how to do this? Would it be better to redesign the database or can this method be used?

Thanks for your input

If the tree had a fixed number of levels you might think about redesigning the database, but it sounds like the agents can refer to each other endlessly. There's going to be no way to get all the info you want in one query since there's no way of knowing how many levels you want.
When dealing with trees it is often a good idea to write a recursive function to do all the work for you, along the lines of:

PHP Code:

function print_branch($level, $code) {
  $result = mysql_query("select * from table where sponsor_code = '$code'");
  while($row = mysql_fetch_array($result)) {
    for($i = 0; $i < $level; $i++) echo "-";
    echo $row['Name'];
    print_branch($level+1, $row['sponsor_code']);
  }
}

function print_tree($code) {
  $result = mysql_query("select Name from table where code = '$code'");
  print_branch(0,$code);
}

print_tree('H1122'); 


It causes a lot of SQL queries but it does the job.

That is something that I was considering. I knew for a fact that this will not be able to be done in one query. I had done a recursive function but I put it all into an array instead of just making the function to print it and then I was trying to write another recursive function to print it. I noticed this was kind of stupid after I started having problems with it and I was making the problem harder than it looks. I think what you supplied me with could work. I'll have to test it but it looks like a great function

Thanks for your input!

Wow, thank you so much!
This worked perfectly.
I'm not too familiar with working with trees and I was starting to pull my hair out trying to figure this out. Thank you so much

This one is kinda basic since it prints a tree in the form:

level0
-level1
--level2
--level2
---level3
---level3
--level2
---level3
----level4
--level2
-level1
--level2

You can make it more elaborate to improve the formatting.