How to search first level user record from his parent user id

Question

I have created one employee's table and all employee are connected with other's and for that, I have created one parentUserId field to know his parent user.

Here is my table screenshot:

Now I have just started learning PHP & MySQL so not getting how to get the first user record with the SELECT query. For example, If I am Stephen Lopez user I want to know who is the first user of all connect users.

I am trying to find Phyllis Hinton user. Is it possible with SQL query or I need to make for loop with PHP?

Do you know how many levels deep a user can be? If so, then you can do a fixed number of joins to get your answer. Other than this, I think you are asking for a recursive query. Yes, this could also be handled from PHP, but I don't think that is the nicest way. — Tim Biegeleisen, Dec 03 '16 at 06:18
follow this - http://stackoverflow.com/questions/31168675/how-to-mak-a-recursive-self-join-in-mysql or this - http://stackoverflow.com/questions/8104187/hierarchical-queries-in-mysql — H. Jabi, Dec 03 '16 at 06:30

score 1 · Answer 1 · answered Dec 03 '16 at 06:52

as someone that starting with PHP and mysql, here is a complete code example to get you what you want. there is no magic SQL here but a PHP loop (with a count limiter) to repeat asking the DB for the parent in the connection.

<?php

/* setup your database information */
$host = 'localhost';
$user = 'db_username';
$pass = 'db_password';
$db   = 'db_name';

$link= mysqli_connect($host,$user,$pass,$db); // connect to the database
if (mysqli_connect_errno()) {                 // if fail connection... exit.
    die(mysqli_connect_error());
}

/* the function to locate the first user in the chain of connections */
function find_first_user($link,$first,$last,$max_count=10){
    $cnt=0;   // local counter as prtection to never go over the $max_count
    $parent=0; 
    $tbl='myTableName';

    // find info about the person in question via the first & last name
    $q="select parentUserId from $tbl where firstName='" . mysqli_real_escape_string($link, $first) .
        "' and lastName='" . mysqli_real_escape_string($link, $last) . "'";
    if($res=mysqli_query($link, $q)){
        list($parent)=mysqli_fetch_row($res);
    }
    // if we found a parentId, repeat looking in the chain of connections
    while($parent && ++$cnt<$max_count){
        $q="select parentUserId,firstName,lastName from $tbl where userId=".intval($parent);
        if($res=mysqli_query($link, $q)){
            list($parent,$first,$last)=mysqli_fetch_row($res);
        }
    }
    return "$first $last";
}

// example usage
print find_first_user($link,'Stephen','Lopez') . "\n";
?>

I have tried your code and working perfect but I have one question. What is the use of `$max_count=10`. Can you please explain me and what If I want to search by user id instead of searching with the name? — Mr.Happy, Dec 03 '16 at 07:55
you can send the max_count to the function ; if you don't, it have a default value of 10. it just a protection from running "endless" number of queries because maybe the DB setup incorrectly (example, parent point to itself) or if the chain of connection is super long and make take too much time. To lookup by id first we need a bit changed to the function. first, you need to send the id (and not the user/pass). next, you need to change the first query. the one noted with "find info about the person..." to lookup by id. — Shai Shprung, Dec 04 '16 at 01:11
For example If I have 1000 to 5000 users, Do we need to update $max_count? — Mr.Happy, Dec 05 '16 at 03:31
( if you are using my solution please give it a "Up" arrow ). if you have 5,000 user-chain and you don't mind to weight for 5,000 queries that may take 15-20sec to complete, you can call the function like this: find_first_user($link,'Stephen','Lopez',5000). — Shai Shprung, Dec 05 '16 at 03:33

score 0 · Answer 2 · answered Dec 03 '16 at 07:13

I think you can use this query if you know maximum deep of user levels for example 5 is maximum deep :

SELECT
user.firstName,
parent.firstName AS parent_firstName,
super_parent.firstName AS super_parent_firstName,
s_super_parent.firstName AS s_super_parent_firstName,
s2_super_parent.firstName AS s2_super_parent_firstName,
FROM
  `user`
LEFT OUTER JOIN user AS parent ON parent.id = user.parentuserId
LEFT OUTER JOIN user AS super_parent ON super_parent.id =     parent.parentuserId
LEFT OUTER JOIN user AS s_super_parent ON s_super_parent.id =  super_parent.parentuserId
LEFT OUTER JOIN user AS s2_super_parent ON s2_super_parent.id = s_super_parent.parentuserId

How to search first level user record from his parent user id

2 Answers2