Recursive function returns array values use that values in sql query outside that function in php

Question

I want to get the sponsor level from the given agent id.So i'm using the following code:

function categoryChild($agent,$property) {
global $sponser;
$s = "SELECT c1.Level,c1.id,c1.sponsor_id,c1.sponser_level FROM agent_details c1 WHERE c1.id='".$agent."'";
//echo $s;
$r = mysql_query($s);
// echo $user_level=$fetch['sponser_level'];
 //echo $intro_id;
$sponser =array();
if(mysql_num_rows($r) > 0) {
 //echo "hai";
 # It has children, let's get them.
 while($fetch=mysql_fetch_assoc($r)) {
 # Add the child to the list of children, and get its subchildren
 // $sponser [$fetch['id']] = categoryChild($fetch['sponsor_id'],$property);
 $sponser[] = categoryChild($fetch['sponsor_id'],$property);
 // commissionCal($property,$sponser);
 }
 print_r($sponser); // this returns Array ( [0] => Array ( ) ) Array ( [0] => Array ( ) [1] => Array ( [0] => Array ( ) ) ) 
 }
 return $sponser;
 }
 function commissionCal($property,$sponser_level)
 {
//print_r($sponser_level);
 $get_level="SELECT a1.sponser_level,a2.agent_level_id,a2.property,a2.percentage FROM agent_details a1,agent_level_amount a2 WHERE a2.property='".$property."' AND a2.agent_level_id='". $sponser_level."' ";
 }
 function callFunction($agent,$property)
 {
 $sponser_level=categoryChild($agent,$property);
 //print_r($sponser_level); 
 commissionCal($property,$sponser_level);
 }
 callFunction($agent,$property);

I want to get the sponsor's level to feed it in next function which is having select query to retrieve the percentage value of corresponding level.

Please don't tell MySQL is deprecated - it's just for testing.

• Danyal Sandeelo: categoryChild( ?? implementation?
• PHP dev: @ Danyal Sandeelo sorry?what are you asking?
• Naruto: He's asking where your code is for the function categoryChild()
• PHP dev: @ Naruto I have mentioned it inside categoryChild()
• Danyal Sandeelo: Yes, i saw it your are calling it recursilvely.. why don't your write a separate function that will return the array..??

Answer 1

@variables are left around after a Stored Procedure or Stored Function. Assign them inside the function, then use them outside (or in the next function).

Answer 2

I have got my desired output from the following code by @ Danyal Sandeelo guidance and referred

function categoryChild($sponser,$id,$last_ins_id,$property,$total_amount,$date) {
global $sponser;
$s = "SELECT c1.Level,c1.id,c1.sponsor_id,c1.sponser_level FROM agent_details c1 WHERE c1.id='".$id."'";
//echo $s;
$r = mysql_query($s);
// echo $user_level=$fetch['sponser_level'];
if(mysql_num_rows($r) > 0) {
 # It has children, let's get them.
 while($fetch=mysql_fetch_assoc($r)) {
 $sponsor_id = $fetch['sponsor_id'];
 $agent_id=$fetch['id'];
 $sponser[] = $fetch['sponser_level'];
 //print_r($sponser);
 $c=count($sponser);
 //echo $c."<br>";
 foreach($sponser as $key=>$val)
 {
 // echo $key.'-'.$val."<br>";
 $level=$val;
 }
 //$property=1;
 $get_level="SELECT a1.sponser_level,a2.agent_level_id,a2.property,a2.percentage FROM agent_details a1,agent_level_amount a2 WHERE a2.property='".$property."' AND a2.agent_level_id='". $level."' ";
 //echo $get_level;
 $exe_query=mysql_query($get_level);
 $fetch_percentage=mysql_fetch_array($exe_query);
 $per=$fetch_percentage['percentage'];
 $property_id=$fetch_percentage['property'];
 $amount=$total_amount*$per/100;
 //echo $per."<br>";
 $ins="INSERT INTO `agent_commission` (`agentid`, `bookid`,`property_id`,`commission`,`commission_amount`,`date`,`status`) VALUES ('".$agent_id."', '".$last_ins_id."','".$property_id."', '".$per."','".$amount."','".$date."', 'pending');";
 //echo $ins;
 $exe_ins=mysql_query($ins);
 categoryChild($sponser,$fetch['sponsor_id'],$last_ins_id,$property,$total_amount,$date);
 // commissionCal($property,$sponser);
 }
 }
 }
 //$agent=11;
 function getLevels($agent,$last_ins_id,$property,$total_amount,$date)
 {
 $sponser =array();
 categoryChild($sponser,$agent,$last_ins_id,$property,$total_amount,$date);
 return $sponser;
 }
//getLevels($agent); 

From the above code I'm getting the following records:

 id agentid bookid property_id commission commission_amount date status 
 -------------------------------------------------------------------
 67 14 27 1 10 4000 2015年02月17日 pending
 68 10 27 1 10 4000 2015年02月17日 pending
 69 1 27 1 0 0 2015年02月17日 pending
 /*66 14 27 1 2 800 2015年02月17日 pending*/

in the above result id 66 and 67 having same agent_id-14,its level is 6,So when the level is 6 its should have the commission percentage 2,but it's having 2 and 10.I want to store commission % as 2 only.How do I get it!!

Same time agent_id 14 is having direct introducer_id 10,so its having commission by its corresponding level,But there is 3 intermediate levels between 6 and 10 (7,8,9).Now I want to add those level's commission amount with level 10 commission.How do I do it in the same function I have posted above. help me!!