How to make a referral counter in PHP?

Question

PHP

Guys, there is a multi-level referral system on the site. How to implement a referral counter and sub referrals?
Let's say Vasya registered on Petya's referral, and Kostya registered on Vasina's. It is necessary that the record that the number of Petya's referrals is 2 (two) gets into the database.
It is the logic of the code that is needed, I will write the code myself. Thanks in advance!

Answer 1

For starters: each user, in theory, should have an inviter field, or any other field, where the ID of the user who invited him is stored. Let's say:

| id | username | inviter |
|----|----------|---------|
| 1  | abc      | null    |
| 2  | abcd     | 1       |
| 3  | abcde    | 2       |
| 4  | abcdef   | 1       |

Next, we make a selection:

SELECT COUNT(*) FROM table_name WHERE inviter = {НОМЕР_ПОЛЬЗОВАТЕЛЯ}

This way we can count all the referrals of the user {USER_NUMBER} for the first deepening. For the second deepening (calculate the referrals of the user's referral), it seems to me that you need to do it through additional requests, but I don’t see the point in this because referrals are only counted to the first depth, they are never counted to the second depth. That is, if my referral has 10 referrals, then I still have one referral.

Answer 2

select count ....

Answer 3

Recursive crawl through the "tree" of referrals with an accumulative calculation.
Or.
When a new sect of a referral appears, add units to each higher in the hierarchy.