CODEWITHSUNDEEP
phpmysqltreeviewparent-child
SK Developers
SK Developers

Reputation: 64

count child from parent child php mysql 5 level deep

Folks,
I am badly in need of counting every member on each level I have 5 level deep Commission model so I am able to count the first level but not able to count whole team a person have in his / her down line. I want to count how many members on each level like
on 1st level 2 members, second level 34 members, third level 7 members etc. I am sharing my sql table here

id fname rid cnt

1 NetCash 0 3

2 Mian Salman 1 5

3 Zeeshan Murtaza 1 2

4 Usman Liaqat 1 2

5 Javed Iqbal 2 1

6 Faysal Islam 2 0

7 Waqas Pervaiz 3 0

8 Bashir Ahmad 5 0

9 Mirza Amir 2 0

10 Tauqeer Saghir 2 4

11 Asif Butt 4 0

12 Abdul Qayum 10 0

13 jhangir abbas 10 1

14 Ansar Mahmood 13 0

15 M USMAN KAYANI 10 0

16 Jabir Hussain 2 0

17 Abdul Rehman 10 0

18 Adnan javed 3 1

19 muhammad irfan 18 0

20 khurram 4 0
I need to count the whole number of members for Parent id / rid = 1 on 1st level , 2nd level ... up to 5th level.
I am able to count on first level like
SELECT COUNT(*) AS counts FROMuser_tableWHERE rid = 2
if you need any clarification please comment i will explain.

Upvotes: 0

Views: 773

Answers (2)

SK Developers
SK Developers

Reputation: 64

WITH RECURSIVE MemberHierarchy AS (
    -- Base case: start from the parent ID (rid = 1)
    SELECT 
        id AS member_id, 
        rid AS parent_id, 
        1 AS level
    FROM 
        user_table
    WHERE 
        rid = 1

    UNION ALL

    -- Recursive case: find members whose rid matches the current level's member IDs
    SELECT 
        ut.id AS member_id, 
        ut.rid AS parent_id, 
        mh.level + 1 AS level
    FROM 
        user_table ut
    INNER JOIN 
        MemberHierarchy mh ON ut.rid = mh.member_id
)
-- Count the number of members at each level
SELECT 
    level, 
    COUNT(*) AS member_count
FROM 
    MemberHierarchy
GROUP BY 
    level
ORDER BY 
    level;

Explanation of the Query Recursive CTE (WITH RECURSIVE MemberHierarchy):

The base case starts with the rid = 1 (parent). The recursive case joins the table with itself to find all members whose rid matches the current level's member_id. Level Tracking:

Each recursive iteration increments the level by 1. Final Query:

The SELECT statement at the end groups the data by levels and counts the members in each level. Ordering:

The results are ordered by the level to give a clear hierarchy. Example Output for rid = 1

Based on your data, the output would look like this:

 Level Member Count
    1   3
    2   8
    3   4
    4   2
    5   1

If you need a more detailed breakdown (e.g., listing members at each level), the query can be adjusted to include member names or other attributes.

Upvotes: 0

user3033467
user3033467

Reputation: 1188

Unless I missed something, you simply could use a GROUP BY

SELECT COUNT(id) AS counts, rid AS level FROM user_table WHERE rid<6 AND rid>0 GROUP BY rid

Upvotes: 1

Related Questions