Multilevel user relationship

[skima]

Developed an application that models a multilevel relationship. It all have five stages, the first stage creates seperate tables, while the second stage accepts entry from the first to qualify of the other tables. Now we need to track/create a relation so we can present them in a graphical format with the newest users filling the table right-to-left up-to-down. (2X3 matrix feeding 2X4 forced matrix)

 

i already done one but not satisfactory. it lists even the ones that have not successfully completed their previous stage.

 

Help.Suggestion.Tips will be highly appreciated!

[btherl]

Can you slow down a bit with the explanation?  I have no idea what you're talking about

 

If you want, include a schema of your tables and some sample code.  What we will definitely need is your query which is supposed to list users who have completed the previous stage but is not behaving as you expect.

[skima]

wat have done is to create a relationship with a fore number with new two numbers creating a relation so that the table can go forth. now i need a script that will add a number to the two new numbers as its progeny.

 

example:

 

1 will be the sponsor of 2 and 3, 2 will be the sponsor of 4 and 5, 3 will be the sponsor of 6 and 7 ..... and so on .

 

Now i want to check if either of this offsprings have gotten offspring for it selve if not then a a new entry to and available one which will probably have gotten offspring or less. If its got 2 offsprings then no need to assign it any offspring again.

 

 

[skima]

hey is the code i used to solved the first problem:

 

note** i use ADODB

//to select the 2 direct referal of the currently logged in person
function select2($user_id){
	global $db,$user;
	$sql=$db->Execute("SELECT user_id FROM user WHERE sponsor='$user_id'");
	if($sql->RecordCount())	{
	$fet=$sql->Getrows();

	return $fet;
	}else{
		return false;
	}

}
function getAllRef2($user_id){
	global $db,$user;
	$sql=$db->Execute("SELECT user_id FROM user WHERE sponsor='$user_id' ");
	$my_dl=$sql->RecordCount();
	while($fet=$sql->FetchRow()){
	 $id=$fet['user_id'];
	 $dl=$this->getAllRef($id);
	 $dl2=$this->getAllRef2($id);
	 //$my_dl= $dl + $dl2;
	 $my_dl+=$dl;

	 }
//	echo "kl: $dl2";
	return $my_dl ;
}
function selectUs($user_id){
	global $db,$user;
	$sql=$db->Execute("SELECT user_id FROM user WHERE sponsor='$user_id' ");
	$my_dl=$sql->RecordCount();
	while($fet=$sql->FetchRow()){
		$dl=$this->getAllRef2($fet['user_id']);
		$my_dl+=$dl;
		//$this->selectUs($)
	}
	return $my_dl;
}

function passStage1($user_id,$stage){
	global $db,$user;
	$tr=false;
	switch($stage){
	 	//bronze
	 	case 1:
	 	break;

		case 2:
			if($this->passFeeder($user_id)){
				$tr=true;
			}
		break;

		case 3:
		//silver 30
			if($this->selectUs($user_id)>= 30 ){
				$tr=true;
			}
		break;

		case 4:
			//Gold 30
			if($this->selectUs($user_id)>= 46 ){
				$tr=true;
			}
		break;

		case 5:
			//Diamond 30
			if($this->selectUs($user_id)>= 62 ){
				$tr=true;
			}
		break;


	}

	return $tr;
}
function locateStage($stage){
	global $db,$user;
	$sql=$db->Execute("SELECT user_id FROM user ");
	while($fet=$sql->Fetchrow()){
	 	$user_id=$fet['user_id'];
	 	if($stage==2){
			if(!$this->passStage1($user_id,2)){
				$user_id='';
			}else{
				$ar[]=$user_id;
			}
		}
		if($stage==3){
			if(!$this->passStage1($user_id,3)){
				$user_id='';
			}else{
				$ar[]=$user_id;
			}
		}
		if($stage==4){
			if(!$this->passStage1($user_id,4)){
				$user_id='';
			}else{
				$ar[]=$user_id;
			}
		}
		if($stage==5){
			if(!$this->passStage1($user_id,5)){
				$user_id='';

			}else{
				$ar[]=$user_id;
			}
		}

	 }
	return $ar;
}

[btherl]

What's with the call to getAllRef() in getAllRef2()?  Something looks suspicious there.

 

getAllRef2($user_id) returns the count of all users in $user_id's downline, is that right?  And it does it by recursing into itself.  Does that function work correctly?

 

And selectUs($user_id) seems to do the same as getAllRef2().. is that correct?

[skima]

What's with the call to getAllRef() in getAllRef2()?  Something looks suspicious there.

 

getAllRef2($user_id) returns the count of all users in $user_id's downline, is that right?  And it does it by recursing into itself.  Does that function work correctly?

 

And selectUs($user_id) seems to do the same as getAllRef2().. is that correct?

 

getAllRef($user_id)  ::: It gets the downline of the given id.

i only called the getAllRef2($user_id)  is that i can get its downline straight away and then, loads its downline's downline with getAllRef();

 

 

i then recurse getAllRef2() is that it will do same for the result. right now am having performance problem. the page keeps timing out each time i load it. I think the query is too much. Any advise on how i can optimize this script?

[btherl]

There's many reasons that it could be timing out .. as a first start, I would add a depth limit for the recursion.

 

So it would look like this:

 

	function getAllRef2($user_id, $depth){
                if ($depth == 10) {
                    die("Recursion reached level 10.  Is there a loop somewhere?");
                }

	global $db,$user;
	$sql=$db->Execute("SELECT user_id FROM user WHERE sponsor='$user_id' ");
	$my_dl=$sql->RecordCount();
	while($fet=$sql->FetchRow()){
	 $id=$fet['user_id'];
	 $dl=$this->getAllRef($id);
	 $dl2=$this->getAllRef2($id, $depth + 1);
	 //$my_dl= $dl + $dl2;
	 $my_dl+=$dl;

	 }
//	echo "kl: $dl2";
	return $my_dl ;
}

 

Then you call the function as getAllRef2($id, 0);

 

Even after you find the bug you should keep this code, as it will prevent similar problems in the future due to corruption of the database data.  This might not find the problem, but it will at least eliminate one potential source.