How does one create this function?

[imgrooot]

Say I have this records table.

RECORDS TABLE

record_id    |    sponsor_id    |    user_id    |    plan_id
------------------------------------------------------------------------------
1                 user5              user6               5  // I am this user.     
2                 user3              user5               3 
3                 user3              user4               4
4                 user2              user3               4
5                 user2              user2               2
6                 user0              user1               5    

I am "user6" and my sponsor is "user5".  What I want to do is find the same "plan_id" from my sponsors, no matter how far up I have to do.  

 

For eg. My current plan id is "5". My sponsor is "user5". If I look for "user5" in the user_id column, I would find that he only has plan "3" id. So I go to his sponsor, which is "user3" and find him in the user_id column. That user's plan id is "4" so it does not match my plan id either. I repeat the same process by going to his sponsor and his sponsor and so on until I find the plan id that matches me.  So for this table example, that would be "user1". 

 

I only want to retrieve the first result that matches my plan id.  How do I go on about coding this function? 

 

Normally I can do these queries to go up limited amount. But I am looking for a more proper function that lets me search my sponsors unlimited times.

$find_plan_id = $db->prepare("SELECT sponsor_id, plan_id FROM records WHERE user_id = :user_id");
$find_plan_id->bindParam(':user_id', $user_id);
$find_plan_id->execute();
$result_find_plan_id = $find_plan_id->fetchAll(PDO::FETCH_ASSOC);
if(count($result_plan_id) > 0) {
  foreach($result_plan_id as $row) {
    $get_sponsor_id_1 =	$row['sponsor_id'];
    $get_plan_id_1    =	$row['plan_id'];
  }
  if($get_plan_id_1 == $my_plan_id) {
  
    echo 'Plan id matches.';
    
  } else {
  
    $find_plan_id_2 = $db->prepare("SELECT sponsor_id, plan_id FROM records WHERE user_id = :user_id");
    $find_plan_id_2->bindParam(':user_id', $get_sponsor_id_1);
    $find_plan_id_2->execute();
    $result_plan_id_2 = $find_plan_id_2->fetchAll(PDO::FETCH_ASSOC);
    if(count($result_plan_id_2) > 0) {
      foreach($result_plan_id_2 as $row) {
        $get_sponsor_id_2 =	$row['sponsor_id'];
        $get_plan_id_2    =	$row['plan_id'];
      }
      if($get_plan_id_2 == $my_plan_id) {
      
        echo 'Plan id matches.';
        
      } else {
        // repeat the process
      }
    }
    
  }
}

[ginerjm]

I'll propose this although I may be a little rusty:

 

 

$user = 'user6';     // select the desired user to be used
 
$q = "select r.record_id, r.sponsor_id, r.user_id, r.plan_id
        from records r 
      join (select plan_id from records where user_id = '$user') k
       on k.plan_id = r.plan_id and r.user_id <> '$user' 
      limit 1";
 

[requinix]

Using MySQL 5.x I assume?

 

Use "recursion" in your code to run a simple SELECT query. I used quotes there because you don't actually need real recursion for this - just a loop.

 

1. Find the user's plan and sponsor.

2. Set a variable with the sponsor.

3. Look for that user.

4. If the user's plan matches then great.

5. If the user doesn't have a sponsor then stop - you didn't find anything.

6. If the user's plan does not match then update that variable from earlier with this user's sponsor (which we know from #5 they must have at this point).

7. Go to 3.

[bsmither]

"I repeat the same process by going to his sponsor and his sponsor..."

Unlimited looping needs a calculated release. In your table, user2's sponsor is user2. (Typo?) This could create an inescapable loop.

 

I like the query above, but the sponsor tree needs a sort so that the LIMIT 1 will be the closest ancestor.