Mysql data in inheritance format using PHP

Question

I have a table in this order, i am trying to get data from this table where each row depends on the other to get the desired information i need.

ID USERNAME REFFERAL
1 user1 10
2. user2. 1
3 user3. 2
4 user4. 3
5. User5. 4

$sql = mysqli_query($server, “select refferal from profile where id=‘5’”);
$row1 = mysqli_fetch_assoc($sql);
$ref1 = $row[‘refferal’];

$sql2 = mysqli_query($server, “select refferal from profile where id=‘$ref1’”);
$row2= mysqli_fetch_assoc($sql2);
$ref2 = $row2[‘refferal’];

$sql3 = mysqli_query($server, “select refferal from profile where id=‘$ref2’”);
$row3= mysqli_fetch_assoc($sql3);
$ref3 = $row3[‘refferal’];

$sql4 = mysqli_query($server, “select refferal from profile where id=‘$ref3’”);
$row4= mysqli_fetch_assoc($sql4);
$ref4 = $row4[‘refferal’];

I would want to get all the usernames of of user4,user3,user2,user1 in one query if possible .. This is what i have tried so far but i don't think its is very efficent.

i would be happy if someone pointed out to me that **referral** was spelled with two "r's" especially if i were drafting the original HTTP_REFERER http header field https://en.wikipedia.org/wiki/HTTP_referer — WEBjuju, Nov 25 '16 at 20:52

score 0 · Accepted Answer · answered Nov 25 '16 at 20:49

When joining a table to itself, I find that a left join is easiest to understand in case there is no data to the right side (the left will still show up and the right side will be null...in the case where there is no referral).

This query takes the original input of user 5 and self joins via left joins 3 times in order to find the referral chain of users.

select
  p1.username ref1, p2.username ref2, p3.username ref3, p4.username ref4
from
  profile p1 left join 
  profile p2 on p1.refferal = p2.id left join 
  profile p3 on p2.refferal = p3.id left join 
  profile p4 on p3.refferal = p4.id
where
  p1.id = 5;

The output of the row will be four fields names ref1, ref2, ref3, and ref4 where ref1 is the original user with id = 5.

Mysql data in inheritance format using PHP

1 Answers1