MySql PHP tree view

Question

I am building a web application for an affiliate program and on the users table I have this structure:

Based on this MySql table I want every user to be able to see his tree, so I need this query:

1. The user to see who recruited him and who was recruited by him and I don't want the query to show other users that are recruited by the same recruited that recruited this user. Basicly it should look this way:

Answer 1

if your table structure really looks like this you ned to define a function or easier do it in a recursive lookup in your language (php), but its a really inefficient way. read this article it deals with this recursive and other teqniques.

it explains storing of hirachical data in databases better than i could: http://articles.sitepoint.com/article/hierarchical-data-database