MySQL Query Assistance (for PHP Output)

Question

I have a table that lists a hierarchy of team members for an MLM. I am trying to figure out how to query the table most efficiently in MySQL so I can output the results in PHP.

I have learned in research that nested queries or nested queries in PHP while loops are not ideal, yet when I look at MySQL joins, I don't see how to access the information correctly.

Table Values (Table Name is MLM):

ID  Upline   FName

1     1      Joe    (he is at the top of the hierarchy)
2     1      Jane   (Jane is directly under Joe)
3     2      Jack
4     3      Jill

To find out the names of all these people reporting under Joe?

Select FName from MLM where Upline = 1;
and
Select FName from MLM where Upline in (Select FName from MLM where Upline = 1);
and
Select FName from MLM where Upline in (Select FName from MLM where Upline in (Select FName from MLM where Upline = 1));

This isn't good to start, and not knowing how many levels will be under Joe, it isn't scalable.

Can you please let me know how I can best query this information or combine a PHP mysql_fetch_array in a while loop to find and echo all the values?

ajacian81 · Accepted Answer · 2012-10-02 03:53:48Z

1

Not commonly known, but you can join a table on itself. You will have to give each instance a seperate alias so that you can refer to them as required.

The following query will retrieve all the people with the same Upline as Joe:

SELECT m2.* FROM MLM m1
JOIN MLM m2 ON m1.Upline = m2.Upline
WHERE m1.FName LIKE "Joe"

If you only want the people reporting to Joe, and not Joe himself, you can do something like this:

SELECT m2.* FROM MLM m1
JOIN MLM m2 ON m1.Upline = m2.Upline
WHERE m1.FName LIKE "Joe"
AND m1.FName <> m2.FName

answered Oct 2, 2012 at 3:53

ajacian81

7,6299 gold badges54 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1713438 Over a year ago

Thank you for showing me the direction to go in... I will study self-joins and see if I can get this to work.

ajacian81 Over a year ago

No problem, good luck :) Let me know if you need any more help.

prajeesh kumar · Accepted Answer · 2012-10-02 05:01:49Z

0

Representing hierarchical data in database is tricky. You can think about either of the following :-

Re design your table such that it is easy to identify the hierarchy - Check this link for better understanding of the limitations and problems with subquery hierarchy Managing Hierarchical Data in MySQL OR
Select * from the table and in your php code create the hierarchy by iterating through the results.

Other than this, if you go on for subqueries on subqueries, you are not going to be anywhere near a practical solution.

answered Oct 2, 2012 at 5:01

prajeesh kumar

1,9761 gold badge17 silver badges17 bronze badges

1 Comment

user1713438 Over a year ago

Thank you for the link. I just purchased Joe's book and will study hierarchical data models.

Collectives™ on Stack Overflow

MySQL Query Assistance (for PHP Output)

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related