Say I have a MySQL table like below. Going from the bottom, I want to find a sponsor that has a "Status = 1". But I want to do it in a order. For e.g. I am "Mike". My sponsor is "Richard". I want to check if "Richard" has a status of 1. He doesn't. So now I am going to check "Richard"'s sponsor to see if he has a status of 1. He doesn't either. So I keep going up, checking my sponsor's sponsor's sponsor...etc.
Normally I can do that with a single or multiple queries if I want to check several levels up. But the problem arises if I want to check infinite levels up until it finds the sponsor who's status is 1? How that work? What would a php query/function look like checking infinite levels up?
Sponsors Referrals Status
--------------------------------------
Zack Joey 1
Joey Tracy 0
Tracy Helen 0
Helen Richard 0
Richard Mike 0Though the solution definitely seems to be to have the parent_id as Lawrence suggests, I though I'd give the PHP solution to this assuming you had the list of people in an array already (i.e., not one query per pair, as that'd be really wasteful).
What you need to do is recursively iterate through the array, going from sponsor to sponsor until you hit a sponsor with status 1. Take a look at this function:
<?php
$people = [
[
"sponsor" => "Zack",
"referal" => "Joey",
"status" => 1,
],
[
"sponsor" => "Joey",
"referal" => "Tracy",
"status" => 0,
],
[
"sponsor" => "Tracy",
"referal" => "Helen",
"status" => 0,
],
[
"sponsor" => "Helen",
"referal" => "Richard",
"status" => 0,
],
[
"sponsor" => "Felipe",
"referal" => "Juan",
"status" => 0,
],
[
"sponsor" => "Richard",
"referal" => "Mike",
"status" => 0,
],
];
static $i = 0;
function getParentSponsor($referal, $people) {
foreach ($people as $pair) {
if ($pair["referal"] === $referal) {
if ($pair["status"] === 1) { // we found our parent sponsor
return $pair["sponsor"];
}
else {
return getParentSponsor($pair["sponsor"], $people); // we need to go to the next element
}
return $pair["sponsor"];
}
}
$i++;
}
echo getParentSponsor("Mike", array_reverse($people)).PHP_EOL; // Zack
echo getParentSponsor("Richard", array_reverse($people)).PHP_EOL; // Zack
echo getParentSponsor("Joey", array_reverse($people)).PHP_EOL; // Zack