Jump to content

How does one create this function?


Recommended Posts

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
      }
    }
    
  }
}
Link to post
Share on other sites

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";
 
Link to post
Share on other sites

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.

Link to post
Share on other sites

"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.

Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.