Hello guys, I am a new learner in PHP and I am modifying a simple binary site.
I am here to ask help how can I calculate my downline pairs or the total left and right side of
some user.

Here is the example of my tables in database:

+++++++++++++++++++++++++++++++++
| childID | parentID | position +
+++++++++++++++++++++++++++++++++
| Admin   | NULL     | NULL     +
| First   | Admin    | lft      +
| Second  | Admin    | rgt      +
| Third   | First    | lft      +
| Fourth  | First    | rgt      +
| Fift    | Second   | lft      +
| Six     | Second   | rgt      +
| Sevent  | Third    | lft      +
| Eight   | Third    | rgt      +
| Ninth   | Fourth   | lft      +
| Tenth   | Fifth    | rgt      +

I have three tables consist of childID or their username, parentID or their Sponsors/Referral, and
position for their position which is lft(left) and rgt(right).

What I am planning to do is, I want to show the user (or childID) their
total left(lft) and right(rgt) downlines.

For example, I want to get the total of all downlines of the childID (First) from left and right
base on my table structure and show it to the user (First).

Hope someone can help me about this. Thanks in advance.

Member Avatar for diafol

Not sure if you'd find "Modified Preorder Tree Traversal" easier than this.

Example...

-----------------
txt   | lft | rgt
-----------------
Admin    1     22
First    2     15 
Second  16     21
Third    3      8
Fourth   9     14 
Fifth   17     18
Sixth   19     20
Seventh  4      5
Eighth   6      7
Ninth   10     11
Tenth   12     13

That way "nodes" are bound by the left and right:

For example seventh = 4 and 5, so to get the breadcrumb, you...

SELECT txt FROM tbl WHERE lft <= 4 AND rgt >= 5 ORDER BY lft

This will retrieve...

ADMIN
FIRST
THIRD
SEVENTH

I think if you use your table structure, you'll end up having to use recursives, which can be slooow.

HERE'S A GRAPHIC:

e2330ab1fbe59978e50992ccffdf3d55

Member Avatar for diafol

BTW - ridiculously easy to update too. When you insert a new item - you just add 2 to left and right values >= than the item left number (e.g. add ELEVENTH to FIFTH - this new item would be 18-19. Add 2 to everything >= 18. Remember not to add the new item until after you update, or you'll end up updating that too!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.