Get all referred users in a single query?

Question

Is it possible to get all the users that might be linked to UserID1?

For example:

UserID1 referred UserID2 and UserID5

UserID2 referred UserID3

UserID3 referred UserID4

Result on UserID1's page:

+UserID2

+UserID5

++UserID3

+++UserID4

How I am currently doing it:

$user_data['id']=1;
$primary_referral_query=mysqli_query($conn, "SELECT username FROM users WHERE referrer=$user_data[id]");
while($primary_referral=mysqli_fetch_array($primary_referral_query))
{
    echo '+'.$primary_referral['username'].'<br>';
}
$secondary_referral_query=mysqli_query($conn, "SELECT a.username, b.username, c.username AS users_c_username FROM users AS a, users AS b, users AS c WHERE b.referrer = a.id AND a.id <> b.id AND c.referrer = b.id AND a.id=$user_data[id]");
while($secondary_referral=mysqli_fetch_array($secondary_referral_query))
{
    echo '++'.$secondary_referral['users_c_username'].'<br>';
}

Have a look at [this question](http://stackoverflow.com/q/20215744/5459839). There are several different answers with ideas on how to get all descendant records linked to a given record in a hierarchical table. — trincot, Sep 26 '16 at 19:31
@bitWorking Result of my code is how I showed in my question (minus the `+++UserID4` because I haven't done that loop) — Draven, Sep 26 '16 at 19:34

score 0 · Answer 1 · edited May 23 '17 at 12:19

Your method of storing hierarchical data is called the adjacency list (model).

You have different options:

1) Read all data from database put it in a php array and traverse it recursively

Example: https://stackoverflow.com/a/15307555/1948627

2) Select only the current user and descendants, put it in a php array and traverse it recursively

The SQL should look like:

SELECT u1.username as lev1, u2.username as lev2, u3.username AS  as lev3, u4.username AS as lev4
FROM users AS u1
LEFT JOIN users AS u2 ON u2.referrer = u1.id
LEFT JOIN users AS u3 ON u3.referrer = u2.id
LEFT JOIN users AS u4 ON u4.referrer = u3.id
WHERE u1.id = 1;

I didn't find examples for the php code, but it should be more difficult, because the data you receive is now redundant. Also note: For every new depth in the tree you have to add one JOIN in the DB select.

3) Recursive PHP/MySQL

Example: https://stackoverflow.com/a/10994181/1948627

I would go with option 1 if you don't have much data. Or better choose another way of storing or retrieving your data.

My favorite way are the Closure Tables

Another interesting opinion about Nested Sets: https://stackoverflow.com/a/31642680/1948627

Get all referred users in a single query?

1 Answers1