Counting elements in multiple expanding arrays

[showman13]

I have a question that may be a bit complicated, so let me set it up with what I am working with first.

I am dealing with a 3 x 10 matrix, which I’m sure you realize means on level 1 there are 3 potential positions, and on level 2 there are 9 potential positions and level 3 – 27, and so on until you get to level 10 which has 59,049 potential positions.

These positions at any point within this entire matrix can be filled by any member that is in a position above them, so some of the levels may be full and some may not be, and they are not filled completely from left to right, the positions on any level can be filled in sporadically.

What I need to be able to do is to determine how many positions are filled on the 10 levels below any selected position(I’ll refer to this as the subject position) in the overall matrix.

Each position that is filled has a member record attached to it, that contains the member_id and the up1_id (which is the member_id of the position that it was placed under)

I know the long and drawn out way to do it would be to query the member database for the member_ids that have the subject position’s member_id recorded as their up1_id. This would produce up to 3 records that I would put into an array.

Then I would traverse this array and do the same query for each record, querying for the member_ids that have the member_ids in the array as their up1_id. This would produce up to 9 records that I would put in an array.

I think you can see where I’m going with this…

10 levels deep, with each level containing potentially 3 times as many elements in the array as the previous level.
This could be a long SLOW drawn out process to gather a total number on each level and an accumulated total overall…

What I’m wondering is if there are any magic formulas that could be used in a MySql Query to extract the required information, without doing it the way I have described?

SIMPLE, Right?

Probably not simple, but I figured if anyone would know how to accomplish it, I would find them in here.

Thanks in advance for your positive feedback.
Douglas

[showman13]

I can't believe that this post has been here for 23 hours and been viewed by 11 people, and not a single response.

I don't know if the question was to broad or if it was one that can't be solved or what, but if one of you highly evolved MySql developers could respond and let me know if I am wasting my time in this effort or if it has some merit, I would certainly appreciate it.

Thank You,
Douglas

[Reverend Jim]

Perhaps the problem is that nobody understands what you are intending. For example, a 3x10 matrix, by any definition I am familiar with, is a structure with 3 rows and 10 columns. I don't know what definition you are using. Also, because this is a database forum, it would be helpful to show the structure of the tables you are working with.

[showman13]

Reverend Jim, that is the only definition that I am aware of for a 3 x 10 matrix as well, which is why I described it as I did in the original post.

OK, to make it easier to see what I need to accomplish, I wrote a little test script to count the number of active positions on each of 10 levels under a selected member ID...

Following is the script / function, and after that are the results and time it took to run it with this limited amount of data. The thing to keep in mind is that the potential number of elements in the array doing it like this is 88,572 and this sample run only contains 1,682 (less than 2% of potential) and took 13 seconds to process.

So, what I'm looking for is an effective means of determining the counts for each of the 10 levels without going through this process.

The member record that contains all the necessary information for this process has a field 'mem_id' which is the auto incremented index created as member join, and a fiele 'up1_id' which is updated with the 'mem_id' of the member that they are placed under in the matrix.

Hopefully that will give you all the information necessary to make a determination.

If there is more information that I need to provide, please let me know.

Again, thanks in advance.
Douglas

<?php // script zz_temp_test5.php
include 'all_inc/config_site.php';
include 'all_inc/pghead.php';
$links = 'y'; // indicates that menu should be displayed
include 'all_inc/pgtop.php';

// ***********************************************************************
//   UPDATED 01/15/2014
//  Function designed to determine number of active positions under a member
//  On each level for up to 10 levels deep
  // Call function with mem_id to get downline count for
  // Used to determine monthly commission run using count for each level
  // returns a single array with 10 elements - count on each level
  function down10($under_mem) {
    settype($level_count,"array");// return array holds count on each level
    $level = 1;
    $query = "SELECT mem_id FROM members where up1_id='$under_mem'";
    // query checks for positions placed under the selected member
    // There will never be more than 3 under any individual position
    $q_res = mysql_query($query);
    $active_count=0;// set counter for active positions on first level
    //  Generate initial elements in matrix array
    while ($q_row = mysql_fetch_array($q_res)) { // step through results
      ++$active_count; // increment for each active position
      // push onto matrix array
      $level_pos[$level][$active_count]=$q_row[0];
    }
    $level_count[$level]=$active_count; // push level count onto return array
    while ($level<11){// only care about 10 levels under selected member
      $prev_level=$level;// set variable to use in next level select statements
      ++$level;// increment to start on next level
      if ($active_count>0){// if previous level has active positions
        $prev_count=$active_count; // set variable to walk array elements
        $active_count=0;// reset counter for active positions on next level
        for ($step=1; $step<=$prev_count; ++$step){
        // step through elements on that level to get active elements under them
          $query = "SELECT mem_id FROM members where up1_id=".$level_pos[$prev_level][$step];
          // query checks for positions placed under the selected member
          $q_res = mysql_query($query);
          while ($q_row = mysql_fetch_array($q_res)) { // step through results
            ++$active_count; // increment for each active position
            // push onto matrix array
            $level_pos[$level][$active_count]=$q_row[0];
          }
        }
      }
      $level_count[$level]=$active_count; // push level count onto return array
    }
    return $level_count;//return array with 10 elements containing level counts
  }



if ($subbed != 'Y') {
  ?>
Enter member ID to determine count of positions on each level under them<br>
<form name="target_mem" action="<?echo $_SERVER['PHP_SELF'];?>" method="POST">
  Member with mem_id of <input name="pick_mem" size="8" value="10000001" maxlength="8"><br><br>

  <input type="hidden" name="subbed" value="Y">
  <input type="submit" name="submit" value="Run Counter">
</form>
  <?php

}else {

   $mtime = microtime();
   $mtime = explode(" ",$mtime);
   $mtime = $mtime[1] + $mtime[0];
   $starttime = $mtime;


  print "Subject member = " . $pick_mem . "<br><br>Go to function<br><br>";
  $up_arr = down10($pick_mem);
  for ($x=1; $x<11; ++$x){
    print "<br>Element " . $x . " value shows ".$up_arr[$x]." Active Positions";
  }

   $mtime = microtime();
   $mtime = explode(" ",$mtime);
   $mtime = $mtime[1] + $mtime[0];
   $endtime = $mtime;
   $totaltime = ($endtime - $starttime);
   print "<br><br>This function was processed in ".$totaltime." seconds";
}


include 'all_inc/pgbottom.php';

?>

Output from running the script:

Subject member = 10000001

Go to function

Element 1 value shows 3 Active Positions
Element 2 value shows 9 Active Positions
Element 3 value shows 27 Active Positions
Element 4 value shows 81 Active Positions
Element 5 value shows 188 Active Positions
Element 6 value shows 278 Active Positions
Element 7 value shows 353 Active Positions
Element 8 value shows 344 Active Positions
Element 9 value shows 243 Active Positions
Element 10 value shows 156 Active Positions

This function was processed in 13.089767932892 seconds

[Reverend Jim]

I'm going to have to pass on this. I still have no idea what you are doing or how the structure you describe is represented in your database.

[showman13]

I honestly don't understand what it is that you don't understand, or what kind of explanation you require. If I did, I would provide it.

Every member, when they join has their up1_id field set to the mem_id that they are placed under. There is only ever 3 at most, members with any single up1_id, and they represent the 3 positions on the up1_ids first level. That seems pretty clear to me.

The same is true for every member(position) in the matrix, so it dynamically generates the matrix based on the correlation between the mem_id and the up1_id.

[Reverend Jim]

I posted a reply twice and both times they disappeared. I'll try again.

I think I get what you are trying to do. To put it into concrete terms, your table represents membership in a club (or pyramid scheme) where there are ten possible levels of membership. The top level can have three members. Each member can sponsor up to three members on the next level down. You already store a member id and the id of each member's sponsor (uplink). Is there any other info in the table (level number, for example) that would be of use?

[showman13]

Nothing else would be necessary, it is simply to get a count of active(filled) positions on each of the 10 levels.

But with the time it takes to do a small sampling, there is no way I can use what I wrote in real life. on a full or nearly full matrix it looks like it would take a minimum of 6-7 minutes to come back with a reply, and that isn't taking into consideration the system slowing as the number of elements increases.

If there is another way to accomplish that, I don't see what it is, so I've been working on another way to create the same end result, only by updating a counter in each members record each time a new position is activated on any of their levels... I haven't tried it yet, but I think it will be much more efficient and will spread the load over thousands of processes as opposed to trying to do it all in one.

Sorry to have taken up your time on something that can't be resolved.

Douglas

[Reverend Jim]

If you stored the level number with each record then it becomes trivial to determine how many slots are available on a level.

SELECT Available = POWER(3,4)-COUNT(*) 
  FROM myTable
 WHERE Level = 4

and it only takes one extra byte for each record in the table.

[showman13]

Well, that would be very true, if you were always looking for the count on the 10 levels under the same member.
But since this matrix is ever evolving and starting from the company and working down, where technically every member/position is in themselves the beginning of their own 10 level matrix within the master matrix, that would be impossible.

If my position landed on the 10th level of the company master matrix, then whoever ended up on my 3rd level would be on the companys 13th level, and they would be on my up 1's 4th level and on their up 1's 5th level, and so on. That is the nature of a matrix being built. Everyone that is added to it is on someones 1st level, and on someone's second level, etc...

I've concluded that I need to come at this from a completely different direction, but thanks you for your feedback anyway...

Maybe I'll come back for help on one that will make sense to work out.

Douglas

[Reverend Jim]

I'll keep this one in the back of my head. It's an interesting problem.