Search the Community

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 } } } }