Mysql output for first row works fine, for other rows doesn't for downline counting

[biggdanta]

My forum has users which can refer other users. I store them in a table like
|UID|referrer|Referral|
|300||302,304|
|302|300|303|
|303|302|305,306,307|
|304|300|308|
|308|304|309|

Now Level 0, there is UID 300 at level A, there is 302 and 304 at level B, there is 303 and 308 at level C, there is 305,306,307 and 308.

Now I am expecting below code to show as total number as 4, but it shows only 3. Which means it only counts first tree(only level c referrals under 302 and not 304). I want to make it to work, please help.

<?php

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT group_concat(uid) FROM mybb_users WHERE referrer='300'";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {

  while($row = mysqli_fetch_assoc($result)) {
    $abc= $row['group_concat(*)'];   

    $sql = "SELECT uid FROM mybb_users WHERE referrer='$abc'";
    $result = mysqli_query($conn, $sql);

    if (mysqli_num_rows($result) > 0) {
       while($row = mysqli_fetch_assoc($result)) {
       $wbc= $row["uid"];
       
       $sql = "SELECT count(*) FROM mybb_users WHERE referrer='$wbc'";
       $result = mysqli_query($conn, $sql);
       if (mysqli_num_rows($result) > 0) {
         while($row=mysqli_fetch_assoc($result)) {
        echo $row['count(*)'];          }
    } else {
       echo "0";  
      }
      }
    } else {
       echo "0";
    }
    
  }
  
} else {
  echo "0";
}

                    ?>

Thanks for read and I appreciate any help in advance.

[Thallius]

You database design and also the db queries are really bad.

Please explain a little bit more in details what a „level“ is and how the users reference to each other.

Then we can offer you a useable solution.

[biggdanta]

my database has a table mybb_users and there are columns such as uid name email referrer and referral.

levels aren’t stored in database.
when a user referrers someone, lets say person 300 refers user 302 then 302 is level A for 300. then user 300 refers user 303, now 303 for 300 is level B, but for 302 he is level A.
Same way when user 303 refers another users 305, 306, 307 they are level C for 300, but for 302 they are lelvel b…and so on.

[Thallius]

Ok so you said you have a table with the columns

UID, referrer and referral.

For what do you need the referral? For me you only need UID and referrer.

What output do you expect? From the logic above, there is no limit in levels. So a user can have level a,b,c,d,e,f etc referrer. Which of them do you want to show?
And how should the result look like? One row with

UID, referrer lvl1, referrer lvl2, referrer lvl3 etc or many rows with UID, referrer, level?

[biggdanta]

I just want to show the output to have the total number of users at level C.
In above example, user 300 referred 302 and 304,
302 referred 303, who referred 305, 306 and 307.
where 304 referred 308 who referred 309.
Thus, for user 300, at level C there are 4 users, 305, 306, 307 and 309.
I tried it using above code example but it counts only users referred by 303 and ignores users referred by 304. So it’s showing 3, where it should show 4.
Pardon for my bad lingustic approach. I appreciate your time and replies, Thallius.
Here is an image which explains is better.
In other words, I want to show count of 305, 306, 307 and 308 which are level C members for user 300. (Considering 300 as level 0, 302 and 304 as level a, 303 and 308 as level b and 305, 306, 307 and 309 as level c) I want to display only total count of level C members for user 300. Once again, Thanks for the reply!

[m_hutley]

So what you are describing is called Hierachical Data.

Hierarchical Data in SQL: The Ultimate Guide

Here’s a good reference for ways that this data can be represented in a database, as well as its methods of selection, insert, moving, etc.

I want to show count of 305, 306, 307 and 308 which are level C members for user 300.

Lets assume you’re going to use the Adjacency List model.

Table refs columns
UID INT (PK), referrer INT

Table Data

309,308
308,304
304,300
307,303
306,303
305,303
303,302
302,300
300,NULL

Query

WITH RECURSIVE rlookup AS (
   (
    SELECT UID,referrer,1 as lev
    FROM   refs 
    WHERE  referrer IS NULL
   )
   UNION  ALL
   (
    SELECT this.UID, this.referrer, prior.lev+1
    FROM   rlookup prior
    INNER  JOIN refs this 
    ON     this.referrer = prior.UID
   )
)
SELECT UID,referrer 
FROM   rlookup 
WHERE  lev = 4;

Result:

UID	referrer
305	303
306	303
307	303
309	308