Referral System on Levels with Closure Tabels
===

I am looking for help in developing a Referral system on 3 levels using the closure tabels tehnique, but i am not sure how to optimize it properly so if i will have a lot of people when i request a tree display it might crash the database.

I need help in posting the tree for users on 3 levels depth, can someone explain me exactly how it works? some small basic examples will be really helpfully! 

What i need help is creating an optimized 3 level depth tracking for my users for referral system.

Me -
        You
        Dad
            - Mum
                   - Teddy

I have 2 direct referrals and 1 indirect and 1 inindirect.

That's how i want to display it.. help please but in optimize way, i managed to do it in a stupid way... i guess which require many SQL querys

Code is here: 

> > <h2>Stats</h2>  <?php  include "core/config.php"; 
> 
> 
> $uid = '1'; 
> 
> function referralCount($uid,$reflvl)  { 
> 
> $sql = "SELECT * FROM members WHERE sponsor='$uid'"; 
> $rslusrref1=mysql_query($sql);  $nusrref1 =
> mysql_num_rows($rslusrref1); 
> 
> 
> $reflvl1=$nusrref1;  $ttlreflvl2="0";  $ttlreflvl3="0"; 
> 
> for ($i=0; $i<$nusrref1; $i++)  {  $arrusrref1 = 
> mysql_fetch_row($rslusrref1); 
> 
>    $qryusrref2 = "SELECT * FROM members WHERE
> sponsor='$arrusrref1[0]'";     $rslusrref2=mysql_query($qryusrref2); 
>    $nusrref2 = mysql_num_rows($rslusrref2);   
>  $ttlreflvl2=$ttlreflvl2+$nusrref2; 
> 
> 
>       for ($j=0; $j<$nusrref2; $j++) 
>       { 
>       $arrusrref2 =  mysql_fetch_row($rslusrref2); 
> 
>       $qryusrref3 = "SELECT * FROM members WHERE sponsor='$arrusrref2[0]'"; 
>       $rslusrref3=mysql_query($qryusrref3); 
>       $nusrref3 = mysql_num_rows($rslusrref3); 
>       $ttlreflvl3=$ttlreflvl3+$nusrref3; 
>       }  } 
> 
> $reflvl2=$ttlreflvl2;  $reflvl3=$ttlreflvl3;  if($reflvl=='1')  { 
> return($reflvl1);  }  elseif($reflvl=='2')  {  return($reflvl2);  } 
> elseif($reflvl=='3')  {  return($reflvl3);  } 
> 
> 
> 
> } 
> 
> $reflvl1=referralCount($uid,1);  $reflvl2=referralCount($uid,2); 
> $reflvl3=referralCount($uid,3); 
> 
> 
> echo "  <table> 
> 
> <tr>  <td><b>No. of Referrals</b></td>  </tr> 
> 
> <tr>  <td> 
> 
> <table border='1' cellpadding='1' cellspacing='1'> 
> 
> <tr>  <td>1st Level</td>  <td>$reflvl1</td>  </tr> 
> 
> <tr>  <td>2nd Level</td>  <td>$reflvl2</td>  </tr> 
> 
> <tr>  <td>3rd Level</td>  <td>$reflvl3</td>  </tr>  </table>  "; 
> 
> echo "  <a href='logout.php'>logout</a>  "; 
> 
> ?>


