Get data of User's referrer and referrer's referrer in php

Question

Actually I am stucked here, I want to get the data of current User and the current User's referrer's data and referrer's referrer data.

Like A is the first User, the B is second, the C is third, the D is fourth. If the user A is logged in then the query should get the user A's data first and under that there should be the the data of user B because user B is referred by user A and under B there should be the data of user C because the user C is referred by User B and so on for User D.

These all data should be show under the User A's login. I am currently getting only 1 referrer data by this query:

    $querySelect = "Select * from tbl_users where UserID = '$UID'";
    $resultSelect = mysqli_query($conn,$querySelect);
    $numSelect = mysqli_num_rows($resultSelect);
    $FetchedSelect = mysqli_fetch_assoc($resultSelect);

if($numSelect > 0)
{
   $refID = $FetchedSelect['refID'];

//some code to show the the table data...
}

    $querySelect1 = "Select * from tbl_users where UserID = '$refID'";
    $resultSelect1 = mysqli_query($conn,$querySelect1);
    $numSelect1 = mysqli_num_rows($resultSelect1);
    $FetchedSelect1 = mysqli_fetch_assoc($resultSelect1);

if($numSelect1 > 0)
{
   //do some codes to show the table data 
}

In this way I am just getting two user's data of Current User and Current User's referrer. But I want to get all users data of upper line as I mentioned on the top of this post. How can I achieve that?

Welcome. Maybe this answer can help you out?: https://stackoverflow.com/a/33737203/694400 — Wieger, Apr 02 '20 at 15:16
@Wieger its an great answer there but it dose not solves my problem, Its giving me the data under the given user ID. But I want to get the data of upper side of the User ID that I've (The current logged in User) And as an additional, I want to get only 5 users of upper side. For example the logged in user id is 30, the query should get the data of 30 (Logged in User), 29, 28, 27, 26, 25 (These all are the referrer of each other on the upper Line). How can I get the data of upper line, not down line? — Ali Usman, Apr 02 '20 at 15:33
**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add any data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST`, `$_GET` or data *of any kind* directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman, Apr 02 '20 at 17:24
Note: The [object-oriented interface to `mysqli`](https://www.php.net/manual/en/mysqli.quickstart.connections.php) is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface where missing a single `i` can cause trouble. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is an artifact from the PHP 4 era and should not be used in new code. — tadman, Apr 02 '20 at 17:25

score 1 · Answer 1 · edited Apr 02 '20 at 17:25

I've got the answer from stackoverflow's another thread by digging in it. An here is my right query to get only 5 upper line users data from the same table :)

SELECT T2.UserID, T2.Reff_User,T2.Username 
FROM ( SELECT @r AS _id, (SELECT @r := Reff_User FROM tbl_users WHERE UserID = _id) AS ref_user, @l := @l + 1 AS lvl 
FROM (SELECT @r := 32, @l := 0) vars, tbl_users m WHERE @r <> 0) T1 
JOIN tbl_users T2 ON T1._id = T2.UserID ORDER BY T1._id DESC LIMIT 5

Here is the thread link:

Finding all parents in mysql table with single query (Recursive Query)

@sel answer solved my problem. Thank you stackover flow, specially @sel.

Get data of User's referrer and referrer's referrer in php

1 Answers1