How to sort out the referral structure with an algorithm?

Question

PHP

Hey! I tried a lot of things, but to no avail, I want to ask for your help, friends!
There are 2 tables in mysql - users and user_referrals .
In users the columns are id, login, ref. There are entries with an empty ref.
Example:

• id, login, ref
  
• 1, ktest, ''
  
• 2, zalm, ''
  
• 3, koks, ktest
  
• 4, masster, ktest
  
• 5, joker, ktest
  
• 6, texnar, joker
  

Regular referral. You need to iterate over the entire users table and collect it in user_referrals.
In user_referrals the columns are user_id ( id for login ), refer_id ( id for ref ), level ( nesting level ).
And now you need to iterate over the entire users table so that rows like this are written to user_referrals:

• user_id, refer_id, level
  
• 3, 1, 1
  
• 4, 1, 1
  
• 5, 1, 1
  
• 6, 5, 1
  
• 6, 1, 2
  

8 levels for each refer_id.
Hope you give me some ideas, thanks!

Answer 1

Use nested set devacademy.ru/posts/nested-set

Answer 2

MySQL does not have recursive queries, so one simple query will not get rid of it.
So here you are , for example.